ACCESS FEED: [AccessDevelopers] Returning additional fields with a Group By

Minggu, 26 Januari 2014

[AccessDevelopers] Returning additional fields with a Group By

I'm relatively new to Access, so I hope you'll bear with me. My Select below is returning exactly the data set I'd like from RandRoster - the RandRoster row with the largest ModRand value for each IDCar value.

SELECT RandRoster.IDCar, Max(RandRoster.ModRand) AS MaxOfModRand

FROM RandRoster

GROUP BY RandRoster.IDCar;

However, without modifying that functionality, I'd like to also have this query return the RandRoster.Shipment value for those same rows. Is there a way to do so?

I tried including the Shipment field in the Select earlier and got the error, "You tried to execute a query that does not include the specified expression 'Shipment' as part of an aggregate function." I believe I read that any fields in the Select also had to be in the Group By clause, but if I include Shipment in the Group By, it returns a record for each car/shipment pair instead of just the one car entry that has the largest random number.

Do I need to create a separate query after this one to retrieve RandRoster entries matching the IDCar and MaxOfModRand values returned by this one, so I can then retrieve the Shipment field as well? If so, how would I go about that? I've never written a query that has, as its criteria, the results returned from a previous query.

Thanks very much for your time,

Joe

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (1)

Recent Activity:

Visit Your Group

Please zip all files prior to uploading to Files section.