Group by Query

[tylerg11]

I have a question regarding Group By queries. I'm grouping on a specific table to show the most recent records (so for 5 different quotes for the same project it will show one record - the most recent one). I can do this without a problem.



However, what if I also want to show some fields from a related table with a 1-to-1 relationship with my table? Does this need to be two separate queries? Do I need to do the grouping query first? Just wondering if I could do it by grouping on some fields but not others but that doesn't look possible.

[Dal Jeanis]

1) You can join the tables before you do the GROUP BY, and then use the First() function to request Access to ggive you the desired values from any arbitrary record in the group.

2) You can do the GROUP BY and then join the grouped record to your other table, as long as all the fields needed for the join are present on the grouped records.

[Dal Jeanis]

Supposing you had a table of time logged, and a staff table.

tblStaff
   StaffID
   StaffName
   
tblLog
   LogID
   StaffID
   LogInDate
   LogOutDate
   HoursLogged

Now, this gives you the hours in the last 30 days, but you also want the staff name off the staff table:

qryLog:
SELECT 
   TL.StaffID,
   Max(TL.LoginDate) As LastLoggedIn,
   SUM(TL.HoursLogged) AS HoursInLast30Days
FROM
   tblLog AS TL
WHERE 
   TL.LoginDate > Date() - 30
GROUP BY 
   TL.StaffID;

You could join the records before you do the sum, and then accept the first name on any record for a given staffid:

SELECT 
   TL.StaffID,
   First(TS.StaffName),
   Max(TL.LoginDate) As LastLoggedIn,
   SUM(TL.HoursLogged) AS HoursInLast30Days
FROM
   tblLog AS TL
   INNER JOIN
   tblStaff AS TS
   ON TL.StaffID = TS.StaffID
WHERE 
   TL.LoginDate > Date() - 30
GROUP BY 
   TS.StaffID;

or you could join the results of qrylog to the staff table and get the same result, probably slightly more efficiently:

SELECT 
   QL.StaffID,
   TS.StaffName,
   QL.LastLoggedIn,
   QL.HoursInLast30Days
FROM
   qryLog AS QL
   INNER JOIN
   tblStaff AS TS
   ON TL.StaffID = TS.StaffID;

you could also do it in the same query, although Access sometimes gets picky about exact syntax.

SELECT
   QL.StaffID,
   TS.StaffName,
   QL.LastLoggedIn,
   QL.HoursInLast30Days
FROM 
   tblStaff AS TS
   INNER JOIN
   (SELECT 
      TL.StaffID,
      Max(TL.LoginDate) As LastLoggedIn,
      SUM(TL.HoursLogged) AS HoursInLast30Days
   FROM
      tblLog AS TL
   WHERE 
      TL.LoginDate > Date() - 30
   GROUP BY 
      TL.StaffID) AS QL
   ON TS.StaffID = QL.StaffID;

[tylerg11]

1) You can join the tables before you do the GROUP BY, and then use the First() function to request Access to ggive you the desired values from any arbitrary record in the group.

Yes, this is really what I'm trying to do. Join tables and get a clean set of records and then group from there. Here's my problem. Let's say I have 5 quotes for the same project and I want to only show the most recent quote for the project. One of the fields is "Status" which is different for all 5 quotes. This means when I group (which forces me to group on all fields) I will get multiple quotes to show when I only want one. See what I'm trying to do here? Thanks.

[tylerg11]

Basically I'm trying to GROUP before JOIN if that makes any sense. Done through the Access query interface GROUP occurs after the join.

[Dal Jeanis]

The third example shows how that should look in SQL.

[June7]

And another possible solution http://allenbrowne.com/subquery-01.html#TopN