A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SELECT DISTINCT query



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2010, 07:41 PM posted to microsoft.public.access.queries
Kathy R.
external usenet poster
 
Posts: 20
Default 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  
Old May 14th, 2010, 09:22 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old May 14th, 2010, 09:24 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 14th, 2010, 09:25 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 14th, 2010, 09:26 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 14th, 2010, 09:34 PM posted to microsoft.public.access.queries
Kathy R.
external usenet poster
 
Posts: 20
Default 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  
Old May 15th, 2010, 04:23 PM posted to microsoft.public.access.queries
Kathy R.[_3_]
external usenet poster
 
Posts: 25
Default 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  
Old May 17th, 2010, 04:14 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old May 17th, 2010, 06:02 PM posted to microsoft.public.access.queries
Kathy R.
external usenet poster
 
Posts: 20
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.