discount weeks in columns of report 1

[robertsquestion]

Hi,

I'm using MS Access 2003. Table 'T_discount' has got following fields:
Customer
Brand
From_week
To_week

So following records are in the table:
A - X - 2 - 6
A - Y - 7 - 8
B - X - 3 - 5
B - Y - 4 - 9
B - Z - 1 - 5

So in the table the discount periods per customer per brand are maintained.
Now I'm looking for a report that displays the Customer and Brands in rows and the weeks in columns. There should be 1 column per week in the report (even if there are no discounts in that week), so it should just display all weeks in the current year. If there is a discount period for a customer, the weeks from this period should be 'marked' (for example with a specific color).
So for the first record (A - X - 2 - 6), I would like to see 5 red cells on the row for customer A and Brand X (weeks 2, 3, 4, 5 and 6).

Defining a color per record according to a new field 'color_code' in tabel T_discount would even be better, but let's start with the simple version..

I hope my explanation is clear and someone out there knows a solution for this question.
Thanks in advance!

Robert van der Berg
The Netherlands

 

[dhookom]

First create a table [tblNums] with a single field [Num] and records with values from 1 to 52.

Then create a crosstab query with a SQL view like:

TRANSFORM First("X") AS Expr1
SELECT T_Discount.Customer, T_Discount.Brand, T_Discount.From_Week, T_Discount.To_Week
FROM T_Discount, tblNums
WHERE (((tblNums.Num) Between [From_Week] And [To_Week]))
GROUP BY T_Discount.Customer, T_Discount.Brand, T_Discount.From_Week, T_Discount.To_Week
ORDER BY T_Discount.Customer, T_Discount.Brand, tblNums.Num
PIVOT tblNums.Num In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16);

You shouldn't stop at 16 but keep going to 52.
[tt]
Customer Brand From_Week To_Week 1 2 3 4 5 6 7 8 9 10
A X 2 6 X X X X X
A Y 7 8 X X
B X 3 5 X X X
B Y 4 9 X X X X X X
B Z 1 5 X X X X X
[/tt]
You can use conditional formatting or code in the report to set the properties of the controls on the report.

Duane
Hook'D on Access
MS Access MVP

 

[robertsquestion]

Hi Duane,

Thanks for your solution, works great!

One additional question: is there a possibility to let the user of the database decide which weeks will be visible (as columns) in the result of this query?
So in other words: is it possible to make the weeks that are in the output of the query 'variable' by letting the user enter a first- and last-week on a form?

So I guess that the last part of the SQL should be changed:
PIVOT tblNums.Num In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16);
should then be something like:
PIVOT tblNums.Num In (tblNums.Num >= Form!first_week and tblNums.Num <=Form!last_week);

Ofcourse this last piece of code won't work, but maybe you know how to solve this?

Thanks,
Robert

 

[dhookom]

I expect you could adapt the faq703-5466. This assumes the number of weeks to display for a single report is constant but the range can jump around.

Duane
Hook'D on Access
MS Access MVP