Help - Query Dealing with Dates!!

[bbanks2]

Hello,



I need help creating a query. I really don't know where to begin with this one. I would like the query to pull records (audits) that are 365 days past its "Actual Start Date (field)". There are about 150 audits and each audit can have a different "Actual Start Date."

Here is the information in the database:

Table Name: Manager
Field: AuditNum
Field: ActualStartDate

Table Name: ManagerStatus
Field: Reason
Field: ActionPlan

Ideal query output (for records over 365 days):

AuditNum ActualStartDate Reason ActionPlan

Any help is greatly appreciated!!!! Thank you!!

[jzwp11]

First, how are the Manager and ManagerStatus tables related? I do not see a field in each that would make a join.

As to the criteria of pulling records where the difference between today's date and the actualstartdate is >365 that is fairly easy. You will need a couple of built-in functions. The date() function, just returns the current date (based on your system date). The datediff() function finds the difference between two dates. The function can return the number of days (d), years (yyyy), or months (m) etc. depending on which option you use in the first term. Since you mentioned 365 day, I am using the days option ("d") in the function:

WHERE datediff("d",actualstartdate, date())>365

[bbanks2]

Thanks for your response jzwp11! This worked. Problem Resolved!!

The Manager and ManagerStatus tables are linked by "AuditNum."

[jzwp11]

Ok, you would bring both tables into the query and join them via the auditnum field. Then add a calculated field datediff("d",actualstartdate, date()) with the criteria of >365.