DISTINCT or GROUP BY?

[Kathy R.]

Using Access 2007. My table is as follows

tblIndividual
IndID (autonumber, primary key)
OLD_FamID (not unique)
LastName (not unique)
OLD_MembID (unique)

I am trying to set up a query to show me the first instance of each
OLD_FamID.

Data:
OLD_FamID LastName OLD_MembID
1 Jones 1
1 Jones 2
1 Jones 3
2 Smith 4
2 Smith 5
3 Rogers 6
3 Rogers 7

Query would show
1 Jones 1
2 Smith 4
3 Rogers 6

How do I do this? I've been playing around a bit with both DISTINCT and
GROUP BY, but can't quite get it. Thanks for the help!

 

[Allen Browne]

In query design view, depress the Totals button on the toolbar/ribbon so you
get the Totals row.

In the Totals row under OLD_FamID, choose Group By
In the Totals row under OLD_MembID choose Min

To get the name for that record see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

 

[KARL DEWEY]

Try this --
SELECT OLD_FamID, LastName, Min(OLD_MembID) AS OLD_MembID_x
FROM tblIndividual
BROUP BY OLD_FamID, LastName;

 

[Kathy R.]

Thank you Allen, that worked great.

 

[Kathy R.]

Thank you Karl,

I used Allen's solution. I had tried something similar to yours but it
didn't work because I had several cases where I had the same OLD_FamID
but different LastName(s). I neglected to mention that in my sample
data. I simplified it a bit too much.

I do appreciate the reply though. The more I learn the better!

Kathy R.