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

G

Guest

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.
 
A

Amy Blankenship

Scott A said:
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.
Click to expand...

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
 
J

John W. Vinson

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

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]
 
G

Guest

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.
 
A

Amy Blankenship

Scott A said:
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.
Click to expand...

Could you give a bit more detail here?
 
G

Guest

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;
 
A

Amy Blankenship

Scott A said:
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:
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

running total of records in query 1
Access Query - "Duplicate Records Selected" 5
Left join in query 12
deleting duplicate records obtained from a duplicate query 4
Append Query Issue 0
Query based on Query 1
One-to-Many join act as a One-to-One join 2
Create update query for remaining records 10

Top