Access 2003 - Left Join wierdness

[Alan Lane]

Hello world:

Background:
Yesterday, January 21, Doug Steele was kind enough to help me out on a Left
Join problem. I was trying to return all stores and their Gross Adds for
December, 2004 (even though some of them had no Gross Adds for that month).
My query would only show the non-zero GA stores. Doug stated "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."
So, I changed my query per his suggestion, and I got back the correct number
of records (matching the number of stores).

Current Situation:
Weird. I am getting back the correct number of records, but I am not
getting back all the Store ID numbers. I do get the Store Name returned for
all records, which helps somewhat. Some of the zero GA stores show the
Period and Store ID correctly, some don't. Does anyone know what's going
on, and how I can fix it? I need to return all Store ID's and all GA
quantities.

I have 2 tables (tblStoreInfo and tblDTTD_GA) which are linked one-to many
on STORE4_ID.

Here's the Query and the output it produces. Note that I have temporarily
prepended the Store ID to the Store Name, so that the blank Store ID's will
be identified. I am also changing the store names to protect the innocent.
I am only showing those stores with a 0 (Zero) in the GA column. My
apologies for the way the output looks. I hope you will get the idea.

The Query-----------------------------------------------
SELECT
NZ(tblDTTD_GA.P ERIOD,"0") AS Period,
NZ(tblDTTD_GA.S TORE4_ID,"0") AS [Store4 ID],
NZ(tblStoreInfo .STORE_NAME,"un known") AS [Store Name],
NZ(tblDTTD_GA.D TTD_ALLOC_GA,0) AS [Gross Addition]
FROM
tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.ST ORE4_ID=tblDTTD _GA.STORE4_ID
WHERE
NZ(tblDTTD_GA.P ERIOD,"200412") ="200412"
ORDER BY
tblDTTD_GA.PERI OD, tblDTTD_GA.STOR E4_ID;
--------------------------------------------------------

The Output----------------------------------------------
Period Store4 ID Store Name Gross Addition
0 0 3106__Foo 0
0 0 1601__Gat 0
0 0 110P__Pre 0
0 0 1102__Gal 0
0 0 1203__Oak 0
0 0 CRIC__CRI 0
0 0 1000__Rev 0
0 0 0502__UNK 0
0 0 6151__Oak 0
0 0 3104__Kno 0
0 0 2303__The 0
0 0 2801__Car 0
0 0 311P__Pre 0
200412 1101 1101__UNK 0
200412 2007 2007__Cla 0
200412 3301 3301__S C 0
200412 4102 4102__Val 0
200412 5105 5105__Cra 0
200412 5109 5109__Cen 0
200412 5206 5206__Bou 0
200412 6504 6504__Wil 0
--------------------------------------------------------

Help!

Alan

[Salad]

Alan Lane wrote:

Hello world:

Background:
Yesterday, January 21, Doug Steele was kind enough to help me out on a Left
Join problem. I was trying to return all stores and their Gross Adds for
December, 2004 (even though some of them had no Gross Adds for that month).
My query would only show the non-zero GA stores. Doug stated "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."
So, I changed my query per his suggestion, and I got back the correct number
of records (matching the number of stores).

Current Situation:
Weird. I am getting back the correct number of records, but I am not
getting back all the Store ID numbers. I do get the Store Name returned for
all records, which helps somewhat. Some of the zero GA stores show the
Period and Store ID correctly, some don't. Does anyone know what's going
on, and how I can fix it? I need to return all Store ID's and all GA
quantities.

I have 2 tables (tblStoreInfo and tblDTTD_GA) which are linked one-to many
on STORE4_ID.

Here's the Query and the output it produces. Note that I have temporarily
prepended the Store ID to the Store Name, so that the blank Store ID's will
be identified. I am also changing the store names to protect the innocent.
I am only showing those stores with a 0 (Zero) in the GA column. My
apologies for the way the output looks. I hope you will get the idea.

