Crosstab Query Omitting Some Items

  • Thread starter Thread starter Alberto
  • Start date Start date
A

Alberto

I have developed a query by the name of Query1 that pulls only those product
units that have been sold over the last 14 weeks. In this query, I have
[date_purchased], [product_model], and [units_purchased].

I am now trying to create a Crosstab Query off of Query1 that totals
[units_purchased] on a weekly basis for different product_models.

I have gone into the field properties for the Column Heading and typed in
the following:
"Week 1","Week 2","Week 3","Week 4","Week 5","Week 6","Week 7","Week
8","Week 9","Week 10","Week 11","Week 12","Week 13","Week 14"

The field I am pulling looks like this:
"Week " & Format([date_purchased],"w").

When I do this, I only get values for the first 7 columns(weeks). And while
the individual totals for each week are wrong, the total units sold over the
entire 14 weeks appears to be right. What am I doing wrong?
 
I figured out what I was doing wrong. WHen I do it like this, it is okay:

PIVOT DatePart("ww",[date_purchased]) In (1,2,3,4,5,...,53);

However, I would like each week to end on Friday. I would also like not to
have all 53 weeks appear in the Column Headings. How can I do this?
 
Back
Top