Showing fields i want to show even when no value

Topic

New Reply

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!!!

Reply | Quote

Replies

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 (…)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote