Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Showing fields i want to show even when no value

    Hey guys, I know very very little about Access, and what i do know I have learned from scrolling the internet. But I have finally found something that I can't find an answer to.

    I have four fields in a query:

    BILLING DATE

    NAME CONTROL

    REPORT GROUP CODE

    COST EXT (SUM)

    I'm using the billing date to set a date parameter (>1/1/2015). The report group code has hundreds of different codes, but I only want them to show 11 specific ones. But I need them to show up even if the Query doesn't return a valuThe cost ext field is a sum of the total instances of each report group code, so 0 is fine.

    So the problem I have is this, I need to show all 11 instances of REPORT GROUP CODE even when there is null in all of the fields.

    Thanks in advance!!!

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    If you don't have one, you need a separate table that just contains all the codes. You need to create a new query that uses a Left Join to connect this table to your previous query, something like this:

    SELECT ... FROM [codetable] LEFT JOIN [old query] ON [codetable].code = [old query].code WHERE [codetable].code IN (...)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    If you don't have one, you need a separate table that just contains all the codes. You need to create a new query that uses a Left Join to connect this table to your previous query, something like this:

    SELECT ... FROM [codetable] LEFT JOIN [old query] ON [codetable].code = [old query].code WHERE [codetable].code IN (...)

    Mark,

    Thank you very much for your reply. I know nothing about SQL, but i created a table with just those codes, and created a new query in design view and changed to SQL and this is what is there.

    SELECT
    FROM 1RGC LEFT JOIN tblInvoiceDetailTable ON [1RGC].[REPORT GROUP CODE] = tblInvoiceDetailTable.[REPORT GROUP CODE]

    Do i need to add the fields from the original query back in in design view? When I tried that, the missing codes still did not come up. Thank you for your patience, and I apologize for not knowing what I'm doing.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    If you populated 1RGC with just the codes you wanted, you don't need a LEFT JOIN, you could have used an INNER JOIN. My suggested use of the LEFT JOIN assume you already had a table with ALL the codes in it, and you'd use the IN statement to select the ones you wanted.

    You should be able to add the fields from tblInvoiceDetailTable to the query in Design View.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •