Reduce # of date columns in crosstab query

  • Thread starter Thread starter Gordon
  • Start date Start date
G

Gordon

I have a crosstab query which shows the number of orders for each product
over the past several weeks. The problem is that I want it to show the
results for all products, and not just the products that had orders. Right
now, it only shows the products that had orders. Is there a way to do this?
My SQL code is below.

TRANSFORM CLng(Nz(Sum([Sales By Product temp].Ordered),0)) AS SumOfOrdered
SELECT [Sales By Product temp].ShortSKU2, [Sales By Product temp].Product,
CLng(Nz(Sum([Sales By Product temp].Ordered),0)) AS [Total Of Ordered]
FROM [Sales By Product temp]
WHERE (((DateAdd("d",-Weekday([OrderDate],2)+1,[OrderDate])) Between Date()
And DateAdd("ww",-13,Date())))
GROUP BY [Sales By Product temp].ShortSKU2, [Sales By Product temp].Product
ORDER BY Format(DateAdd("d",-Weekday(OrderDate,2)+1,OrderDate),"mm/dd") DESC
PIVOT Format(DateAdd("d",-Weekday(OrderDate,2)+1,OrderDate),"mm/dd");

Thanks,
Gordon
 
If the problem is missing rows, then it may be a join problem in [Sales By
Product temp] which I'm assuming is a query that joins at least 2 tables.
Open that query in design view and double click on the link between the
tables until a dialog box shows. Select the second options. Save the query
then run the crosstab. If that doesn't give the expected results, try the
third option in Sales By Product temp.

If the problem is columns across then one way to fix this problem is to open
the crosstab query in design view; right click in the area near the tables;
and select Properties. Next go into the Column Headings and put in something
like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.

Of course this only works if you always know what your column heading need
to be.
 
Hi Jerry,

Thanks for the reply. I tried changing the join definitions as you
suggested, and that didn't do the trick. Also, I can't use fixed column
headings because I just want the most recent 10 weeks of data. So those
headings are going to change from week to week.

I'm concerned that the problem might be involved with the way crosstabs
works. Basically, my WHERE statement automatically throws out all of the
products that did not have sales over the past 10 weeks. But I want to see
which products did not have sales, too. I could remove the WHERE statement,
but then I get 4 years worth of weekly data, which is not what I want,
either.

So it seems like I need to remove the WHERE statement, and find some way to
limit the columns output, or else have some other way to get the query to
include all the data.

Any thoughts?

Bontrager makes great stuff.

Thanks again,
Gordon


Jerry Whittle said:
If the problem is missing rows, then it may be a join problem in [Sales By
Product temp] which I'm assuming is a query that joins at least 2 tables.
Open that query in design view and double click on the link between the
tables until a dialog box shows. Select the second options. Save the query
then run the crosstab. If that doesn't give the expected results, try the
third option in Sales By Product temp.

If the problem is columns across then one way to fix this problem is to open
the crosstab query in design view; right click in the area near the tables;
and select Properties. Next go into the Column Headings and put in something
like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.

Of course this only works if you always know what your column heading need
to be.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Gordon said:
I have a crosstab query which shows the number of orders for each product
over the past several weeks. The problem is that I want it to show the
results for all products, and not just the products that had orders. Right
now, it only shows the products that had orders. Is there a way to do this?
My SQL code is below.

TRANSFORM CLng(Nz(Sum([Sales By Product temp].Ordered),0)) AS SumOfOrdered
SELECT [Sales By Product temp].ShortSKU2, [Sales By Product temp].Product,
CLng(Nz(Sum([Sales By Product temp].Ordered),0)) AS [Total Of Ordered]
FROM [Sales By Product temp]
WHERE (((DateAdd("d",-Weekday([OrderDate],2)+1,[OrderDate])) Between Date()
And DateAdd("ww",-13,Date())))
GROUP BY [Sales By Product temp].ShortSKU2, [Sales By Product temp].Product
ORDER BY Format(DateAdd("d",-Weekday(OrderDate,2)+1,OrderDate),"mm/dd") DESC
PIVOT Format(DateAdd("d",-Weekday(OrderDate,2)+1,OrderDate),"mm/dd");

Thanks,
Gordon
Click to expand...
Click to expand...
 
The only thing that I can think of is trying the Left Join in the original
query again.
Then put in an Or Is Null in the criteria. Actually you might want to move
the criteria out of the crosstab and into something like the original query.
My advice on crosstabs is to create a query that limits the records and
fields, then create a crosstab based on it. That way you more easily see what
records are being returned first, then see if there are any problems just
with the crosstab.

You could use the column headings to limit the number of columns; however,
you'd probably have to manually change it each week.

I've heard lots of complaints about some of their wheels lately. Plan on
replacing my old Trek 5200 with a Madone next year so I might just find out!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Gordon said:
Hi Jerry,

Thanks for the reply. I tried changing the join definitions as you
suggested, and that didn't do the trick. Also, I can't use fixed column
headings because I just want the most recent 10 weeks of data. So those
headings are going to change from week to week.

I'm concerned that the problem might be involved with the way crosstabs
works. Basically, my WHERE statement automatically throws out all of the
products that did not have sales over the past 10 weeks. But I want to see
which products did not have sales, too. I could remove the WHERE statement,
but then I get 4 years worth of weekly data, which is not what I want,
either.

So it seems like I need to remove the WHERE statement, and find some way to
limit the columns output, or else have some other way to get the query to
include all the data.

Any thoughts?

Bontrager makes great stuff.

