MAX records help

[Confused]

I need help with a query.

CLEAN_SS (table name)

[ID]
[CompName]
[EmpSS]
[EmpLast]
[EmpFirst]
[Type]
[Reason]
[Date]

I need help designing a query from this table that will show me the highest (MAX) [Type] based on the [Date] field for Each [EmpSS] with each [CompName].

So if WALMART has 5 employees, those 5 employees have 18 records in the CLEAN_SS table. I would like to see the most recent record for each one based on the [TYPE] If employee (A) has 4 of those 18 records, yet they all 4 have the same TYPE then that employee will only have one record in the query. IF employee (B) has 3 records and they all have different TYPES then that employee will have 3 records in the final query.

“Query1” works perfect as long as I keep the table under 75 total records. Problem is that the DB I need to run this on has about 24,000 records. If my table has less than 100 records it runs perfect. IF I have more I get an error:

“At most one Record can be returned by this subquery”

Query1 has all of the columns I need it to have, I just need it to work with a larger amount of data.

Can you Help?

See file attached for sample table & query. Thank you all.

 

[KathyL]

I didn’t look at your file.
The source of one query can be another query, that’s one option.
You can also use a make-table query to generate an interim table which can be partially or fully summarized. And then you run another query against your interim table.
A combination of these methods may get the results you want.

 

[WIS]

[“Query1” works perfect as long as I keep the table under 75 total records. Problem is that the DB I need to run this on has about 24,000 records. If my table has less than 100 records it runs perfect. IF I have more I get an error:

Seems like you might have Top Values set at 100 records. Get the qry up in QBE mode, rt click and go to Properties. This will give you the "Query Properties" box. Top Values should be All.