SELECT DISTINCT query

[Kathy R.]

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.

 

[Jerry Whittle]

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?

 

[KARL DEWEY]

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?

 

[KARL DEWEY]

ERROR (I saw it as it went out the door).
SELECT tblFamily.FamID
FROM SELECT tblFamily
GROUP BY tblFamily.FamID;

 

[KARL DEWEY]

Did it again --
SELECT tblFamily.FamID
FROM tblFamily
GROUP BY tblFamily.FamID;

 

[Kathy R.]

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.

 

[Kathy R.]

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

 

[Jerry Whittle]

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*"));

 

[Kathy R.]

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.