Microsoft Query question using MAX function!!

[bpitche]

I have been researching the max function to find out the last service date for each piece of equipment. That part of my query works wonderful, but what I also need to see is the pieces of equipment that have do not have a last service date (in other words, have not been serviced yet. I am linking my equipment table with my service table. Is there any way to pull the pieces of equipment that have not been serviced yet??

Thanks in advance, Brad Pitcher
The Hoover Company
bpitcher@hoover.com

 

[Kalin]

I suggest using the IN clause in the SQL-Statement.

So the complete SQL-Query becomes....

SELECT columns FROM equipment WHERE (current_where_clause ) OR equipment.primary_key NOT IN (SELECT DISTINCT service.equipment_primary_key FROM service);

If you need more help, post you current SQL-Statement so I will be able to modify it. Grtz,

Kalin

 

[nomus1]

Another way to do this would be to save your max querry.
Then do another querry that has the origional table and the max querry. Identify the relationships between the two tables. Right click on the relationship line and change the join properties to include all records from your original table.
Select the columns you need your query to have. Then pull down the item number from your max querry and add the criteria Is Null.
This will only give you the records that don't match the max.