Multiple Items In A Query

[neilbexton]

Hi

I have made a database to show me dates that I need to check various documentation from my contractors.

Once a month I want to print out a query/report to tell me what checks need to be made in the following month.

The dates are
Licence Check
Licence Expiry
Van Ins Exp
GIT Expiry
MOT Expiry
Passport Check
Vis Expiry

I need a query table that shows a list of names that have anything to be checked in the month.

So the Column headers would be
Name, Surname, Licence Check, Licence Expiry, Van Ins Exp, GIT Expiry, MOT Expiry, Passport Check, Vis Expiry.

Its easy to do a list with one date but when I add multiple dates into the query it looks for names and surnames with the date within the next 30 days for every date and therefor brings back no records.

I have attached the picture. Obviously not all the records will show dates. Some will be blank.

I hope someone can help. If I have not been clear enough please feel free to ask to the explain.

Thank you in advance.

 

[pbaldy]

For starters, your design isn't normalized. Those should be records in a related table rather than fields. Then you're just searching one field. In any case, you should be able to put each criteria on a separate line, which creates an "OR" test rather than an "AND" test.

 

[neilbexton]

I have attached a picture of what I thought you mean but it come back "data mismatch"

 

[neilbexton]

The attached file return no results what so ever. I need a list of any date occurring in the query with names down the side.

 

[CJ_London]

you need to make use of the OR criteria so move the criteria for Licence expiry date to the next row down, Van Ins move 2 rows down etc

 

[neilbexton]

Like the attached?

This returns the message 'Date type mismatch in criteria expression'

 

[CJ_London]

check my post - it says move the criteria i.e.<Date()+30

 

[pbaldy]

You don't want the text values, just the <Date()+30, but on separate lines like you have the text.

 

[neilbexton]

D'oh

So simple.

Thanks guys

 

[pbaldy]

No problem.

 

[neil@ctcexpress]

Hold up a minute.

I just notice last the dates in the 2, 3 etc columns are over a month into the future. I only want it to show check that need to be made within the next month.

As attached

If I attached the database could you help.

 

[neil@ctcexpress]

Hi

I have attached a reduced form of the database with no personal detail. Just dates.

Please help.

 

[pbaldy]

But one of the fields qualifies. This is a result of the lack of normalized design I mentioned. Since all of the fields are in the same record, the entire record is returned when any of them is coming due. I have a similar app; I have a table for the basic personnel data, a table for the different types of licenses, and a table that joins the two. That table has a record for each item a person has, with the expiration date. When I query for upcoming expiration's, I only get the ones actually expiring, not everything the person has.

 

[neilbexton]

Hi

So I would need 7 more tables for the below. With the name and the date?
Licence Check
Licence Expiry
Van Ins Exp
GIT Expiry
MOT Expiry
Passport Check
Visa Expiry

How would I link them to the main table?
Would the Query you have given me above then work?

Thanks

 

[pbaldy]

No, like this. The upper left is the employee file, upper right is the file containing all available documents, lower left is the junction table. The first employee 3581 has 6 records in that table, representing 6 different documents each with its own expiration date. I can easily query that table to find any upcoming expiration's, linking to the other tables to get names, documents, etc.

 

[neilbexton]

Hi

This is all way above my head. Im not that deep in Access.

If there is anyone out there who can do this for me I would be willing to pay some cash via paypal.

Let me know your price!!!

Thanks

 

[neilbexton]

Hi

This is all way over my head. Can anyone do this for my. Id be willing to pay a bit for it.

Thanks

 

[CJ_London]

have you checked your private messages? Click on userCP and select messages