Query dates from different tables

shosim85

Registered User.
Local time
Today, 22:37
Joined
Aug 22, 2012
Messages
17
Please Help!

I have several different tables with date fields for different things, i.e ID expiry, Course Expiry, Passport Expiry, etc. These fields are stored in different tables. E.g:

Table 1 Table 2 Table 3

text field date field text field
text field text field date field
date field text field text field

I want to run a query that prompts the user to enter an expiry date (mm/yy) and produce a stepped report of the person's name and details of all date fields across all tables that expire in the month and year input by the user.

I think I just need a bit of code that brings all these date fields together so I can query it, prompting the user to enter a date but I have no idea how to do it.

Please help as I am about to :banghead:.

Thanks in advance

shosim85
 
We need to know exactly the fields in your tables, their relationships and some sample records.
 
I cannot attach because the file was too big. Is there any other way I can get it to you please?

Thanks

shosim85
 
We only need a few records. Copy and paste something like 5 records into Excel and upload that file. Obviously we don't expect to see confidential info.

As for your relationships, a screenshot will do.
 
ok. I have attached a few records on the excel spreadsheet and a screen shot of the relationships. Hope this helps.

Thanks
 

Attachments

  • Staff 2.xls
    Staff 2.xls
    32 KB · Views: 134
  • relationships.JPG
    relationships.JPG
    78.2 KB · Views: 103
Alright shosim85, based on the data that you've sent, can also include what you would like to see from those tables.
 
Ok. I would like to query all date fields across the different tables so that the user can enter the mm/yy they want to query. Then I want the results to display only those fields where the mm/yy is a match to the user's input. So e.g would be user enters 09/12 and the database would produce something like:

Tanya Simms | Entry Permit | 22/09/12
Tanya Simms | Petrol Tools | 14/09/12
Jodie Roberts | Visa Expiry | 08/09/12

Once I can capture the results, I will produce a stepped report. Is this possible?
 
Even date of birth?
And why do you have FullName in your Courses and Small Tools tables?

Look into joining the tables using a UNION ALL statement first. For example, you will need to join the Staff table for as many times as you have Date fields using several UNION ALL's.
 
OK?! Not too familiar with that method as I am kinda self taught and seem to know some of the more complicated stuff but not the basics. I will read up on this and see how it goes.

Thanks for your time and help.

And no, not date of birth, lol
 
vbaInet, sorry to be a pain but I am confused as to how to go about doing this union query. I have been reading that to do this, fields in the different tables need to have the same named fields. None of mine do so I'm not sure how I combine them.

Please help, sorry

shosim85
 
You misunderstood. They need to be of the same data type and they need to have the same number of fields. The number of fields it's referring to is the one you include in the UNION ALL statement.

E.g.:
Code:
SELECT FirstDate
FROM Table1
UNION ALL
SELECT SecondDate
FROM Table1
Same table but different fields joined together.
 
.... and you can add the date fields from the other tables too.
 
YEP! It's as I thought.... YOU are BRILLIANT! That works nicely :cool:. I'll plod on gently now (well, at least until I come across another problem)!

Thanks so much, your a STAR!!!!
 
Hi vbaInet

Hope all is well with you. I have now near enough completed the database. However I have one small problem. I have done a query joining the date fields from my tables:

SELECT SmallTools.FullName, [Small Tools ID].SmallTool AS Title, SmallCodeID.SmallCode AS Code, UnionCourseDate.Expiration
FROM Staff INNER JOIN ([Small Tools ID] INNER JOIN (SmallCodeID INNER JOIN (UnionCourseDate INNER JOIN SmallTools ON UnionCourseDate.Expiration = SmallTools.SmallToolsExpiry) ON SmallCodeID.SmallCodeID = SmallTools.SmallToolsCodeID) ON [Small Tools ID].SmallToolsID = SmallTools.SmallToolsID) ON Staff.FullName = SmallTools.FullName
WHERE (((Staff.LeavingDate) Is Null)) AND ((Format([Expiration],"mm/yy"))=[Enter Expiry Date])
UNION ALL
SELECT Course.FullName, [Course Title ID].CourseTitle AS Title, CourseCodeID.CourseCode AS Code, UnionCourseDate.Expiration
FROM Staff INNER JOIN ([Course Title ID] INNER JOIN (CourseCodeID INNER JOIN (UnionCourseDate INNER JOIN Course ON UnionCourseDate.Expiration = Course.CourseExpiry) ON CourseCodeID.CourseCodeID = Course.CourseCodeID) ON [Course Title ID].CourseTitleID = Course.CourseTitleID) ON Staff.FullName = Course.FullName
WHERE (((Staff.LeavingDate) Is Null) AND ((Format([Expiration],"mm/yy"))=[Enter Expiry Date]));

I have produced a report based on this query and get the attached (repeated results). Can you see what has gone wrong because I can't work it out? I have not got multiples in the original table so I'm not sure why this is happening.

Thanks

shosim85
 

Attachments

  • expiry report.jpg
    expiry report.jpg
    87.7 KB · Views: 88
Perhaps you should try UNION instead of UNION ALL -- UNION ALL will keep the duplicates/replicates whereas UNION will not.
 
Happy to help. Glad it's working.
 
Ok, been a while but I have another problem. I have the following code:

SELECT Staff.FullName, [Course Title ID].CourseTitle AS Title, UnionCourseCode.CCode, Course.CourseExpiry
FROM UnionCourseCode INNER JOIN (CourseCodeID INNER JOIN ([Course Title ID] INNER JOIN (Staff INNER JOIN Course ON Staff.FullName = Course.FullName) ON [Course Title ID].CourseTitleID = Course.CourseTitleID) ON CourseCodeID.CourseCodeID = Course.CourseCodeID) ON UnionCourseCode.CCode = CourseCodeID.CourseCode
WHERE (((UnionCourseCode.CCode)=[Please enter Course Code]) AND ((Course.CourseExpiry)>=Date()) AND ((Staff.LeavingDate) Is Null));
UNION ALL SELECT Staff.FullName, [Small Tools ID].SmallTool AS Title, UnionCourseCode.CCode AS Code, SmallTools.SmallToolsExpiry
FROM UnionCourseCode INNER JOIN (Staff INNER JOIN ([Small Tools ID] INNER JOIN (SmallCodeID INNER JOIN SmallTools ON SmallCodeID.SmallCodeID = SmallTools.SmallToolsCodeID) ON [Small Tools ID].SmallToolsID = SmallTools.SmallToolsID) ON Staff.FullName = SmallTools.FullName) ON UnionCourseCode.CCode = SmallCodeID.SmallCode
WHERE (((UnionCourseCode.CCode)=[Please enter Course Code]) AND ((SmallTools.SmallToolsExpiry)>=Date()) AND ((Staff.LeavingDate) Is Null));
Now this code works fine except for one thing...

If there is no expiry date for the course, that course does not appear on the report. I tried to do:

WHERE (((UnionCourseCode.CCode)=[Please enter Course Code]) AND ((SmallTools.SmallToolsExpiry)>=Date() Or Is Null) AND ((Staff.LeavingDate) Is Null));
but this gives me syntax errors. Any idea how to fix this please?

Thanks
shosim85
 

Users who are viewing this thread

Back
Top Bottom