Query Group/Min Problem

[rheide]

Hello All,
I feel like this is a very easy query, but I just can't figure it out. Here is some of the data I am working with:
MC CB CS GDate
A004 C ABCJ973 05/29/01
A004 C DEFI378 03/11/02
A004 C GEG336 08/22/01
B004 D LLL753 04/25/97
B004 E DEA367 04/28/97

What I need for the query to do (in plain English) is to look at MC, CB and GDate. If MC and CB are the same pick the one with the oldest GDate and then also include the CS from that record.

The problem with the query below, is that the CS field is not included.
SELECT tblLics.[MC], tblLics.[CB], Min(tblLics.[GD]) AS [D]
FROM tblLics
GROUP BY tblLics.[MC], tblLics.[CB];

Any suggestions would be appreciated.
-R

 

[RV]

>If MC and CB are the same pick the one with the oldest GDate and then also include the CS from that record.<

Do you want to retrieve any rows when MC and CB are NOT the same?
It'rs rather essential always to define what you want to retrieve and don't want to retrieve under predefined conditions.

RV

 

[rheide]

Sorry for not being clear.
I am attaching an image of the query (its in excel just so I could highlight the rows).

The rows that are yellow are the ones that I would want to keep.

Thanks,
Randy

 

[Jon K]

Create a new query based on the table and your Totals query, linking the fields MC to MC, CB to CB, GDate to D.

 

[rheide]

Worked great. There were 7 dupes that I had to pull out (because they had the same data MC, CB and GDate), but it did the trick.

Thanks for the solution!! I appreciate it.