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
|
|||
|
|||
SELECT DISTINCT query
Hi folks,
Using Access 2007. I have a query (SQL below) in which I need to select only those records with distinct FamID's I'm pretty sure I need to use SELECT DISTINCT, but if I put that right at the beginning it's looking for a whole distinct record, not just the distinct FamID (if I understand it correctly). How can I change this query so that I end up with records where the FamID field is distinct? SELECT tblFamily.FamID, tblGroup.GroupName, tblFamily.FamLastName, tblAddress.Street, tblAddress.City, tblAddress.State, tblAddress.ZipCode FROM tblGroup INNER JOIN (((tblFamily INNER JOIN tblIndividual ON tblFamily.FamID = tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID = tblAddress.AdFamID) INNER JOIN tblGroupMembers ON tblIndividual.IndID = tblGroupMembers.GMIndID) ON tblGroup.GroupID = tblGroupMembers.GMGroupID WHERE (((tblGroup.GroupName) Like "*flm*")); Thank you for your help! Kathy R. |
#2
|
|||
|
|||
SELECT DISTINCT query
Are you saying that you only want one record for the tblFamily.FamID? If so
is that field the primary key field in tblFamily? Also what do you want to see for the reset of the record? For example which individual do you want to see? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kathy R." wrote: Hi folks, Using Access 2007. I have a query (SQL below) in which I need to select only those records with distinct FamID's I'm pretty sure I need to use SELECT DISTINCT, but if I put that right at the beginning it's looking for a whole distinct record, not just the distinct FamID (if I understand it correctly). How can I change this query so that I end up with records where the FamID field is distinct? SELECT tblFamily.FamID, tblGroup.GroupName, tblFamily.FamLastName, tblAddress.Street, tblAddress.City, tblAddress.State, tblAddress.ZipCode FROM tblGroup INNER JOIN (((tblFamily INNER JOIN tblIndividual ON tblFamily.FamID = tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID = tblAddress.AdFamID) INNER JOIN tblGroupMembers ON tblIndividual.IndID = tblGroupMembers.GMIndID) ON tblGroup.GroupID = tblGroupMembers.GMGroupID WHERE (((tblGroup.GroupName) Like "*flm*")); Thank you for your help! Kathy R. . |
#3
|
|||
|
|||
SELECT DISTINCT query
You can get that with this --
SELECT tblFamily.FamID FROM tblFamily GROUP BY SELECT tblFamily.FamID; But what other field do you want with it? -- Build a little, test a little. "Kathy R." wrote: Hi folks, Using Access 2007. I have a query (SQL below) in which I need to select only those records with distinct FamID's I'm pretty sure I need to use SELECT DISTINCT, but if I put that right at the beginning it's looking for a whole distinct record, not just the distinct FamID (if I understand it correctly). How can I change this query so that I end up with records where the FamID field is distinct? SELECT tblFamily.FamID, tblGroup.GroupName, tblFamily.FamLastName, tblAddress.Street, tblAddress.City, tblAddress.State, tblAddress.ZipCode FROM tblGroup INNER JOIN (((tblFamily INNER JOIN tblIndividual ON tblFamily.FamID = tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID = tblAddress.AdFamID) INNER JOIN tblGroupMembers ON tblIndividual.IndID = tblGroupMembers.GMIndID) ON tblGroup.GroupID = tblGroupMembers.GMGroupID WHERE (((tblGroup.GroupName) Like "*flm*")); Thank you for your help! Kathy R. . |
#4
|
|||
|
|||
SELECT DISTINCT query
ERROR (I saw it as it went out the door).
SELECT tblFamily.FamID FROM SELECT tblFamily GROUP BY tblFamily.FamID; -- Build a little, test a little. "Kathy R." wrote: Hi folks, Using Access 2007. I have a query (SQL below) in which I need to select only those records with distinct FamID's I'm pretty sure I need to use SELECT DISTINCT, but if I put that right at the beginning it's looking for a whole distinct record, not just the distinct FamID (if I understand it correctly). How can I change this query so that I end up with records where the FamID field is distinct? SELECT tblFamily.FamID, tblGroup.GroupName, tblFamily.FamLastName, tblAddress.Street, tblAddress.City, tblAddress.State, tblAddress.ZipCode FROM tblGroup INNER JOIN (((tblFamily INNER JOIN tblIndividual ON tblFamily.FamID = tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID = tblAddress.AdFamID) INNER JOIN tblGroupMembers ON tblIndividual.IndID = tblGroupMembers.GMIndID) ON tblGroup.GroupID = tblGroupMembers.GMGroupID WHERE (((tblGroup.GroupName) Like "*flm*")); Thank you for your help! Kathy R. . |
#5
|
|||
|
|||
SELECT DISTINCT query
Did it again --
SELECT tblFamily.FamID FROM tblFamily GROUP BY tblFamily.FamID; -- Build a little, test a little. "Kathy R." wrote: Hi folks, Using Access 2007. I have a query (SQL below) in which I need to select only those records with distinct FamID's I'm pretty sure I need to use SELECT DISTINCT, but if I put that right at the beginning it's looking for a whole distinct record, not just the distinct FamID (if I understand it correctly). How can I change this query so that I end up with records where the FamID field is distinct? SELECT tblFamily.FamID, tblGroup.GroupName, tblFamily.FamLastName, tblAddress.Street, tblAddress.City, tblAddress.State, tblAddress.ZipCode FROM tblGroup INNER JOIN (((tblFamily INNER JOIN tblIndividual ON tblFamily.FamID = tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID = tblAddress.AdFamID) INNER JOIN tblGroupMembers ON tblIndividual.IndID = tblGroupMembers.GMIndID) ON tblGroup.GroupID = tblGroupMembers.GMGroupID WHERE (((tblGroup.GroupName) Like "*flm*")); Thank you for your help! Kathy R. . |
#6
|
|||
|
|||
SELECT DISTINCT query
Jerry Whittle wrote:
Are you saying that you only want one record for the tblFamily.FamID? If so is that field the primary key field in tblFamily? Also what do you want to see for the reset of the record? For example which individual do you want to see? Hi Jerry, Yes, the tblFamily.FamID is the primary key. Essentially, what I want is to address labels to the "x" family if they have one, or more, family members in FLM. So, if Joe Miller, Sue Miller and Bob Jones are all in FLM, I would like to see (distinct on FamID, not on last name!): Miller Jones From which I would build the string: The Miller Family 123 Main Street Anytown, NY 55555 The Jones Family 456 Elm Avenue Anytown, NY 55555 But I need the individual's information in there because the individual is the one that is a member of a group. The family is not a member of the group. The tblFamily is in a one-to-many relationship with both the tblAddress and tblIndividual. The tblIndividual is in a many-to-many relationship with tblGroup with the joining table tblGroupMembers. I hope that's a bit clearer. If I've made the waters more muddy let me know and I'll try to explain better. Kathy R. |
#7
|
|||
|
|||
SELECT DISTINCT query
Hi Karl,
I need the following fields to make an address label report formatted like: The Dewey family (tblFamily.FamLastName, grouped on unique tblFamily.FamID which is why I thought to use SELECT DISTINCT) 123 Main Street (tblAddress.Street) Sometown, NY 55555 (tblAddress.City, State, Zip) WHERE one or more members of the family is a member of FLM (tblGroup.GroupName) which then involves the tblIndividual, tblGroup, and tblGroupMembers Individual and Groups are in a many-to-many relationship with GroupMembers being the joining table, and Family and Individual are in a one-to-many relationship. Let me know if I need to explain myself better or you need to see the table structure. Thank you for your help! Kathy R. tblIndividual KARL DEWEY wrote: Did it again -- SELECT tblFamily.FamID FROM tblFamily GROUP BY tblFamily.FamID; |
#8
|
|||
|
|||
SELECT DISTINCT query
SELECT DISTINCT tblFamily.FamLastName,
tblAddress.Street, tblAddress.City, tblAddress.State, tblAddress.ZipCode FROM tblGroup INNER JOIN (((tblFamily INNER JOIN tblIndividual ON tblFamily.FamID = tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID = tblAddress.AdFamID) INNER JOIN tblGroupMembers ON tblIndividual.IndID = tblGroupMembers.GMIndID) ON tblGroup.GroupID = tblGroupMembers.GMGroupID WHERE (((tblGroup.GroupName) Like "*flm*")); -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kathy R." wrote: Jerry Whittle wrote: Are you saying that you only want one record for the tblFamily.FamID? If so is that field the primary key field in tblFamily? Also what do you want to see for the reset of the record? For example which individual do you want to see? Hi Jerry, Yes, the tblFamily.FamID is the primary key. Essentially, what I want is to address labels to the "x" family if they have one, or more, family members in FLM. So, if Joe Miller, Sue Miller and Bob Jones are all in FLM, I would like to see (distinct on FamID, not on last name!): Miller Jones From which I would build the string: The Miller Family 123 Main Street Anytown, NY 55555 The Jones Family 456 Elm Avenue Anytown, NY 55555 But I need the individual's information in there because the individual is the one that is a member of a group. The family is not a member of the group. The tblFamily is in a one-to-many relationship with both the tblAddress and tblIndividual. The tblIndividual is in a many-to-many relationship with tblGroup with the joining table tblGroupMembers. I hope that's a bit clearer. If I've made the waters more muddy let me know and I'll try to explain better. Kathy R. . |
#9
|
|||
|
|||
SELECT DISTINCT query
Jerry,
This worked great. Thank you! Could you indulge me just a bit further and explain why it works? As I see it, the "SELECT DISTINCT" is choosing distinct records that have all five fields the same (FamLastName and address info). So, if I had Jones and Smith residing at the same address, they would both show up. Is this correct? I always thought I needed the field that is in the "WHERE" clause, in the "SELECT" clause also. Apparently not! Thanks again! Kathy R. Jerry Whittle wrote: SELECT DISTINCT tblFamily.FamLastName, tblAddress.Street, tblAddress.City, tblAddress.State, tblAddress.ZipCode FROM tblGroup INNER JOIN (((tblFamily INNER JOIN tblIndividual ON tblFamily.FamID = tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID = tblAddress.AdFamID) INNER JOIN tblGroupMembers ON tblIndividual.IndID = tblGroupMembers.GMIndID) ON tblGroup.GroupID = tblGroupMembers.GMGroupID WHERE (((tblGroup.GroupName) Like "*flm*")); |
Thread Tools | |
Display Modes | |
|
|