Access 2003 - Left Join won't work ... WHY?

[Alan Lane]

Hello world:

I have 2 tables. Table 1 has Store Information (tblStoreInfo) and contains
123 records. Primary key is STORE4_ID. Table 2 has Gross Addition
Information (tblDTTD_GA), by date and by store. It contains thousands of
records. Foreign key is STORE4_ID. They are related one-to many on
STORE4_ID.

I want to do a query which will give me the Gross Additions for all 123
stores for December 2004, whether or not they had any Gross Additions.
(Actually, only 114 stores had GA activity in December).

I have tried many different things, but I can't get Access to give me more
than 114 records for December. What am I doing wrong? Code is below.
Thanks for any help on this question!

Alan

SELECT
NZ(tblDTTD_GA.PERIOD,0) AS Period,
NZ(tblDTTD_GA.STORE4_ID,0) AS [Store4 ID],
NZ(tblStoreInfo.STORE_NAME,'unknown') AS [Store Name],
NZ(tblStoreInfo.STORE_TYPE,'unknown') AS [Store Type],
NZ(tblDTTD_GA.DTTD_ALLOC_GA,0) AS [Gross Addition]

FROM
tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.STORE4_ID = tblDTTD_GA.STORE4_ID

WHERE
(((tblDTTD_GA.PERIOD)='200412'))

ORDER BY
tblDTTD_GA.STORE4_ID;

[Douglas J. Steele]

It's because of the Where clause. The stores which don't have any records
for the period in question are being eliminated because you're not setting
their Period.

Try changing it to

WHERE
(((Nz(tblDTTD_GA.PERIOD), '200412') = '200412'))


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Alan Lane" <al****@san.rr.com> wrote in message
news:Nu*******************@twister.socal.rr.com...

Hello world:

I have 2 tables. Table 1 has Store Information (tblStoreInfo) and
contains 123 records. Primary key is STORE4_ID. Table 2 has Gross
Addition Information (tblDTTD_GA), by date and by store. It contains
thousands of records. Foreign key is STORE4_ID. They are related one-to
many on STORE4_ID.

I want to do a query which will give me the Gross Additions for all 123
stores for December 2004, whether or not they had any Gross Additions.
(Actually, only 114 stores had GA activity in December).

I have tried many different things, but I can't get Access to give me more
than 114 records for December. What am I doing wrong? Code is below.
Thanks for any help on this question!

Alan

SELECT
NZ(tblDTTD_GA.PERIOD,0) AS Period,
NZ(tblDTTD_GA.STORE4_ID,0) AS [Store4 ID],
NZ(tblStoreInfo.STORE_NAME,'unknown') AS [Store Name],
NZ(tblStoreInfo.STORE_TYPE,'unknown') AS [Store Type],
NZ(tblDTTD_GA.DTTD_ALLOC_GA,0) AS [Gross Addition]

FROM
tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.STORE4_ID = tblDTTD_GA.STORE4_ID

WHERE
(((tblDTTD_GA.PERIOD)='200412'))

ORDER BY
tblDTTD_GA.STORE4_ID;

[Alan Lane]

Hi Doug:

That worked like a charm! Many thanks!

Alan

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:-f********************@rogers.com...

It's because of the Where clause. The stores which don't have any records
for the period in question are being eliminated because you're not setting
their Period.

Try changing it to

WHERE
(((Nz(tblDTTD_GA.PERIOD), '200412') = '200412'))


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Alan Lane" <al****@san.rr.com> wrote in message
news:Nu*******************@twister.socal.rr.com...

Hello world:

I have 2 tables. Table 1 has Store Information (tblStoreInfo) and
contains 123 records. Primary key is STORE4_ID. Table 2 has Gross
Addition Information (tblDTTD_GA), by date and by store. It contains
thousands of records. Foreign key is STORE4_ID. They are related one-to
many on STORE4_ID.

I want to do a query which will give me the Gross Additions for all 123
stores for December 2004, whether or not they had any Gross Additions.
(Actually, only 114 stores had GA activity in December).

I have tried many different things, but I can't get Access to give me
more than 114 records for December. What am I doing wrong? Code is
below. Thanks for any help on this question!

Alan

SELECT
NZ(tblDTTD_GA.PERIOD,0) AS Period,
NZ(tblDTTD_GA.STORE4_ID,0) AS [Store4 ID],
NZ(tblStoreInfo.STORE_NAME,'unknown') AS [Store Name],
NZ(tblStoreInfo.STORE_TYPE,'unknown') AS [Store Type],
NZ(tblDTTD_GA.DTTD_ALLOC_GA,0) AS [Gross Addition]

FROM
tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.STORE4_ID = tblDTTD_GA.STORE4_ID

WHERE
(((tblDTTD_GA.PERIOD)='200412'))

ORDER BY
tblDTTD_GA.STORE4_ID;