Query difficulties

[jgelpi16]

I have the below query that is not returning the results desired. What I want is to return all stores that are in PUBLIC_ADC_GLP_LEDGER, but not in tblWeeklySales (for the specific POST_DATE). My below query returns/appends nothing. Thanks in advance for your help!

INSERT INTO tblWeeklySales ( STORENUM, POST_DATE, VALUE016, VALUE017, VALUE018, VALUE019, WeeklySales )
SELECT PUBLIC_ADC_GLP_LEDGER_TAB.STORENUM, PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE, Sum(PUBLIC_ADC_GLP_LEDGER_TAB.VALUE016) AS VALUE016, Sum(PUBLIC_ADC_GLP_LEDGER_TAB.VALUE017) AS VALUE017, Sum(PUBLIC_ADC_GLP_LEDGER_TAB.VALUE018) AS VALUE018, Sum(PUBLIC_ADC_GLP_LEDGER_TAB.VALUE019) AS VALUE019, ([Value016]+[Value017])-([Value018]+[Value019]) AS WeeklySales
FROM PUBLIC_ADC_GLP_LEDGER_TAB LEFT JOIN tblWeeklySales ON (PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE = tblWeeklySales.POST_DATE) AND (PUBLIC_ADC_GLP_LEDGER_TAB.STORENUM = tblWeeklySales.STORENUM)
WHERE (((PUBLIC_ADC_GLP_LEDGER_TAB.DIVISION)=1) AND ((PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE) Is Null And (PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE)>#12/31/2010#) AND ((tblWeeklySales.STORENUM) Is Null))
GROUP BY PUBLIC_ADC_GLP_LEDGER_TAB.STORENUM, PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE
ORDER BY PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE;

[pbaldy]

Off the top of my head:

PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE Is Null And
PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE>#12/31/2010#

A field can not be both Null and greater than a given date.

[jgelpi16]

Very true...I'll have to play around with it more. That may not be the right syntax/logic, but that is what I'm trying to do...

[kennejd]

I think it should be tblWeeklySales.POST_DATE is NULL...not PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE.

[Bwilliamson]

Change this:
((PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE) Is Null And (PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE)>#12/31/2010#)

To this:
((PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE) Is Null OR (PUBLIC_ADC_GLP_LEDGER_TAB.POST_DATE)>#12/31/2010#)

That will satisfy the logic.

[jgelpi16]

I'll give the "OR" a shot. However, I'm trying to do an unmatched query. I want to append any record that is not in tblWeeklySales, but is in my PUBLIC table. The two fields I was trying to test for are the store number and date. Maybe I am going about this entirely wrong?

[pbaldy]

As someone mentioned, if you want to find records not in tblWeeklySales, that's the table that should be referred to.

[jgelpi16]

ok, I'll take another crack at it. Thanks for all the input.

[pbaldy]

I should have clarified, that referred to the "Is Null" test. It was kennejd in post 4 that first pointed it out.