Results 1 to 9 of 9

Query to include all records from one table and matching records from another

  1. #1
    sugr74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    24

    Query to include all records from one table and matching records from another

    I need a query to produce all the records from one table and matching records from another table, if no matching records then assume its a zero.

    the report will be a rolling order book so includes the order value from one table, and then will show the corresponding invoice value from the other table, if no invoice has been raised then assume a zero value.

    I just can't seem to get my head around it!



    TIA

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,544
    You will want to use a LEFT JOIN (see: https://support.office.com/en-us/art...1-c701e9f7f5fb).
    If you click on the Join line between the two Tables in the Query Builder, you can easily change this. The descriptions next to the radio button make it very evident of which one you need to select.

    Regarding pulling back values from the other table, use the NZ function, which you can tell it what to return in the event that there is no match, i.e.
    Code:
    NZ([FieldName],0)+0
    The reason why we add 0 is that NZ returns a text value, so adding 0 to zero coerces it to return the number 0 instead of the text element 0.

    See here for more on NZ: https://www.techonthenet.com/access/...dvanced/nz.php
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    sugr74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    24
    Where in the query would the NZ go?

    SELECT Orders.[Order Entry Date], Orders.[Job Number], Orders.[Customer Name], Orders.[Full site address], Orders.[Order Value], Orders.[Door type], [Outstanding Balance Good].[SumOfInvoice Total], Orders.[G C %], [Order Value]-[SumOfInvoice Total] AS [Outstanding Balance], (([Order Value]-[SumOfInvoice Total])*[G C %])/100 AS [Oustanding G C]
    FROM (Orders INNER JOIN [Invoice details] ON Orders.[Job Number] = [Invoice details].[Job Number]) INNER JOIN [Outstanding Balance Good] ON Orders.[Job Number] = [Outstanding Balance Good].[Job Number];

    TIA

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,544
    The fields from the "other" table. I assume that is your "INVOICE DETAILS" table, but it appears that you have not changed your JOIN type yet.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    sugr74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    24
    If no invoice has been raised i want it to still show the job details and the full value outstanding. I have changed the inner join to a left join

    TIA

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,111

  7. #7
    sugr74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    24
    Thank you for that

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,544
    If no invoice has been raised i want it to still show the job details and the full value outstanding. I have changed the inner join to a left join
    I know. That is exactly what doing the LEFT JOIN does. It says "take ALL records from the one table, even if there is no match".
    The NZ is to tell it what to return for that field in the case where there is no match.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  9. #9
    sugr74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    24
    I don't know how to use the NZ. Two fields from the invoice details table are currency, it then performs a subtraction to give a balance. If there is no matching record I need the currency fields to be zero not blank?

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

Similar Threads

  1. Replies: 4
    Last Post: 04-06-2018, 01:37 AM
  2. Replies: 13
    Last Post: 05-09-2017, 11:43 PM
  3. Replies: 4
    Last Post: 03-29-2014, 12:29 AM
  4. Replies: 4
    Last Post: 12-30-2013, 12:49 PM
  5. Replies: 1
    Last Post: 10-24-2011, 07:01 AM

Posting Permissions

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