Thanks again,
Gordon


Jerry Whittle said:
If the problem is missing rows, then it may be a join problem in [Sales By
Product temp] which I'm assuming is a query that joins at least 2 tables.
Open that query in design view and double click on the link between the
tables until a dialog box shows. Select the second options. Save the query
then run the crosstab. If that doesn't give the expected results, try the
third option in Sales By Product temp.

If the problem is columns across then one way to fix this problem is to open
the crosstab query in design view; right click in the area near the tables;
and select Properties. Next go into the Column Headings and put in something
like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.

Of course this only works if you always know what your column heading need
to be.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Gordon said:
I have a crosstab query which shows the number of orders for each product
over the past several weeks. The problem is that I want it to show the
results for all products, and not just the products that had orders. Right
now, it only shows the products that had orders. Is there a way to do this?
My SQL code is below.

TRANSFORM CLng(Nz(Sum([Sales By Product temp].Ordered),0)) AS SumOfOrdered
SELECT [Sales By Product temp].ShortSKU2, [Sales By Product temp].Product,
CLng(Nz(Sum([Sales By Product temp].Ordered),0)) AS [Total Of Ordered]
FROM [Sales By Product temp]
WHERE (((DateAdd("d",-Weekday([OrderDate],2)+1,[OrderDate])) Between Date()
And DateAdd("ww",-13,Date())))
GROUP BY [Sales By Product temp].ShortSKU2, [Sales By Product temp].Product
ORDER BY Format(DateAdd("d",-Weekday(OrderDate,2)+1,OrderDate),"mm/dd") DESC
PIVOT Format(DateAdd("d",-Weekday(OrderDate,2)+1,OrderDate),"mm/dd");

Thanks,
Gordon
Click to expand...
Click to expand...
Click to expand...
 
Hi Jerry,

Thanks for the reply. I'm thinking that if there is no sale of a specific
product within the time frame of interest, then there will be no record in
"Sales by Product temp" for that product, so it won't show up no matter how I
do the query.

Do you know of a way to generate a line of data (or a record) for each item
or product? I could set OrderDate to Date(), and the "Ordered" variable to 0
or 1. At least then I'd have a record to work with. Then I could subtract
it from the final calculation in the crosstab query. Do you think something
like that would work?

Getting a Madone? That should be a nice ride. I've been eyeing the Looks
recently, but I'm wondering if they're a bit of a flash in the pan. (Kind of
like "this year's Cervelo.")

Thanks,
Gordon


Jerry Whittle said:
The only thing that I can think of is trying the Left Join in the original
query again.
Then put in an Or Is Null in the criteria. Actually you might want to move
the criteria out of the crosstab and into something like the original query.
My advice on crosstabs is to create a query that limits the records and
fields, then create a crosstab based on it. That way you more easily see what
records are being returned first, then see if there are any problems just
with the crosstab.

You could use the column headings to limit the number of columns; however,
you'd probably have to manually change it each week.

I've heard lots of complaints about some of their wheels lately. Plan on
replacing my old Trek 5200 with a Madone next year so I might just find out!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Gordon said:
Hi Jerry,

Thanks for the reply. I tried changing the join definitions as you
suggested, and that didn't do the trick. Also, I can't use fixed column
headings because I just want the most recent 10 weeks of data. So those
headings are going to change from week to week.

I'm concerned that the problem might be involved with the way crosstabs
works. Basically, my WHERE statement automatically throws out all of the
products that did not have sales over the past 10 weeks. But I want to see
which products did not have sales, too. I could remove the WHERE statement,
but then I get 4 years worth of weekly data, which is not what I want,
either.

So it seems like I need to remove the WHERE statement, and find some way to
limit the columns output, or else have some other way to get the query to
include all the data.

Any thoughts?

Bontrager makes great stuff.

Thanks again,
Gordon


Jerry Whittle said:
If the problem is missing rows, then it may be a join problem in [Sales By
Product temp] which I'm assuming is a query that joins at least 2 tables.
Open that query in design view and double click on the link between the
tables until a dialog box shows. Select the second options. Save the query
then run the crosstab. If that doesn't give the expected results, try the
third option in Sales By Product temp.

If the problem is columns across then one way to fix this problem is to open
the crosstab query in design view; right click in the area near the tables;
and select Properties. Next go into the Column Headings and put in something
like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.

Of course this only works if you always know what your column heading need
to be.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a crosstab query which shows the number of orders for each product
over the past several weeks. The problem is that I want it to show the
results for all products, and not just the products that had orders. Right
now, it only shows the products that had orders. Is there a way to do this?
My SQL code is below.

TRANSFORM CLng(Nz(Sum([Sales By Product temp].Ordered),0)) AS SumOfOrdered
SELECT [Sales By Product temp].ShortSKU2, [Sales By Product temp].Product,
CLng(Nz(Sum([Sales By Product temp].Ordered),0)) AS [Total Of Ordered]
FROM [Sales By Product temp]
WHERE (((DateAdd("d",-Weekday([OrderDate],2)+1,[OrderDate])) Between Date()
And DateAdd("ww",-13,Date())))
GROUP BY [Sales By Product temp].ShortSKU2, [Sales By Product temp].Product
ORDER BY Format(DateAdd("d",-Weekday(OrderDate,2)+1,OrderDate),"mm/dd") DESC
PIVOT Format(DateAdd("d",-Weekday(OrderDate,2)+1,OrderDate),"mm/dd");

Thanks,
Gordon
Click to expand...
Click to expand...
Click to expand...
 
Back
Top