The Query-----------------------------------------------
SELECT
NZ(tblDTTD_GA.P ERIOD,"0") AS Period,
NZ(tblDTTD_GA.S TORE4_ID,"0") AS [Store4 ID],
NZ(tblStoreInfo .STORE_NAME,"un known") AS [Store Name],
NZ(tblDTTD_GA.D TTD_ALLOC_GA,0) AS [Gross Addition]
FROM
tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.ST ORE4_ID=tblDTTD _GA.STORE4_ID
WHERE
NZ(tblDTTD_GA.P ERIOD,"200412") ="200412"
ORDER BY
tblDTTD_GA.PERI OD, tblDTTD_GA.STOR E4_ID;
--------------------------------------------------------

The Output----------------------------------------------
Period Store4 ID Store Name Gross Addition
0 0 3106__Foo 0
0 0 1601__Gat 0
0 0 110P__Pre 0
0 0 1102__Gal 0
0 0 1203__Oak 0
0 0 CRIC__CRI 0
0 0 1000__Rev 0
0 0 0502__UNK 0
0 0 6151__Oak 0
0 0 3104__Kno 0
0 0 2303__The 0
0 0 2801__Car 0
0 0 311P__Pre 0
200412 1101 1101__UNK 0
200412 2007 2007__Cla 0
200412 3301 3301__S C 0
200412 4102 4102__Val 0
200412 5105 5105__Cra 0
200412 5109 5109__Cen 0
200412 5206 5206__Bou 0
200412 6504 6504__Wil 0
--------------------------------------------------------

Help!

Alan

Why don't you break this up into 2 queries, maybe 3.
Query 1
SELECT
NZ(tblDTTD_GA.P ERIOD,"0") AS Period,
STORE4_ID AS [Store4 ID],
FROM tblStoreInfo

Now, do you really want records with blank StoreIDs? If not, add the
criteria
IS NOT NULL
under storeID.

Query2
SELECT
STORE4_ID AS [Store4 ID],
tblDTTD_GA.PERI OD,
STORE_NAME.
NZ(tblDTTD_GA.D TTD_ALLOC_GA,0) AS [Gross Addition]
FROM tblDTTD_GA.STOR E4_ID
WHERE NZ(tblDTTD_GA.P ERIOD,"200412") ="200412"

Now create Query3 and link Query1 to Query2. Do the left join on
[Store4 ID]

[MacDermott]

Well, one thing I see is that you are including STORE4_ID from tblDTTD_GA.
If that ID exists in tblStoreInfo there will be a record for it, but if
there's no record in tblDTTD_GA for that ID, your SQL will return a 0.
Using tblStoreInfo.ST ORE4_ID instead of NZ(tblDTTD_GA.S TORE4_ID,"0") should
solve this problem.
Note that you're drawing the store name from tblStoreInfo, which is why it's
always present.

HTH

"Alan Lane" <aw*****@yahoo. com> wrote in message
news:ip******** **********@twis ter.socal.rr.co m...

Hello world:

Background:
Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for
December, 2004 (even though some of them had no Gross Adds for that month). My query would only show the non-zero GA stores. Doug stated "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."
So, I changed my query per his suggestion, and I got back the correct number of records (matching the number of stores).

Current Situation:
Weird. I am getting back the correct number of records, but I am not
getting back all the Store ID numbers. I do get the Store Name returned for all records, which helps somewhat. Some of the zero GA stores show the
Period and Store ID correctly, some don't. Does anyone know what's going
on, and how I can fix it? I need to return all Store ID's and all GA
quantities.

I have 2 tables (tblStoreInfo and tblDTTD_GA) which are linked one-to many
on STORE4_ID.

Here's the Query and the output it produces. Note that I have temporarily
prepended the Store ID to the Store Name, so that the blank Store ID's will be identified. I am also changing the store names to protect the innocent. I am only showing those stores with a 0 (Zero) in the GA column. My
apologies for the way the output looks. I hope you will get the idea.

The Query-----------------------------------------------
SELECT
NZ(tblDTTD_GA.P ERIOD,"0") AS Period,
NZ(tblDTTD_GA.S TORE4_ID,"0") AS [Store4 ID],
NZ(tblStoreInfo .STORE_NAME,"un known") AS [Store Name],
NZ(tblDTTD_GA.D TTD_ALLOC_GA,0) AS [Gross Addition]
FROM
tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.ST ORE4_ID=tblDTTD _GA.STORE4_ID
WHERE
NZ(tblDTTD_GA.P ERIOD,"200412") ="200412"
ORDER BY
tblDTTD_GA.PERI OD, tblDTTD_GA.STOR E4_ID;
--------------------------------------------------------

The Output----------------------------------------------
Period Store4 ID Store Name Gross Addition
0 0 3106__Foo 0
0 0 1601__Gat 0
0 0 110P__Pre 0
0 0 1102__Gal 0
0 0 1203__Oak 0
0 0 CRIC__CRI 0
0 0 1000__Rev 0
0 0 0502__UNK 0
0 0 6151__Oak 0
0 0 3104__Kno 0
0 0 2303__The 0
0 0 2801__Car 0
0 0 311P__Pre 0
200412 1101 1101__UNK 0
200412 2007 2007__Cla 0
200412 3301 3301__S C 0
200412 4102 4102__Val 0
200412 5105 5105__Cra 0
200412 5109 5109__Cen 0
200412 5206 5206__Bou 0
200412 6504 6504__Wil 0
--------------------------------------------------------

Help!

Alan

[Alan Lane]

Hello world:

Thanks to Salad and McDermott for your responses. I now have my GA query
working correctly, though other problems remain. Let me explain the final
goal I'm shooting for, which will clarify (I hope) why I made the choices I
did.

I actually need to produce a report of 13 data elements (of which GA is only
the first), by store, over the whole calendar year of 2004. Stores have
been opened, closed and moved (which changes their store id) over the year.
There may be months when a particular store has no data in a particular
element because it wasn't open yet, but I still need to show the store
number, with zeroes for data. I tried to do the whole thing at once and
failed, so I thought I would break it down into "bite-size" chunks -- a
single data element and month at a time.

Because of the complexity of the overall report, I decided against using
Salad's "break it into 2 or 3 queries" idea. I'm already going to have to
run 156 queries to get my full dataset returned. If GA for one month was my
final goal, I would seriously consider it though.

MacDermott's point about using the tblStoreInfo instead of tblDTTD_GA to get
the store number was excellent. Using that idea, I was able to return all
stores, whether or not they had GA. Here's what my successful complete GA
query looks like:

'--qry01GA
SELECT
NZ(tblDTTD_GA.P ERIOD,"200412") AS Period,
NZ(tblStoreInfo .STORE4_ID,"0") AS [Store4 ID],
NZ(tblStoreInfo .STORE_NAME,"un known") AS [Store Name],
NZ(tblStoreInfo .STORE_TYPE,"un known") AS [Store Type],
NZ(tblMarket.MA RKET_NAME,"unkn own") AS [Market Name],
NZ(tblMarket.RE GION_OLD,"unkno wn") AS Region,
NZ(tblDTTD_GA.D TTD_ALLOC_GA,0) AS [Gross Addition]
FROM
tblMarket
INNER JOIN (tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.ST ORE4_ID=tblDTTD _GA.STORE4_ID)
ON tblMarket.AIRPO RT_CODE=tblStor eInfo.AIRPORT_C ODE
WHERE
NZ(tblDTTD_GA.P ERIOD,"200412") ="200412"
ORDER BY
tblStoreInfo.ST ORE4_ID;

I thought I could just use the above query as a template for the other data
elements. No such luck! For instance, the below query refuses to bring
back the full count of stores. Can anyone explain why? Does it have
something to do with the AND in the WHERE clause?

'--qry05ServicePay ment
SELECT
NZ(tblServiceTr ansaction.PERIO D,"200412") AS Period,
NZ(tblStoreInfo .STORE4_ID,"0") AS [Store4 ID],
NZ(tblServiceTr ansaction.QUANT ITY,0) AS [Service Payment]
FROM
tblStoreInfo
LEFT JOIN tblServiceTrans action
ON tblStoreInfo.ST ORE4_ID = tblServiceTrans action.STORE4_I D
WHERE
(((NZ(tblServic eTransaction.PE RIOD,"200412")) ="200412")
AND ((tblServiceTra nsaction.SERVIC E_TYPE_ID)=7))
ORDER BY
tblStoreInfo.ST ORE4_ID;

Thanks in advance for any help provided.

Alan

[MacDermott]

Notice the difference between this:
WHERE NZ(tblDTTD_GA.P ERIOD,"200412") ="200412"
and this:
WHERE ((tblServiceTra nsaction.SERVIC E_TYPE_ID)=7))

The second query returns only records where SERVICE_TYPE_ID =7; it will not
return a record for any store in tblStoreInfo which has no matching record
in tblServiceTrans action.
The first query returns all records in tblStoreInfo, including those for
which there is no record in tblDTTD_GA for that period.

One approach would be this:
WHERE (NZ(tblServiceT ransaction.SERV ICE_TYPE_ID,7)= 7))

Do you think you understand this pattern enough to be able to apply it to
further query building?
Or would you like more explanation of what is happening here?

But what really concerns me is your statement that you'll need to run 156
queries.
Perhaps you could say more about what you feel makes this necessary?

HTH
- Turtle

"Alan Lane" <aw*****@yahoo. com> wrote in message
news:%6******** **********@twis ter.socal.rr.co m...

Hello world:

Thanks to Salad and McDermott for your responses. I now have my GA query
working correctly, though other problems remain. Let me explain the final
goal I'm shooting for, which will clarify (I hope) why I made the choices I did.

I actually need to produce a report of 13 data elements (of which GA is only the first), by store, over the whole calendar year of 2004. Stores have
been opened, closed and moved (which changes their store id) over the year. There may be months when a particular store has no data in a particular
element because it wasn't open yet, but I still need to show the store
number, with zeroes for data. I tried to do the whole thing at once and
failed, so I thought I would break it down into "bite-size" chunks -- a
single data element and month at a time.

Because of the complexity of the overall report, I decided against using
Salad's "break it into 2 or 3 queries" idea. I'm already going to have to
run 156 queries to get my full dataset returned. If GA for one month was my final goal, I would seriously consider it though.

MacDermott's point about using the tblStoreInfo instead of tblDTTD_GA to get the store number was excellent. Using that idea, I was able to return all
stores, whether or not they had GA. Here's what my successful complete GA
query looks like:

'--qry01GA
SELECT
NZ(tblDTTD_GA.P ERIOD,"200412") AS Period,
NZ(tblStoreInfo .STORE4_ID,"0") AS [Store4 ID],
NZ(tblStoreInfo .STORE_NAME,"un known") AS [Store Name],
NZ(tblStoreInfo .STORE_TYPE,"un known") AS [Store Type],
NZ(tblMarket.MA RKET_NAME,"unkn own") AS [Market Name],
NZ(tblMarket.RE GION_OLD,"unkno wn") AS Region,
NZ(tblDTTD_GA.D TTD_ALLOC_GA,0) AS [Gross Addition]
FROM
tblMarket
INNER JOIN (tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.ST ORE4_ID=tblDTTD _GA.STORE4_ID)
ON tblMarket.AIRPO RT_CODE=tblStor eInfo.AIRPORT_C ODE
WHERE
NZ(tblDTTD_GA.P ERIOD,"200412") ="200412"
ORDER BY
tblStoreInfo.ST ORE4_ID;

I thought I could just use the above query as a template for the other data elements. No such luck! For instance, the below query refuses to bring
back the full count of stores. Can anyone explain why? Does it have
something to do with the AND in the WHERE clause?

'--qry05ServicePay ment
SELECT
NZ(tblServiceTr ansaction.PERIO D,"200412") AS Period,
NZ(tblStoreInfo .STORE4_ID,"0") AS [Store4 ID],
NZ(tblServiceTr ansaction.QUANT ITY,0) AS [Service Payment]
FROM
tblStoreInfo
LEFT JOIN tblServiceTrans action
ON tblStoreInfo.ST ORE4_ID = tblServiceTrans action.STORE4_I D
WHERE
(((NZ(tblServic eTransaction.PE RIOD,"200412")) ="200412")
AND ((tblServiceTra nsaction.SERVIC E_TYPE_ID)=7))
ORDER BY
tblStoreInfo.ST ORE4_ID;

Thanks in advance for any help provided.

Alan

[Salad]

Alan Lane wrote:

Hello world:

Thanks to Salad and McDermott for your responses. I now have my GA query
working correctly, though other problems remain. Let me explain the final
goal I'm shooting for, which will clarify (I hope) why I made the choices I
did.

I actually need to produce a report of 13 data elements (of which GA is only
the first), by store, over the whole calendar year of 2004. Stores have
been opened, closed and moved (which changes their store id) over the year.
There may be months when a particular store has no data in a particular
element because it wasn't open yet, but I still need to show the store
number, with zeroes for data. I tried to do the whole thing at once and
failed, so I thought I would break it down into "bite-size" chunks -- a
single data element and month at a time.

Because of the complexity of the overall report, I decided against using
Salad's "break it into 2 or 3 queries" idea. I'm already going to have to
run 156 queries to get my full dataset returned. If GA for one month was my
final goal, I would seriously consider it though.
Like McDermott stated, 156 queries for 1 report would be a bit excessive.

The reason I felt it would be nice to break it into 2 or 3 queries is to
create queries that perform a specific task and makes it easier to
understand what is going on.

In your case, it might be best to return a recordset that contains all
of the record information you need. That would mean that you have all
of the columns you would filter your records on.

In a form or report it is very easy to filter the recordset to return
only the records that meet your criteria. You can filter the recordset
when you open the form or report
Docmd.OpenRepor t "Test",,"Id = 20"
or when the report opens do a
Me.Filter = "Id = 20"
Me.FilterOn = True
MacDermott's point about using the tblStoreInfo instead of tblDTTD_GA to get
the store number was excellent. Using that idea, I was able to return all
stores, whether or not they had GA. Here's what my successful complete GA
query looks like:

'--qry01GA
SELECT
NZ(tblDTTD_GA.P ERIOD,"200412") AS Period,
NZ(tblStoreInfo .STORE4_ID,"0") AS [Store4 ID],
NZ(tblStoreInfo .STORE_NAME,"un known") AS [Store Name],
NZ(tblStoreInfo .STORE_TYPE,"un known") AS [Store Type],
NZ(tblMarket.MA RKET_NAME,"unkn own") AS [Market Name],
NZ(tblMarket.RE GION_OLD,"unkno wn") AS Region,
NZ(tblDTTD_GA.D TTD_ALLOC_GA,0) AS [Gross Addition]
FROM
tblMarket
INNER JOIN (tblStoreInfo
LEFT JOIN tblDTTD_GA
ON tblStoreInfo.ST ORE4_ID=tblDTTD _GA.STORE4_ID)
ON tblMarket.AIRPO RT_CODE=tblStor eInfo.AIRPORT_C ODE
WHERE
NZ(tblDTTD_GA.P ERIOD,"200412") ="200412"
ORDER BY
tblStoreInfo.ST ORE4_ID;

I thought I could just use the above query as a template for the other data
elements. No such luck! For instance, the below query refuses to bring
back the full count of stores. Can anyone explain why? Does it have
something to do with the AND in the WHERE clause?

