duplicate records in report due to multiple subtables.... (1 Viewer)

QMDirk

Member
Local time
Today, 06:35
Joined
Nov 16, 2019
Messages
43
I have a report whose Source = 2 tables in a query where they share a common field: "Order Number". There is a 3rd table that adds the package size information. The Report is set up shows the product that is being ordered at the bottom and a third table shows the shipping dimensions of the package and contains the Pallet number. The pallet number is staged to the PO number and tied to the financial side. So, where I have 2 unique entries in 2 different tables, I end up with duplicate listings of the product and lot. I even checked the "Unique Value" in the Query/Properties window and that didn't stop it. Any suggestions?
 

plog

Banishment Pending
Local time
Today, 08:35
Joined
May 11, 2011
Messages
9,934
You posted in the Forms section, talk about a Report, but from what I see the real issue is a query. Queries are where you get the data, reports are really just formatting. Since you are having issues with the correct data, let's talk in terms of tables and queries.

Can you post sample data? Please provide 2 sets of data to demonstrate what you have in your tables and what your query should produce:

A. Starting data from your table(s). Include tab le and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect your query to produce when you feed in the data from A.

Again, 2 sets of data--starting and expected results.
 

QMDirk

Member
Local time
Today, 06:35
Joined
Nov 16, 2019
Messages
43
sorry for posting in the wrong forum, I try to be more aware of that. I think you're right though, because when I just run the report, it shows just the 2 entries from the table. But I have a button setup to run the report for the specified record, attach it to an email, and send it to the person who will input the order into system to generate an invoice. It's imperative that my report is accurate, so, for now I'm just explaining to them to watch for duplicate entries. I'd rather fix it properly though. I'll get some more data and post it tomorrow. Thanks.
 

arnelgp

error reading drive A:
Local time
Today, 21:35
Joined
May 7, 2009
Messages
9,925
on the Query of the report, did you Join the 2 tables on "Order Number" field?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:35
Joined
Feb 19, 2002
Messages
29,179
Just because several tables have a common field doesn't mean that you can use all of them in a single query. What it sounds like is you have two separate relationships rather than one hierarchical relationship. Here's a silly example that shows what I mean by two separate relationships. We havt tblStudents, tblClasses, and tblPets. All three tables include StudentID so in theory, they could all be joined in a single query. However, classes and pets have nothing to do with each other and will cause "duplication" in the resultset.
Sam, Math,Cat
Sam, Math, Dog
Sam, English, Cat
Sam, English, Dog

This is two separate relationships:
Students-->Classes
Students--> Pets

This is a three table join which will make sense:
Students-->Classes-->Teacher
Sam, Math, Mr. Smith
Sam, English, Mrs. Sexton
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom