If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
DISTINCT or GROUP BY?
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! |
#3
|
|||
|
|||
DISTINCT or GROUP BY?
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! . |
#4
|
|||
|
|||
DISTINCT or GROUP BY?
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 |
#5
|
|||
|
|||
DISTINCT or GROUP BY?
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; |
Thread Tools | |
Display Modes | |
|
|