'--qry05ServicePay ment
SELECT
NZ(tblServiceTr ansaction.PERIO D,"200412") AS Period,
NZ(tblStoreInfo .STORE4_ID,"0") AS [Store4 ID],
NZ(tblServiceTr ansaction.QUANT ITY,0) AS [Service Payment]
FROM
tblStoreInfo
LEFT JOIN tblServiceTrans action
ON tblStoreInfo.ST ORE4_ID = tblServiceTrans action.STORE4_I D
WHERE
(((NZ(tblServic eTransaction.PE RIOD,"200412")) ="200412")
AND ((tblServiceTra nsaction.SERVIC E_TYPE_ID)=7))
ORDER BY
tblStoreInfo.ST ORE4_ID;

Thanks in advance for any help provided.
It's possible you want "200412" records OR servicetype = 7 instead of
AND. It is hard to say.

I probaby would have a form where you select the criteria; the period to
report, service types, regions, stores, etc. Then build a filter when
the command button to present the report is pressed.

I guess the best advice I can provide is to study your requirements,
break things down to simple components to keep things simple, and use
filters correctly to return the desired results.

Alan

[Alan Lane]

Hi to Salad and MacDermott:

First, let me say how much I appreciate the time both of you are spending
helping me work out my Access problem. CDMA is a great newsgroup because of
the great help that folks like you provide.

The question of 156 queries: That's 13 individual queries (one for each
data element), for a month, times 12 months. I agree it is outrageously
cumbersome. The reason I feel I'm stuck with it is that I can't depend on
getting back the correct number of stores each month. I would obviously
prefer one monster query that would accomplish everything.

Over 2004, there were a total of 127 stores. Some of them only came on-line
late in the year, so they only have a month or two of data. Some of them
were closed during the year, so they only have data from their active
period. Some of them were moved, so the data ends under their old Store ID,
and starts up under their new Store ID. Some of them have no activity in a
particular data category during one or more months.

What I need to output is 127 rows per month, with each with the 13 data
elements as columns displayed horizontally. If a Store ID has no activity
for all or any data element that is fine, but I need to show the store and
the absence of activity. Obviously, I'm having difficulty getting what I
need. I am much closer to an answer thanks to your help than I was when I
first started, though.

I have a lookup table for stores (tblStoreInfo). It contains 127 records.
I have data tables for the different data categories. They have a common
structure, with a Primary Key made up of PERIOD and STORE4_ID. The one
oddball is tblServiceTrans actions. There are 9 possible types of Service
Transactions that can happen in a store over a month, so
tblServiceTrans actions has a Primary Key of PERIOD, STORE4_ID and
SERVICE_TYPE_ID . 9 of my 13 data elements are these Service Transactions.
All the data tables are related one-to-many to tblStoreInfo on its Primary
Key, STORE4_ID.

I hope this explains my situation a little better.

Any ideas on the best way forward would be greatly appreciated.

Alan

[Salad]

Alan Lane wrote:

Hi to Salad and MacDermott:

First, let me say how much I appreciate the time both of you are spending
helping me work out my Access problem. CDMA is a great newsgroup because of
the great help that folks like you provide.

The question of 156 queries: That's 13 individual queries (one for each
data element), for a month, times 12 months. I agree it is outrageously
cumbersome. The reason I feel I'm stuck with it is that I can't depend on
getting back the correct number of stores each month. I would obviously
prefer one monster query that would accomplish everything.

Over 2004, there were a total of 127 stores. Some of them only came on-line
late in the year, so they only have a month or two of data. Some of them
were closed during the year, so they only have data from their active
period. Some of them were moved, so the data ends under their old Store ID,
and starts up under their new Store ID. Some of them have no activity in a
particular data category during one or more months.

What I need to output is 127 rows per month, with each with the 13 data
elements as columns displayed horizontally. If a Store ID has no activity
for all or any data element that is fine, but I need to show the store and
the absence of activity. Obviously, I'm having difficulty getting what I
need. I am much closer to an answer thanks to your help than I was when I
first started, though.

