Creating Summary Queries

[LeLe]

I have a table where I enter activities for my fabrics. the fields are:
FabricId, Date, Activity. Activites include: ordered, backordered, received
etc. I want my query to return the most recent activity for a particular
fabric. I have used the Group by feature, and entered Max for the date
field; however, since the activity field is always different, it will not
"group" If I eliminate this field from my query I get the most recent date
entry, but of course can't tell what the activity was. I feel like I should
know how to do this! What am I missing?

 

[Tom van Stiphout]

On Sat, 19 Jul 2008 14:23:00 -0700, LeLe

I always do this in two steps: step 1 is the query without Activity
that you had. Step 2 is to create a new query, select your table and
the Step1 query, and join on FabricId and Date. Then select whichever
fields you want to output.

Date is a bad name for a field, because it is a reserved word. Use
something like ActivityDate.

-Tom.
Microsoft Access MVP

 

[Tom Wickerath]

To add some to Tom's reply, Access MVP Allen Browne has a cool utility
available for free, which you can use to scan your database for the use of
reserved words, among other things.

Database Issue Checker Utility
http://allenbrowne.com/AppIssueChecker.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[LeLe]

thanks for the help. It worked great. Somehow I knew it had to involve a 2
step process, and once I followed your instructions it all made sense.
Thanks again.

 

[Ken Sheridan]

In fact it can also be done with a single query, by using a subquery to
return the latest date per fabric:

SELECT *
FROM YourTable AS T1
WHERE ActivityDate =
(SELECT MAX(ActivityDate)
FROM YourTable AS T2
WHERE T2.FabricID = T1.FabricID);

Note how the two instances of the table are given aliases T1 and T2 to
differentiate them. This allows the subquery to be correlated with the outer
query on the FabricID column, so for each possible row returned by the outré
query the subquery will return the latest ActivityDate for the outer query's
current FabricID value. By its inclusion in the outer query's WHERE clause
the subquery thus restricts the result set of the outer query to those rows
with the latest date per fabric.

Ken Sheridan
Stafford, England