SQL: Return only the first row of each Group By

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.
thwackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiverGuyCommented:
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
thwackAuthor Commented:
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
stiemarkCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RiverGuyCommented:
Yes, you would have to do it in 2 steps if you wanted more data displayed than you're grouping on.  
0
thwackAuthor Commented:
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
stiemarkCommented:
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
thwackAuthor Commented:
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
stiemarkCommented:
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
thwackAuthor Commented:
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
stiemarkCommented:
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
thwackAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.