I have a lookup table for stores (tblStoreInfo). It contains 127 records.
I have data tables for the different data categories. They have a common
structure, with a Primary Key made up of PERIOD and STORE4_ID. The one
oddball is tblServiceTrans actions. There are 9 possible types of Service
Transactions that can happen in a store over a month, so
tblServiceTrans actions has a Primary Key of PERIOD, STORE4_ID and
SERVICE_TYPE_ID . 9 of my 13 data elements are these Service Transactions.
All the data tables are related one-to-many to tblStoreInfo on its Primary
Key, STORE4_ID.

I hope this explains my situation a little better.

Any ideas on the best way forward would be greatly appreciated.

Alan

There's got to be a better way.

tblStoreInfo would be the main table I would use since it has all of the
store names.

I would assume that all of your other tables have the Store4_ID field
that would provide a link to tblStoreInfo.

I usually do something different. In a database I work on, I have a
projects table. The first field is a ProjectKey and it is an
autonumber, the second field is the ProjectID, and the third is the
ProjectName. Any tables that link to this table has a ProjectKey field
that is a LongInt.

The reason I do this, using an autonumber as the primary key, is that if
someone dataenters the ProjectID incorrectly, and I have associated
records, I don't need to worry about updating all of the other tables
with that associated id.

So...when I create a table...almost always I will use an autonumber as a
primary key. It simply makes life simple to have a key that is an
autonumber.

I would not be needing all of those NZ()s that you have. Frankly, I
don't know why everything you select must be NZ'd since you can do the
formatting in your report.

I don't know what your 13 data elements are.

So...I would make tblStoreInfo your main table. I would then add
tblMarket. If you are sure every record in tblStoreInfo has a valid
AirportCode, I would simply draw the relationship line between the two
tables on the AirportCode field...otherwi se make it All records in
tblStoreInfo and those that match in tblMarket.

I would then add the tblDTTD_GA table. I would then draw a relationship
line on the StoreID between tblStoreInfo and tblDTTD_GA. I would then
dbl-click on the line and set it to All tblStoreInfo and those that
match in tblDTTD_GA.

Now, I do not know which field you want to access from tblTransactions
for each transaction type. What I would do is do is click on the
ShowTable and select tblTransactions and add it 9 times. You will end
up with tblTransactions ...tblTransacti ons8. I would then add a link
between storeID on each of those tables and tblStoreInfo. Then I would
dbl-click on each relationship line and make it All tblStoreInfo records
and those that match in tblTransactions ...tblTransacti ons.

I don't know if your are getting amount fields or counts or sums or
whatever from tblTransactions . But, drop the transaction field down
from those 9 tables. Now add the period from those 9 tables. Now add
the transaction type from those 9 tables. In the period set them all to
"200412" although you could enter [Enter Period] in each one to create a
parameter that you can enter. Then enter the code to filter on in each
of the 9 tables...tblTra nsactions would be code 1, tblTransactions would
be code 2...tblTransact ions8 would be code 9.

You would also want to filter on the period for tblDTTD_GA with the
[Enter Period] too.

Now you should get 127 rows with all of the data filled in.

If your tblTransactions are many records, you may want to create 9
queries...each that filters for the transaction code. Then make the
query a TOTALs query so you get 1 line for each transaction code. This
way you avoid having multiple lines for each store if there are more
than 1 transaction for each transaction code.

The way I've described this you've cut 150+ queries down to 1. If you
need to create a Totals query for the 9 transaction codes then you are
down to 10 queries. That is much more maintainable.

I expect that if you have questions on this, please read what I've said
thoroughly first. If you understand it, you will realize you have made
a mountain out of a molehill and added work to this report.

Try to keep things simple. Break things down into components and tasks.
This will make your life much easier.

If worst comes to worst and you need some help, it might be best if you
had a program like GoToMyPC http://www.gotomypc.com and let someone come
into your system and show you how to do it.

[MacDermott]

It would seem that you'll need a line in your report for each store for each
month.
You have a "master list" of stores, but not AFAICS one of months.

