Results 1 to 5 of 5
  1. #1
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47

    Report has duplicate records from subform

    Greetings, WorkOrderF has two subforms (WorkOrderLaborF / WorkOrderPartF). The both subforms will have the same laborer/part recorded multiple times at different dates. I have totaled both subforms in different queries (WorkOrderLaborT Query / WorkOrderPartT Query) they work fine. I made a query for the WorkOrderF (WorkOrderPrintQ) to pull all the info for the report. The problem is when I put the 3 queries into a report, I get duplicate records. Any suggestions on how to correct this? Attached db copy for reference

    MooreV4-1.zip

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks like you are summing summed values, but can't see where. Suggest you start building your WorkOrderPrintQ again, a table at a time, checking the results after adding each table to see which one(s) cause the problem

  3. #3
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47
    Thanks for the suggestion. So, the issue happens when the parts are added.
    Click image for larger version. 

Name:	Screenshot 2022-09-01 135723.png 
Views:	17 
Size:	37.0 KB 
ID:	48619 Click image for larger version. 

Name:	Screenshot 2022-09-01 135908.png 
Views:	17 
Size:	42.6 KB 
ID:	48620
    I cant figure out how to stop the labor duplicates

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this is one of the problems you get when you keep 'transactions' in separate tables.

    Think your only option is to create two sub reports, one for labour and one for parts

    edit - or perhaps use a union query to combine the two queries - aircode

    Code:
    SELECT WorkOrder, Employee, hrs, null as partiID, null as Qty FROM qry1
    UNION ALL SELECT Workorder, null, null, partID, Qty FROM qry2

  5. #5
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Just like on your form, use sub reports. The main report will be workorder and make a sub report for each query.
    See Rapport1 in my example database.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Duplicate Records in a Subform
    By Bentley in forum Forms
    Replies: 3
    Last Post: 09-12-2017, 10:56 AM
  2. Replies: 10
    Last Post: 09-20-2016, 03:25 PM
  3. Duplicate Records Form and Subform
    By Michael Hilton in forum Access
    Replies: 0
    Last Post: 10-29-2013, 02:00 PM
  4. Replies: 2
    Last Post: 05-02-2012, 09:16 PM
  5. Replies: 4
    Last Post: 12-13-2010, 05:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums