Results 1 to 11 of 11
  1. #1
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376

    New problem with a report - Data not appearing that does appear in Query

    Hello All,

    I created a new small database for tracking Inspection of labels printed. I had some issues at first that were quickly resolved with input from the great folks on this forum.

    I was putting in some real data today and found some errors (omissions really) in a query and the resulting report. I am using "Query1" (Sorry I should have deleted the other query before zipping the file.) I didn't realize there were omissions at first but after putting in 11 records I saw that some were missing on the report. Further looking into it LabelID's 3 and 12 are missing from the report.

    If you run the report "Label Report All" (which uses "Query1") you get the report showing 9 records, if you look at "tblLabels" you see there are 11 records and the LabelID shows 1-10 & 12, LabelID 11 is missing (not sure why) for a total of 11 records.

    Any idea why the report is matching the data in the tables, or the query?

    (Edit) - After creating a new query without reject info (because the last entry had no rejects, I got all 11 records when I ran the query. This won't do for a report though because I want all records, those with and without rejects to show on the report.

    Thanks

    Dave



    Label Inspection - New.zip
    Last edited by Dave14867; 12-04-2017 at 07:04 PM. Reason: New info

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    With all the joins, there's something in the label table not present in a joined table. At a glance it looks like the errors table. You can try editing the join, but you'll probably have to edit more than one to get around the ambiguous joins error. Probably the two joins to the errors table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This appears to work:

    SELECT tblLabels.DhrNum, tblLabels.LotNum, tblLabels.DHrRev, tblLabels.LabelPN, tblLabels.LabelRev, tblLabels.LabelQty, tblLabelErrors.RejQty, tblRejectCodes.RejCode, tblRejectCodes.Description, tblPrinters.PrinterNum, tblLabelSize.LabelSize, tblLabels.LabelDate, tblLabels.[Insp By], tblLabels.LabelID
    FROM tblRejectCodes RIGHT JOIN (tblPrinters INNER JOIN ((tblLabelSize INNER JOIN tblLabels ON tblLabelSize.LabelSizeID = tblLabels.LabelSizeID) LEFT JOIN tblLabelErrors ON tblLabels.LabelID = tblLabelErrors.LabelID) ON tblPrinters.PrinterID = tblLabels.PrinterID) ON tblRejectCodes.RejectID = tblLabelErrors.RejectID;
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    pbaldy,

    I am not sure what could be missing with LabelID 3, the first 10 entries were all lots with rejected labels, LabelID12 however does not have any rejected labels.

    Why does the report not reflect what is in the results of the query? Should the report reflect what is in the query?

    Thanks

    Dave

  5. #5
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Pbaldy,

    What was missing in the original? I am not familiar with SQL at all ( but it appears I have to get that way quickly). I copied what you posted into a new query and it does appear to give the desired output.

    The report however is still lacking data. I copied my original report, opened it in design view, changed to data source to the new query I created with your code and still don't get the results of the query in the report. What am I missing?

    Thanks

    Dave

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The report groups on LotNum, and there is a duplicate there. The report shows you the 10 lot numbers in the query. The detail section shows 2 items for the one duplicated. Everything is there.

    If you double click or right click on the join lines in design view, you'll see the options available. Choosing one there changes the INNER, LEFT or RIGHT that you see in SQL view.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In different terms, your query now returns 11 records. If you count the Rej Qty field, you'll see 11 (including the blank for the label with no record in the reject table). Thus all 11 records are on the report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    pbaldy,

    Yes, I didn't realize that looking at it quickly, sorry. I changed the group to LabelID so that won't happen in the future.

    BTW - Who is John Galt? Where is he? Is that his real name?

    Have seen the movies and loved them.

    Thanks

    Dave

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    A character in the Ayn Rand novel Atlas Shrugged. That was a tag line in the book.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    pbaldy,

    Yes I knew that, But where is he now? How do any of us find him?

    I have seen all 3 movies and they were great

    Also, thank you for assisting, That has solved my problem (for now).

    I really appreciate you folks helping out those like me that need it. Hopefully I'll be able to contribute on some problems in time and return the favor and keep things going

    I can't thank you enough.

    This is solved.

    Thanks

    Dave

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Perhaps we're meant to find him within ourselves?

    Happy to help Dave!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Criteria option not appearing when creating report
    By PATRICKPBME in forum Reports
    Replies: 6
    Last Post: 07-05-2017, 07:12 AM
  2. Replies: 5
    Last Post: 04-02-2017, 04:55 PM
  3. Data not appearing now in form
    By Waterdog in forum Forms
    Replies: 13
    Last Post: 08-28-2015, 12:57 PM
  4. Border not appearing on blank fields - Report
    By jmitchelldueck in forum Reports
    Replies: 1
    Last Post: 08-26-2015, 08:15 AM
  5. Replies: 13
    Last Post: 12-23-2012, 02:09 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