I'd suggest creating a simple table with the months 01 to 12 (as text):
tblMonths
MonthNo (Text) - PK

Now, you can write this query:
Select tblStoreInfo.* , "2004" & MonthNo AS PERIOD FROM tblStoreInfo,
tblMonths
Note that no join is specified, so you will get a record for each store and
each month.

Save this query as qryStoreMonth, and use it as the master side of all your
joins.
(Before the LEFT in your joins).
That way you'll always have a record for each store and each month, whether
or not there's any activity.

If you already know about parameter queries, you'll see how you can replace
"2004" with a parameter and have this query work for any year; if you don't
know about them, let's leave that for a bit later.

Using qryStoreMonth, you should be able to link tblTDDT_GA on STORE4_ID and
PERIOD.
You'll no longer need a WHERE clause, and you'll no longer need NZ, unless
you want to show a 0 instead of a blank.

To include the data in tblServiceTrans actions, you can use a crosstab query.
From what you've said, I'd be surprised if you're familiar with these, so
I'd suggest you go and learn about them - the Access Help file is a fine
place to start, but you may have other resources you prefer.

I've given you a few of the pieces here -
post back with any problems you run into putting them together.

HTH
- Turtle
"Alan Lane" <aw*****@yahoo. com> wrote in message
news:hQ******** **********@twis ter.socal.rr.co m...

Hi to Salad and MacDermott:

First, let me say how much I appreciate the time both of you are spending
helping me work out my Access problem. CDMA is a great newsgroup because of the great help that folks like you provide.

The question of 156 queries: That's 13 individual queries (one for each
data element), for a month, times 12 months. I agree it is outrageously
cumbersome. The reason I feel I'm stuck with it is that I can't depend on
getting back the correct number of stores each month. I would obviously
prefer one monster query that would accomplish everything.

Over 2004, there were a total of 127 stores. Some of them only came on-line late in the year, so they only have a month or two of data. Some of them
were closed during the year, so they only have data from their active
period. Some of them were moved, so the data ends under their old Store ID, and starts up under their new Store ID. Some of them have no activity in a particular data category during one or more months.

What I need to output is 127 rows per month, with each with the 13 data
elements as columns displayed horizontally. If a Store ID has no activity
for all or any data element that is fine, but I need to show the store and
the absence of activity. Obviously, I'm having difficulty getting what I
need. I am much closer to an answer thanks to your help than I was when I
first started, though.

I have a lookup table for stores (tblStoreInfo). It contains 127 records.
I have data tables for the different data categories. They have a common
structure, with a Primary Key made up of PERIOD and STORE4_ID. The one
oddball is tblServiceTrans actions. There are 9 possible types of Service
Transactions that can happen in a store over a month, so
tblServiceTrans actions has a Primary Key of PERIOD, STORE4_ID and
SERVICE_TYPE_ID . 9 of my 13 data elements are these Service Transactions.
All the data tables are related one-to-many to tblStoreInfo on its Primary
Key, STORE4_ID.

I hope this explains my situation a little better.

Any ideas on the best way forward would be greatly appreciated.

Alan

[Alan Lane]

Hello world:

Yay! Success!! Phew!!!

I started by taking to heart Salad's point that "There's got to be a better
way." I dumped all my contorted, convoluted query structure. I then
started looking for some simple approach to my problem.

About that time, MacDermott's email arrived. Ah ha! His qryStoreMonth idea
was just what I needed. I built that, and the rest of the query just built
itself. My key weakness was the inability to see how to put together a
reliable output of all store id's across the months. Once that was fixed,
the rest was easy.

Just for the heck of it, I built a form with a multi-select list box for
Period selection. Now users can look at all of 2004, any quarter, or any
month. They like it.

I don't know what I would have done without the assistance of Turtle and
Salad. Sometimes you just need other eyes to see quickly what you are blind
to, because you're so deep into it. Thanks for restoring my sight, guys!

Alan