unique records in query of one to many linked tables

[Roddy B]

I am creating a select query from which to generate labels. I am using two
tables with a one to many relationship. The label fields are in the primary
table and the sorting field [DateRequest] is in the secondary table. Because
many of the primary records have multilple linked records in the secondary
table the query repeats name, address etc. multiple times. The current code
looks like this:

SELECT tblChildren.[ID#], [PFirstName] & " " & [PLastName] AS PFullName,
[MCity] & ", " & [MState] & " " & [MZip] AS CSZ, tblChildren.MAddress,
tblCrisisAsst.DateRequest
FROM tblChildren INNER JOIN tblCrisisAsst ON tblChildren.[ID#] =
tblCrisisAsst.[ID#]
WHERE (((tblCrisisAsst.DateRequest) Between #1/1/2006# And #12/31/2006#));

How can I make the query filter so the primary record information (name,
address etc.) is only listed once?

Thanks.

 

[Roddy B]

Thanks. I had done that before and tried it again this morning, but I still
get a repeating of the records. Hence my seeking the advise of someone in
this group. I assumed that was the wrong procedure.

The tables are linked thru the common field ID#. Could the difficulty be
related to the nature of the link? Also this is a split database. Back end
on the server and front in on the C: drive.

Any input is greatly appreciated!

 

[John Vinson]

Thanks. I had done that before and tried it again this morning, but I still
get a repeating of the records. Hence my seeking the advise of someone in
this group. I assumed that was the wrong procedure.

aha... I had overlooked one key thing. You need to REMOVE
tblCrisisAsst.DateRequest from the SELECT clause (or, equivalently,
uncheck the Show box in the query grid). It's being included in the
SELECT DISTINCT giving you one record for each date; the field should
only appear in the WHERE clause. Try

SELECT DISTINCT tblChildren.[ID#], [PFirstName] & " " & [PLastName] AS
PFullName,
[MCity] & ", " & [MState] & " " & [MZip] AS CSZ, tblChildren.MAddress
FROM tblChildren INNER JOIN tblCrisisAsst ON tblChildren.[ID#] =
tblCrisisAsst.[ID#]
WHERE (((tblCrisisAsst.DateRequest) Between #1/1/2006# And
#12/31/2006#));

John W. Vinson[MVP]

 

[Roddy B]

Perfect! That did the trick.
Thanks for taking the time to help me out!

Thanks. I had done that before and tried it again this morning, but I still
get a repeating of the records. Hence my seeking the advise of someone in
this group. I assumed that was the wrong procedure.

aha... I had overlooked one key thing. You need to REMOVE
tblCrisisAsst.DateRequest from the SELECT clause (or, equivalently,
uncheck the Show box in the query grid). It's being included in the
SELECT DISTINCT giving you one record for each date; the field should
only appear in the WHERE clause. Try

SELECT DISTINCT tblChildren.[ID#], [PFirstName] & " " & [PLastName] AS
PFullName,
[MCity] & ", " & [MState] & " " & [MZip] AS CSZ, tblChildren.MAddress
FROM tblChildren INNER JOIN tblCrisisAsst ON tblChildren.[ID#] =
tblCrisisAsst.[ID#]
WHERE (((tblCrisisAsst.DateRequest) Between #1/1/2006# And
#12/31/2006#));

John W. Vinson[MVP]