Q: Can't get crosstab query to work right

M

MarkD

I have a crosstab query with row headings
RegionA
RegionB
RegionC
RegionD

Column headings are 200401, 200402, 200403, ..., 200411
Region B has no sales, so I have to create a table with
the 4 regions and left join to the data table (to ensure
the row headings always show those 4 regions). There is no
null column.

Unfortunately, now I'm getting the "<>" column heading.
I tried setting the criteria for the colums field to Not
Null, but that drops "RegionB" for some reason.

The only thing that seems to work hard-coding the column
headings. However, I don't see anywhere in the querydef
properties where I can add this programmatically (I have
40 crosstab queries that need to be updated monthly).

I'm stuck. What can I do?
 
D

Duane Hookom

Drop the RegionB in the crosstab and then create a new query based on your
crosstab that right or left joins on the table of regions. Use the regions
from this table in your new query grid.
 
M

MarkD

Hi Duane, thanks for the reply

Since the column headings will change monthly (it's a
rolling 13 month crosstab), how do I change the column
headings? I can't use the [TableA].* since that has the
missing region.
 
D

Duane Hookom

Use relative column headings. You can search google groups on my name and
"Mth0". If you don't find the solution, come on back.

I thought you wanted add back the missing region to your completed crosstab?

--
Duane Hookom
MS Access MVP
--

MarkD said:
Hi Duane, thanks for the reply

Since the column headings will change monthly (it's a
rolling 13 month crosstab), how do I change the column
headings? I can't use the [TableA].* since that has the
missing region.
-----Original Message-----
Drop the RegionB in the crosstab and then create a new query based on your
crosstab that right or left joins on the table of regions. Use the regions
from this table in your new query grid.

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
M

MarkD

Hi Duane,

I'll google that information, thanks. And yes, I do want
to add the missing region, I just didn't know to get that.
I guess what I have to do is get REGION from the region
table, and use the relative column headings method to get
the YYYYMM columns.

Thanks,
-Mark
-----Original Message-----
Use relative column headings. You can search google groups on my name and
"Mth0". If you don't find the solution, come on back.

I thought you wanted add back the missing region to your completed crosstab?

--
Duane Hookom
MS Access MVP
--

Hi Duane, thanks for the reply

Since the column headings will change monthly (it's a
rolling 13 month crosstab), how do I change the column
headings? I can't use the [TableA].* since that has the
missing region.
Click to expand...
Click to expand...
 
D

Duane Hookom

That's the method I would use.

--
Duane Hookom
MS Access MVP
--

MarkD said:
Hi Duane,

I'll google that information, thanks. And yes, I do want
to add the missing region, I just didn't know to get that.
I guess what I have to do is get REGION from the region
table, and use the relative column headings method to get
the YYYYMM columns.

Thanks,
-Mark
-----Original Message-----
Use relative column headings. You can search google groups on my name and
"Mth0". If you don't find the solution, come on back.

I thought you wanted add back the missing region to your completed crosstab?

--
Duane Hookom
MS Access MVP
--

Hi Duane, thanks for the reply

Since the column headings will change monthly (it's a
rolling 13 month crosstab), how do I change the column
headings? I can't use the [TableA].* since that has the
missing region.
Click to expand...
Click to expand...
Click to expand...
 
Top