Not getting null values

[froggiebeckie]

I've found several threads on this subject, but I still can't get it to work, so please forgive me if I seem to be duplicating a question.

I'm making a very simple db. It has only 2 tables:
MATERIAL (one)
TEST RESULTS (many) with left join.

I need to report average test results (by MATERIAL) weekly, even if there were no tests for a particular product that week, ie;

MATERIAL A = 100
MATERIAL B = 90
MATERIAL C =
MATERIAL D = 105

When I try to query, I only get results for A, B and D. It seems to be ignoring the MATERIAL table and looking only at the TEST RESULTS table.

I'm sure it's something simple that I'm overlooking but how do I get C to show, even if it has no results.

Here's my query SQL.:

SELECT DISTINCTROW [MATERIAL Query].MATERIAL, Avg([TEST RESULTS].V50) AS [Avg Of V50]
FROM [MATERIAL Query] LEFT JOIN [TEST RESULTS] ON [MATERIAL Query].MATERIAL = [TEST RESULTS].MATERIAL
WHERE ((([TEST RESULTS].[TEST DATE]) Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![END DATE]))
GROUP BY [MATERIAL Query].MATERIAL;


As always, thanks in advance.
BeckieO

 

[stopher]

The left join is fine. The problem is the WHERE clause. If you remove the WHERE clause you will see the nulls ok. The WHERE clause is specifically looking for dates and ignores nulls.

You need to check for nulls as well as the date range...

Try:

SELECT DISTINCTROW [MATERIAL Query].MATERIAL, Avg([TEST RESULTS].V50) AS [Avg Of V50]
FROM [MATERIAL Query] LEFT JOIN [TEST RESULTS] ON [MATERIAL Query].MATERIAL = [TEST RESULTS].MATERIAL
WHERE [TEST RESULTS].[TEST DATE] Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![END DATE] 
OR [TEST RESULTS].[TEST DATE] IS NULL
GROUP BY [MATERIAL Query].MATERIAL;

hth
Stopher

 

[froggiebeckie]

You Got It!!

Yup, Access just does what we tell it to.

That's the thing that makes me the maddest about Access--every now and then it forces me to realize that logic is not my strongest suit. (hehehe)

Thanks so much for getting back to me so quickly,

BeckieO

 

[neileg]

Ah, yes, but!

If you want to test by date and still have materials with null counts in the results, there's another way.

As you know, in a left join you allways get the left hand table records even if there are nulls in the tables to the right. So what you need is a table (or query) that returns all possible materials. So if [MATERIAL Query].MATERIAL can't guarantee to return a record for every possible material, you will need to add somthing that does.