Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73

    Join Not Displaying Correctly

    I have query with a join that is not displaying 3 lines and I don't know why. I had previously posted something similar to this and tried to use that response to correct this problem but I can't figure out why these lines are not showing in the query. I need all lines from both the table and the query to display in my output even if they only exist on one of the sources. The lines that are not displaying are on the "Aging_Billing_Query" but not on the "Service_Channel" tables. I have other lines that are on the "Aging_Billing_Query" but not on the "Service_Channel" table that are showing up so I am very confused about the disconnect. I am not very knowledgeable in Access so please be gentle on the terms that you use when responding to me... . I did try to make it a left outer join and the output is exactly the same.




    SELECT DISTINCT [Aging_ Billers_Query].FB, Service_Channel.Client, Service_Channel.LocationName AS Location, Service_Channel.[W#O##] AS [Work Order #], [Aging_ Billers_Query].[PO #], Service_Channel.Status AS [Web Status], [Aging_ Billers_Query].Biller, Service_Channel.[Inv##] AS [Uploaded Inv #], Service_Channel.[Inv#Amount] AS [Amt Entered], [Aging_ Billers_Query].[Inv #] AS [Aging Inv #], [Aging_ Billers_Query].[Inv Amt], IIf([Inv #]=[Inv##],"Match","Mismatch") AS Mismatch, IIf([Inv#Amount]=[Inv Amt],"Match","Mismatch") AS [Mismatch Var], "Service Channel" AS Portal, IIf([mismatch]="mismatch" And [Aging Inv #] Is Null,"PAID") AS PAID
    FROM Service_Channel LEFT JOIN [Aging_ Billers_Query] ON Service_Channel.[W#O##] = [Aging_ Billers_Query].[PO #];

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    IsNull([Aging Inv #]),"paid")

    the useage: [field] is null , is only used in a query criteria, otherwise it's: IsNull(field])

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Actually, either will work in IIf() in query. That is not cause of issue.

    Need to provide data for analysis. If you want to provide db, follow instructions at bottom of my post.

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    The tables that I am using are linked so if I were to add the DB, it wouldn't retrieve the data. Suggestions?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Make a copy and import tables necessary for the issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    OK, give a day or so to play with it. There is a lot of data on there that is confidential and I need to figure out how to change it without screwing up the database.

  7. #7
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I have attached the database.
    Attached Files Attached Files

  8. #8
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I forgot to mention the exact issues:

    Inv #'s 774332, 801389 & 802310 are listed on the Aging_Billers Query. They are not listed on the second table. The output of the query omits these invoices when they should be included.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Aging table has 57 records and Aging_Billers query has 57 records.

    Service_Channel table has 869 records and Service_Channel query has 870 records.

    There are two records in Aging for PO# 97490971. Is this valid data?



    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Better would be PO_Num.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Yes, the two records showing for PO# 9749071 are correct. Both records are coming from the Aging_Billing query. i am not sure how many records the Service_Channel Query should have in total, but I do know that it is missing at least the 3 records I mentioned above. These 3 records are shown in the Aging_Billing query (
    Inv #'s 774332, 801389 & 802310). These 3 invoices should show in the Aging Inv# column.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Those 3 invoice numbers are not in Service_Channel table and neither are their associated PO# (or W#O## since they are identical). So why would you expect them to show in query?

    Invoice 797782 has PO 95390258 in Aging but 97574364 in Service_Channel.

    Only 53 Aging records have a match in Service_Channel. That's 57 minus the 4 identified above.

    Query is joining records. The query requires all records from Service_Channel and only those from Aging that match. If there is no corresponding value then data cannot be retrieved.

    There are 870 records in the Service_Channel query because there is a duplicate match on PO 9749071 (1 record in Service_Channel linking with 2 records in Aging).

    The queries are functioning correctly, regardless of what you desire to see.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I need to show all the PO’s from
    The Service Channel table and all the invoices listed in the Aging Billimg Query and match it go where is can be matched up. Is that possible?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    UNION query.
    UNION query must be typed or copy/paste in SQLView of query builder.

    SELECT [Inv #] AS InvNum, [PO #] AS PONum FROM Aging
    UNION SELECT [Inv##], [PO#] FROM Service_Channel;

    Now you have a dataset of all possible invoice numbers. UNION won't allow duplicate records.

    You still have a PO mismatch on invoice 797782. This means you will have two records in the UNION for invoice 797782 because there are 2 PO numbers. This makes 2 distinct records.

    Build another query that joins tables to the UNION query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Currently on the Service_Channel Query are fields from Aging_Billers_Query. Can I remove these fields and do a Union query? Also, I know that the two tables or queries that I am making a Union query have to have the same number of columns and same column headers. I don't think this is possible for me to do?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why would you have to remove fields from Aging_Billers_Query? You pull whatever fields you want into UNION. I think my example is pulling from tables, not queries.

    The fields do not have to be the same names in each table. You define the names in the first SELECT line of the UNION.

    Each SELECT line must have the same number of fields. Can use Null as a placeholder if a table does not have data for a field.

    SELECT [Inv #] AS InvNum, [PO #] AS PONum, [Some Field] AS SomeName FROM Aging
    UNION SELECT [Inv##], [PO#], Null FROM Service_Channel;

    Should even work if Null is used on first SELECT.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Left join report not grouping correctly
    By Robyn_P in forum Reports
    Replies: 11
    Last Post: 03-23-2018, 03:02 AM
  2. Left join doesn't work correctly
    By jpunja in forum Queries
    Replies: 3
    Last Post: 10-31-2016, 04:16 PM
  3. Forms not displaying correctly on monitor 2
    By Perceptus in forum Forms
    Replies: 2
    Last Post: 11-06-2014, 01:45 PM
  4. Replies: 8
    Last Post: 12-09-2013, 09:31 AM
  5. Displaying Queries Correctly
    By cgjames in forum Reports
    Replies: 0
    Last Post: 01-18-2011, 06:54 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