Report contains repeat lines of data... (1 Viewer)

QMDirk

Member
Local time
Today, 01:35
Joined
Nov 16, 2019
Messages
52
This is my first time to post in Reports. I'm usually more concerned with forms. I keep running into this scenario:
say for example, there is 1 table and 1 subtable (or more). the first table table [tProductInfo] has (1) entry. The 1st subtable [stLots] contains 2 (or more) entries that correspond with the primary key field in table tProducts. On top of this, a 3rd table, [tPalletNumbers] contains 10 entries (for the 10 pallets that went on Hold).

the 2nd and 3rd tables have a Many-to-One relationship with tProductInfo.

When I create the report by querying all 3 tables, I will always get multiple entries of the same data. If some of the pallet numbers were for one lot and the rest were for another, I would want to see 2 lines, with each line just summing the total number of pallets. But Access will display a separate row for each row in every table where the primary key is the same as the query.

Any takers? Thanks.
 

Minty

AWF VIP
Local time
Today, 08:35
Joined
Jul 26, 2013
Messages
10,355
There is an option to hide duplicates in reports, and to grow/shrink in text boxes. It doesn't work on forms, but it DOES work on reports.
Have a play, and also look into sub-reports where you can not display on no data.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:35
Joined
May 7, 2009
Messages
19,169
if you have Autonumber or Date/Time fields in your table, don't include them in the Query's output.
 

HalloweenWeed

Member
Local time
Today, 04:35
Joined
Apr 8, 2020
Messages
213
When I create the report by querying all 3 tables
Don't do that. Use a query to query all 3 tables, then use the results in your report. You can then debug your query rather than debugging your report blindly.
 

Users who are viewing this thread

Top Bottom