?
Solved

SQL: Return only the first row of each Group By

Posted on 2003-03-10
11
Medium Priority
?
229 Views
Last Modified: 2008-02-01
I'm working on a db that tracks puppy information, and need a query that will return a list of medications due for each puppy (to feed a report obviously).

However, the problem I am having is that it will return EVERY next due medication regardless of whether it has already been given.  I only need it to list the last row of the matches for each puppy (or first - depending how it's sorted).

For example, the query I have now will return 3 rows for the puppy "Patriot":

DogID   MedName LastOfDateGiven NextDue         NextMedName
Patriot DHPP1   4/22/2002       6/3/2002        DHPP2
Patriot DHPP2   5/30/2002       7/11/2002       DHPP3
Patriot DHPP3   6/27/2002       6/26/2003       DHPP-B

However, the only row I want to see is the last one (but for every dog in the query, not just this one) because the first two's "NextDue" have already been filled.  I only ever care about the medication given on the most recent date for a puppy.

I tried using the Last() function on the "DateGiven" field, but it still shows all of them.  I'm guessing it's because either the other fields differ, or because I haven't specified the it needs to be the last within the DogID group, not its own group.  But I don't know how to define that.

BTW, if I remove any of the fields in the Group By section, Access complains that I "tried to execute a query that does not include the specified expression '(whatever I removed)' as part of an aggregate function."  It doesn't make sense to me that I should have to group by every field.  If anything, just by DogID.  But then, I don't fully understand aggregate functions yet.

Anyway, here is the query that returned the rows from which the above sample was excerpted:

SELECT SchedMeds.DogID, SchedMedTypes.MedName, Last(SchedMeds.DateGiven) AS LastOfDateGiven, DateAdd("ww",[SchedMedTypes]![WeeksGood],[SchedMeds]![DateGiven]) AS NextDue, SchedMedTypes_1.MedName
FROM ((SchedMedTypes LEFT JOIN SchedMedTypes AS SchedMedTypes_1 ON SchedMedTypes.NextMed = SchedMedTypes_1.MedTypeID) RIGHT JOIN SchedMeds ON SchedMedTypes.MedTypeID = SchedMeds.MedTypeID) RIGHT JOIN PuppyPgm ON SchedMeds.DogID = PuppyPgm.DogID
GROUP BY SchedMeds.DogID, SchedMedTypes.MedName, DateAdd("ww",[SchedMedTypes]![WeeksGood],[SchedMeds]![DateGiven]), SchedMedTypes_1.MedName, SchedMedTypes.WeeksGood, PuppyPgm.ExitInfo, PuppyPgm.ExitDate
HAVING (((SchedMedTypes.MedName) Like "DHPP*") AND ((SchedMedTypes.WeeksGood) Is Not Null) AND ((PuppyPgm.ExitInfo) Is Null) AND ((PuppyPgm.ExitDate) Is Null))
ORDER BY SchedMeds.DogID, Last(SchedMeds.DateGiven);

There must be a better way to present this question, but I wanted to include the full query just to be sure the whole picture is available.

I think what it comes down to is this...  If there is a table of Dogs related one-to-many with a table of Medications, I need a way to return only the rows in Medications that have the most recent DateGiven for each Dog.

I can do it for a single dog just fine.  But I need the query to return the results for every dog in the database.  That's the power of queries, right?  It would be silly to run the query 200 times to get this result.  There must be a better way.

Hopefully the concept of what I'm trying to do can be clear without fully understanding the data behind it, but let me know if I should provide information about the database design, such as the tables feeding this query.
0
Comment
Question by:thwack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 3

Expert Comment

by:RiverGuy
ID: 8105760
Do you only care about the last date entry from your query?  Never 2 or more rows?  If it is just 1 row for each that you are ever after, this is a simplistic example:

SELECT MAX([Date_Field]), Name
FROM Your_Table
GROUP BY Name;


Try something like that will all of your other filtering criteria.


0
 

Author Comment

by:thwack
ID: 8105828
That is what would make the most sense to me, but like I said, when I tried to remove everything else from "GROUP BY" except for Name, it gave the error I quoted.
0
 
LVL 2

Accepted Solution

by:
stiemark earned 160 total points
ID: 8105875
I would do this as a two step process:

1st step:
Create a query PuppyMaxDates:
SELECT PuppyPgm.DogID, max(SchedMeds.DateGiven) as MaxDateGiven
FROM PuppyPgm LEFT JOIN SchedMeds ON PuppyPgm.DogID = SchedMeds.DogID
GROUP BY PuppyPgm.DogID

Now, use that guery to get the specifics on the meds:
SELECT PuppyMaxDates.DogID, PuppyMaxDates.MaxDateGiven,
DateAdd("ww",SchedMedTypes.WeeksGood,PuppyMaxDates.MaxDateGiven) AS NextDue, ...
FROM PuppyMaxDates LEFT JOIN SchedMed ON (PuppyMaxDates.DogID = SchedMed.DogID AND PuppyMaxDates.MaxDateGiven = SchedMed.DateGiven) ...
ORDER BY ...


No GROUP BY needed at that point.


Mark
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:RiverGuy
ID: 8105933
Yes, you would have to do it in 2 steps if you wanted more data displayed than you're grouping on.  
0
 

Author Comment

by:thwack
ID: 8106300
stiemark, your suggestion sounds like it's on the right track, except that the first query is useless if I can't include the MedTypeID for each row, because that is needed to make the calculations in the second query.

When I do this:

SELECT SchedMeds.DogID, Max(SchedMeds.DateGiven) AS MaxOfDateGiven
FROM SchedMedTypes RIGHT JOIN SchedMeds ON SchedMedTypes.MedTypeID = SchedMeds.MedTypeID
WHERE (((SchedMedTypes.MedName) Like "DHPP*"))
GROUP BY SchedMeds.DogID;

I get almost what I need for the first query.  But I can't add the MedTypeID field without getting an error for not using it in an aggregate function.
0
 
LVL 2

Expert Comment

by:stiemark
ID: 8106351
You pick up the MedType from the second query.

From my example, the query PuppyMaxDates gives you a list of dogs and their MaxDates. Left-joining this query to SchedMeds on both DogID and DateGiven will then let you pick up the rest of the fields you want while ONLY giving you the records you want.

Mark
0
 

Author Comment

by:thwack
ID: 8106480
The SchedMeds table's primary key uses the 3 fields DogID, MedTypeID and DateGiven.  If I left-join on DogID and DateGiven, and there are multiple rows with different MedTypeIDs, it wouldn't know which one it was.
0
 
LVL 2

Expert Comment

by:stiemark
ID: 8106516
According to your question, if the following records apply to Patriot:
Patriot DHPP1   4/22/2002       6/3/2002        DHPP2
Patriot DHPP2   5/30/2002       7/11/2002       DHPP3
Patriot DHPP3   6/27/2002       6/26/2003       DHPP-B

, you only wanted to pick up the last one.

So, joining on this with only "Patriot" and "6/27/02" will only give you the record you wanted. Correct?

Remember, for what you're trying to do, you only care about the furthest date for each dog. You don't care what med it deals with (at that point at least).

If you try it, you'll see what I mean.

Mark
0
 

Author Comment

by:thwack
ID: 8106751
What I haven't mentioned yet is that there are other types of medication.  There are also "Bordatella*" and "Rabies*" types.  I was just going to handle it with 3 different versions of the query rather than putting the effort into making one perfect one.  (But if someone wants to suggest a way to handle all 3 in one query, that's a guaranteed grade A answer as far as I'm concerned.)

All of these medications show up along with the "DHPP*" ones in the SchedMeds table.  If Patriot got both a DHPP3 and a Rabies shot on 6/27/2002, joining on just DogID and Date would return two records.
0
 
LVL 2

Expert Comment

by:stiemark
ID: 8106781
If you're saying that Patriot got both a DHPP3 and a Rabies shot, and you want to pick both up, then what I said will do that. When you join PuppyMaxDates back again, it will pick up all records for 6/27/2002 (assuming that's the max), and you'll end up with 2 records for Patriot.

If you're saying you ONLY want to pick up the DHPP records, then add that to the WHERE statement of PuppyMaxDates.
0
 

Author Comment

by:thwack
ID: 8106918
Well, I do want to pick them both up eventually, but I still need to tell them apart in the end because they have different WeeksGood and NextMed values to feed into the calculations.

But in any case, restricting the second query to DHPP records will allow the join to work as you suggested.

Thanks!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question