Q: Can't get crosstab query to work right

  • Thread starter Thread starter MarkD
  • Start date Start date
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?
 
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.
 
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.
 
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...
 
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...
 
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...
 
Back
Top