Summary Query

[Delindan]

I would like to have a query that captures the last record input for each of the last 3 years. I'm doing this with two queries. My first query capturing the last 3 years is:
SELECT [Crop Data].[FS Specialist], [Crop Data].Breeder, [Crop Data].Family, [Crop Data].Crop, [Crop Data].[Sub Crop], [Crop Data].[New Entries], [Crop Data].[advcd phs 4], [Crop Data].[advcd phs 5 advcd comm], [Crop Data].[advcd phs 5 entered FS at phase 3], [Crop Data].[advcd phs 5 entered FS at phase 4], [Crop Data].[moved phs 4 to phs 6], [Crop Data].[entries renewed], [Crop Data].[Estimate of new entries], [Crop Data].[Data Year], [Crop Data].[Input Date]
FROM [Crop Data]
WHERE ((([Crop Data].[Data Year])>=(Year(Now())-3)))
ORDER BY [Crop Data].Crop, [Crop Data].[Sub Crop], [Crop Data].[Data Year];

This one seems to be working fine. When I try to get the next level to work:

SELECT *
FROM [Crop Last 3 Years] AS T1
WHERE [Input Date] = (SELECT MAX([Input Date])
FROM [Crop Last 3 Years] As T2
WHERE T1.[Crop] = T2.[Crop]
AND T1.[Sub Crop] = T2.[Sub Crop]
GROUP BY [Crop] AND [Sub Crop]);

It almost works but not quite. Not all of the crops have a sub crop so there should be records in the results without a subcrop however all of the results of this query have sub crops which I know is incorrect. Any thoughts on this?

Thanks

 

[JonFer]

Do you want to see a record for each year for each crop and sub-crop? It looks like this will only return the single most recent record for a crop and sub-crop (unless your crop and sub-crop values are specific to a year).

To get the records with a NULL sub-crop value, simply use the Nz() function:

... and Nz(T1.[Sub Crop],"") = Nz(T2.[Sub Crop],"")

In SQL, a NULL != NULL so you have to test directly using IS NULL or use Nz() to replace the NULL before comparing fields.

 

[PHV]

And your GROUP BY clause seems suspect to me.
I'd use this instead:
GROUP BY [Crop][!],[/!] [Sub Crop]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Delindan]

Thanks...let me give it a try

 

[Delindan]

Works great thanks so much