One-to-Many link shows duplicate records in query.

I've got a query that uses form parameters to display a list of records.
Worked great until the client requested that we reference data on the many
side of a one-to-many join.

Now the query shows multiple records for each record on the many side of the
linked table.

What is the best way to limit the query to show only one line for each
record in the primary table?

Any suggestions appreciated.

"Scott A" wrote in message
...
I've got a query that uses form parameters to display a list of records.
Worked great until the client requested that we reference data on the many
side of a one-to-many join.

Now the query shows multiple records for each record on the many side of
the
linked table.

What is the best way to limit the query to show only one line for each
record in the primary table?

Any suggestions appreciated.

What is it that you are trying to do? Do you actually want to only show one
of the records from the many side? What criteria do you want to use to
decide which one record is the one you want to display? Or did you want to
aggregate the data from all the records and display, for instance, totals?

There is no way to display all of the records on the many side and then only
have one line on the one side.

HTH;

Amy

Did you do a left join or inner join? If you did not then that is your
problem.
--
KARL DEWEY
Build a little - Test a little


"Scott A" wrote:

I've got a query that uses form parameters to display a list of records.
Worked great until the client requested that we reference data on the many
side of a one-to-many join.

Now the query shows multiple records for each record on the many side of the
linked table.

What is the best way to limit the query to show only one line for each
record in the primary table?

Any suggestions appreciated.

On Fri, 18 May 2007 08:22:00 -0700, Scott A
wrote:

What is the best way to limit the query to show only one line for each
record in the primary table?

Well, DON'T show users query datasheets.

Instead, display the result in a Form (for the primary table) with a Subform
(for the related table).

John W. Vinson [MVP]

Good questions - I'm not actually showing any of the data from the many side
of the relationship on the form itself. I'm using the value on the many side
as a parameter. So because I'm not showing any data from the many side, I
don't really care which of the records returned by the query is shown, I
basically want the query to ignore or skip records where there are multiple
records on the many side.

Not doing any aggregates or totals.

"Amy Blankenship" wrote:


"Scott A" wrote in message
...
I've got a query that uses form parameters to display a list of records.
Worked great until the client requested that we reference data on the many
side of a one-to-many join.

Now the query shows multiple records for each record on the many side of
the
linked table.

What is the best way to limit the query to show only one line for each
record in the primary table?

Any suggestions appreciated.

What is it that you are trying to do? Do you actually want to only show one
of the records from the many side? What criteria do you want to use to
decide which one record is the one you want to display? Or did you want to
aggregate the data from all the records and display, for instance, totals?

There is no way to display all of the records on the many side and then only
have one line on the one side.

HTH;

Amy

"Scott A" wrote in message
...
Good questions - I'm not actually showing any of the data from the many
side
of the relationship on the form itself. I'm using the value on the many
side
as a parameter. So because I'm not showing any data from the many side, I
don't really care which of the records returned by the query is shown, I
basically want the query to ignore or skip records where there are
multiple
records on the many side.

Could you give a bit more detail here?

The continuous form shows a list of students. I'm using unbound combo boxes
on the form to select parameters. Most of the parameters are unique to each
record, however there is one parameter - hobbies. The Hobbies table is
associated with the Students table with a one-to-many relationship.

The continuous form should show each student only once. Right now each
student shows up once for each hobbie they've been assigned in the database.

My question is how to limit the query to show only one record for each
student while still returning the values that will allow me to filter the
list for Hobbies.

Just an analogy for simplicity's sake. Here's the SQL. Last item in the
FROM clause is the "Hobbies" value, WHERE clause includes all the variables
for the form parameters:

SELECT tblSOPs.SOPID, tblSOPs.SOPCode, tblSOPs.SOPTitle, tblSOPs.SOPVersion,
tblSOPs.SOPAuthorID, tblSOPs.SOPStartDate, tblSOPs.SOPEndDate,
tblSOPs.SOPCategoryID, tblCategories.CategoryCode, tblSOPs.SOPSubCategoryID,
tblSubCategories.CategoryCode, tblSOPTypes.SOPTypeCode,
tblSubCategories.CategoryDescription, tblSOPs.SOPStatus,
tblSOPTypes.SOPTypeID, tblCategories.CategoryID, tblSubCategories.CategoryID
FROM (tblSOPTypes RIGHT JOIN (tblCategories AS tblSubCategories RIGHT JOIN
(tblCategories RIGHT JOIN tblSOPs ON tblCategories.CategoryID =
tblSOPs.SOPCategoryID) ON tblSubCategories.CategoryID =
tblSOPs.SOPSubCategoryID) ON tblSOPTypes.SOPTypeID = tblSOPs.SOPTypeID) LEFT
JOIN tblSOPGXP ON tblSOPs.SOPID = tblSOPGXP.SOPID
WHERE (((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType])) OR
(((tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSOPGXP.GXPTypeID)=[Forms]![fmnuSOPList]![cboGXPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSubCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPSub]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblCategories.CategoryID)=[Forms]![fmnuSOPList]![cboSOPCat]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND
((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND ((tblSOPTypes.SOPTypeID)=[Forms]![fmnuSOPList]![cboSOPType]) AND
(([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR
(((tblSOPs.SOPStatus)=[Forms]![fmnuSOPList]![cboSOPStatus] And
(tblSOPs.SOPStatus)5) AND (([Forms]![fmnuSOPList]![cboSOPType]) Is Null)
AND (([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null)) OR (((tblSOPs.SOPStatus)5)
AND (([Forms]![fmnuSOPList]![cboSOPStatus]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPType]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPCat]) Is Null) AND
(([Forms]![fmnuSOPList]![cboSOPSub]) Is Null) AND
(([Forms]![fmnuSOPList]![cboGXPType]) Is Null))
ORDER BY tblSOPs.SOPCode;


"Amy Blankenship" wrote:


"Scott A" wrote in message
...
Good questions - I'm not actually showing any of the data from the many
side
of the relationship on the form itself. I'm using the value on the many
side
as a parameter. So because I'm not showing any data from the many side, I
don't really care which of the records returned by the query is shown, I
basically want the query to ignore or skip records where there are
multiple
records on the many side.

Could you give a bit more detail here?

"Scott A" wrote in message
...
The continuous form shows a list of students. I'm using unbound combo
boxes
on the form to select parameters. Most of the parameters are unique to
each
record, however there is one parameter - hobbies. The Hobbies table is
associated with the Students table with a one-to-many relationship.

The continuous form should show each student only once. Right now each
student shows up once for each hobbie they've been assigned in the
database.

My question is how to limit the query to show only one record for each
student while still returning the values that will allow me to filter the
list for Hobbies.

Just an analogy for simplicity's sake. Here's the SQL. Last item in the
FROM clause is the "Hobbies" value, WHERE clause includes all the
variables
for the form parameters:


I'd build the SQL dynamically in a function to something like (I'm not even
going to try to parse through that mammoth SQL to unravel it, so I'll go
with your analogy

Select Students.FirstName, Students.LastName, Students.Etc FROM Students
WHERE Students.StudentID IN (SELECT StudentHobbies.StudentID FROM
StudentHobbies WHERE StudentHobbies.HobbyID = YourForm!YourControl.Value);

HTH;

Amy