Crosstab query column alignment

[skerns]

Hello to all

I am using Access 2000 and have created a crosstab query that uses
date/time field for the column header and a unit field for the value
The date field is from one table and the units are in another table
The query returns the correct data. The date field is formatted b
month and year. When I run the query (and the report based on thi
query) they both do the same thing. The data is grouped by year whic
I want, but is stagered. For example

Vehicle|Month | 2002 | 2003 | 2004 | 200
Ford |Aug 02|4
|Sep 02|2
|Oct 02 |3
|Nov 02|2
|Dec 02|5
|Jan 03| |4
|Feb 03| |6
|Mar 03| |3
|Apr 03| |5

How can I get the unit information to return to the top of the colum
for each year
Thank you in advance :

Shirle

 

[Duane Hookom]

Share your SQL view...

 

[John Spencer]

As a guess, DROP the year from the Month Column. You already have the year
in the columns displaying the year. The month column should have 'Aug' not
'Aug 02' or 'Aug 03'

Since I don't know how your data is stored for Month, I can't say how to
eliminate the year.

 

[skerns]

Thank you John

I changed the format of the field [Month] in both the table and th
query to mmm. It correctly displays the month as Aug, Sep, etc. bu
still returns stagered results in the query. I'm not sure what els
to try. My knowledge is limited. Thank you for your time
Shirle

 

[skerns]

Sorry to have wasted your time Duane, I should have done this in th
first place. Thank you so much for your help
Shirle


TRANSFORM First([Cross Sell Units Data].[MTD Sales]) AS [FirstOfMT
Sales
SELECT [Cross Sell Make Data].Make, [Cross Sell Units Data].Mont
FROM [Cross Sell Make Data] INNER JOIN [Cross Sell Units Data] O
[Cross Sell Make Data].Year = [Cross Sell Units Data].Yea
WHERE ((([Cross Sell Units Data].[Sales Type]) In ("New"))
GROUP BY [Cross Sell Make Data].Make, [Cross Sell Units Data].Mont
ORDER BY [Cross Sell Make Data].Make, [Cross Sell Units Data].Mont
PIVOT [Cross Sell Make Data].Yea
WITH OWNERACCESS OPTION

 

[Duane Hookom]

What does your month column look like in the data?

 

[skerns]

Hello Duane
Thank you for responding

Also Thank you John, I changed the Month field in both tables to tex
and entered all dates as simply Aug, Sep and so on. I had a dat
mismatch error with the change when I ran the query. Thanks for you
suggestion though

The table called "Cross Sell Units Data" contains
DealerID|DealerName|Month|Year|SalesType|MTDSale

The DealerID is (numeric), Dealer Name (text), Month (date/time), Yea
(numeric), Sales (text) column says either "New" or "Used" and Sale
is units sold (numeric)

When I open the table in datasheet the data appears on one line fo
each entry, for example

00034|Jones Ford|8/1/2003|New|4
00051|Smith Pontiac|8/1/2003|Used|2
etc

The table called "Cross Sell by Make Data" contains

Make|Month|Year|Unit

Make (text), Month (date/time), Year (numeric), Units (numeric
This table looks like this

Nissan|8/1/2003|2003|4
Mitsubishi|8/1/2003|2003|3
etc

The problem is in the crosstab query where
Make.Cross Sell Make Data is a Row Headin
Year.Cross Sell Make Data is the Column Headin
Month.Cross Sell Units Data is a Row Headin
MTD Sales.Cross Sell Units Data is First Value and
Sales Type.Cross Sell Units Data is WHERE with In ("New") i
criteria

If the data stagers in the query and in the report, can it b
corrected on the report level or do I have to get the query righ
first

Does this give you the information you need
Thank you so much for your help
Shirle