DISTINCT or GROUP BY?

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!

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Kathy R." wrote in message
...
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!

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

--
Build a little, test a little.


"Kathy R." wrote:

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!
.

Thank you Allen, that worked great.



Allen Browne wrote:
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

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.

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