Need assistance in setting up query

[whjwilson]

I am trying to set up a query and report using access, but am unsure how to proceed. What I want to do is set up a database listing units and company's I inspect, and in turn set up access to give me a list of units or company's that are due their annual inspections. For istance my main table consists of this information:

Company Name Date Inspected:
Company A 01 Jan 05
Company B 02 Feb 04
Company C 21 Mar O4

What I would like the query to do is give me a list of which company's are due or overdue an inspection (18 Months from previous inspection date). Any help or guidance anyone could give would be greatly appreciated.

 

[mp9]

Try a query of the form:

SELECT *
FROM YourTableNameHere
WHERE DateInspectedFieldNameHere < DateAdd("m", -18, Now());

http://www22.brinkster.com/accessory/

 

[whjwilson]

mp9,

I have a very basic knowledge of Access, where in the query process would I enter the information you gave above? Thanks.

Will

 

[JoeAtWork]

Once you have more than one record for any of the companies, you will want it to select only the most recent inspection for each company, basically something like:

SELECT tblInspections.CompanyName, Max(tblInspections.LastInspected) AS LastInspectedDate
FROM tblInspections
GROUP BY tblInspections.CompanyName
HAVING (((Max(tblInspections.LastInspected))<DateAdd("m",-18,Now())));

To paste this code into a query, create a new query, when you are prompted to select a table, just click the Close button. Then from the View menu, choose SQL View. Then just paste in the SQL Statement above. Replace the table and field names I used (tblInspections, CompanyName, LastInspected) with whatever names you used.