Creating Static Column Headings in Crosstab Queries

T

Tricia Young

I have a crosstab query I am using on a form with a subform. For some reason
I can not get the columns to change when the dates change in query. Please
help!

The following is my SQL Statement:

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.blnDelivered
ORDER BY qryOrdReq.txtAssetDescription
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",Format$((DateAdd("d",1-Weekday([dtmDateNeeded],2),[dtmDateNeeded])),'Short Date'));


I need the column headings to be Week 1, Week 2, Week 3, etc. matter what
the actual week numbers are.

Currently my results looks like this:

Item ID Asset 3/26/2007 4/16/2007 4/2/2007
4/23/2007
4603 Mixer 2 1
1 1
2201 Buckets 1 4
8 4
2203 Sponges 4 20
2 5

If someone could tell me how to create the crosstab so that the dates could
be sorted properly that would be great. Also I need the column headings to
on Forms and Reports to change when the dates change.

If that cannot be done then I would settle on using Weeks instead.

Please help!
 
M

Michel Walsh

Use a PIVOT based on a DIFFERENCE of date (of difference of WEEK in facts),
one of the date implied in the difference being a supplied parameter,
something like (for illustration only, it is kept simple):


PIVOT DateDiff( "ww", now, dtmDateNeeded)


so the fields, created, will be 0, 1, 2, 3, 4, ... constants! You can thus
add the IN clause: IN(0, 1, 2, 3, 4)




Now, in a form, or in a report, it is somehow to you to specify WHERE,
physically, will be place field [0], field [1], ... while for its 'header',
you can dynamically change the text from '4' to DateAdd("ww", 4, now), and
so on. (Again, if you want to display the Monday of the week, you have to
adjust the expression accordingly).



Hoping it may help,
Vanderghast, Access MVP
 
T

Tricia Young

Thank you very much for your help. I think I have what I need. However, I
am having some difficulty understanding how this works. Please explain.
First is my SQL Statement correct?

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.intNetAvail, Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.intNetAvail, qryOrdReq.blnDelivered
ORDER BY IIf([dtmdateneeded]<(Date()),"past
due",DateDiff("ww",Now(),[dtmDateNeeded]))
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",DateDiff("ww",Now(),[dtmDateNeeded])) In
(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);

If correct please explain what i've done. Does thes numbers represent just
consecutive numbers or do they represent the number for the week?

The Totals field has the correct Qty. But does "0" rep 3/26/2007, "1" rep
4/2/2007, "2" rep 4/9/2007, etc.?


Michel Walsh said:
Use a PIVOT based on a DIFFERENCE of date (of difference of WEEK in facts),
one of the date implied in the difference being a supplied parameter,
something like (for illustration only, it is kept simple):


PIVOT DateDiff( "ww", now, dtmDateNeeded)


so the fields, created, will be 0, 1, 2, 3, 4, ... constants! You can thus
add the IN clause: IN(0, 1, 2, 3, 4)




Now, in a form, or in a report, it is somehow to you to specify WHERE,
physically, will be place field [0], field [1], ... while for its 'header',
you can dynamically change the text from '4' to DateAdd("ww", 4, now), and
so on. (Again, if you want to display the Monday of the week, you have to
adjust the expression accordingly).



Hoping it may help,
Vanderghast, Access MVP



Tricia Young said:
I have a crosstab query I am using on a form with a subform. For some
reason
I can not get the columns to change when the dates change in query.
Please
help!

The following is my SQL Statement:

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.blnDelivered
ORDER BY qryOrdReq.txtAssetDescription
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",Format$((DateAdd("d",1-Weekday([dtmDateNeeded],2),[dtmDateNeeded])),'Short
Date'));


I need the column headings to be Week 1, Week 2, Week 3, etc. matter what
the actual week numbers are.

Currently my results looks like this:

Item ID Asset 3/26/2007 4/16/2007 4/2/2007
4/23/2007
4603 Mixer 2 1
1 1
2201 Buckets 1 4
8 4
2203 Sponges 4 20
2 5

If someone could tell me how to create the crosstab so that the dates
could
be sorted properly that would be great. Also I need the column headings
to
on Forms and Reports to change when the dates change.

If that cannot be done then I would settle on using Weeks instead.

Please help!
Click to expand...
Click to expand...
 
M

Michel Walsh

I would change the pivot clause a little bit:

PIVOT IIf([dtmdateneeded]<(Date()),-1,DateDiff("ww",Now(),[dtmDateNeeded]))
IN(-1, 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);



The number (>=0) represent the week if actual week is week 0. So, column
11, as example, would have its Monday equals to this Monday + 11*7. So, for
this week, indeed, column 0 will be this week, 2007.03.26 being its Monday.
If you run the same query next week, then column 0 will be that week. Using
Now(), or Date(), that makes the week 0 best described as "the current
week", and the column [1], as one week after the current week, etc.


Note that in general "this Monday" can be given by:

Date() + Choose(DatePart("w", Date()), 1, 0, -1, -2, -3, -4, -5, -6)

assuming Sunday is the first day of the week.


ALSO, the added -1 value, I added in the IN list, and as second argument of
the iif, it represents anything in the past, not just the previous week, and
that, because of the iif construction.



Hoping it may help,
Vanderghast, Access MVP


Tricia Young said:
Thank you very much for your help. I think I have what I need. However,
I
am having some difficulty understanding how this works. Please explain.
First is my SQL Statement correct?

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.intNetAvail, Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.intNetAvail, qryOrdReq.blnDelivered
ORDER BY IIf([dtmdateneeded]<(Date()),"past
due",DateDiff("ww",Now(),[dtmDateNeeded]))
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",DateDiff("ww",Now(),[dtmDateNeeded])) In
(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);

If correct please explain what i've done. Does thes numbers represent
just
consecutive numbers or do they represent the number for the week?

The Totals field has the correct Qty. But does "0" rep 3/26/2007, "1" rep
4/2/2007, "2" rep 4/9/2007, etc.?


Michel Walsh said:
Use a PIVOT based on a DIFFERENCE of date (of difference of WEEK in
facts),
one of the date implied in the difference being a supplied parameter,
something like (for illustration only, it is kept simple):


PIVOT DateDiff( "ww", now, dtmDateNeeded)


so the fields, created, will be 0, 1, 2, 3, 4, ... constants! You can
thus
add the IN clause: IN(0, 1, 2, 3, 4)




Now, in a form, or in a report, it is somehow to you to specify WHERE,
physically, will be place field [0], field [1], ... while for its
'header',
you can dynamically change the text from '4' to DateAdd("ww", 4, now),
and
so on. (Again, if you want to display the Monday of the week, you have to
adjust the expression accordingly).



Hoping it may help,
Vanderghast, Access MVP



Tricia Young said:
I have a crosstab query I am using on a form with a subform. For some
reason
I can not get the columns to change when the dates change in query.
Please
help!

The following is my SQL Statement:

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.blnDelivered
ORDER BY qryOrdReq.txtAssetDescription
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",Format$((DateAdd("d",1-Weekday([dtmDateNeeded],2),[dtmDateNeeded])),'Short
Date'));


I need the column headings to be Week 1, Week 2, Week 3, etc. matter
what
the actual week numbers are.

Currently my results looks like this:

Item ID Asset 3/26/2007 4/16/2007 4/2/2007
4/23/2007
4603 Mixer 2 1
1 1
2201 Buckets 1 4
8 4
2203 Sponges 4 20
2 5

If someone could tell me how to create the crosstab so that the dates
could
be sorted properly that would be great. Also I need the column
headings
to
on Forms and Reports to change when the dates change.

If that cannot be done then I would settle on using Weeks instead.

Please help!
Click to expand...
Click to expand...
Click to expand...
 
T

Tricia Young

Okay, I don't know how you ever figured this out. You are greatness! Thank
you for your help. The code you have provided gives me what I need. One
last thing, you mentioned Monday being the first day of the week. The code
you provided gave me Sunday as the first day. How do I customize the code to
assume Monday is always the first day so that the dates can fall into the
proper column?

Thanks again,

Tricia

Michel Walsh said:
I would change the pivot clause a little bit:

PIVOT IIf([dtmdateneeded]<(Date()),-1,DateDiff("ww",Now(),[dtmDateNeeded]))
IN(-1, 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);



The number (>=0) represent the week if actual week is week 0. So, column
11, as example, would have its Monday equals to this Monday + 11*7. So, for
this week, indeed, column 0 will be this week, 2007.03.26 being its Monday.
If you run the same query next week, then column 0 will be that week. Using
Now(), or Date(), that makes the week 0 best described as "the current
week", and the column [1], as one week after the current week, etc.


Note that in general "this Monday" can be given by:

Date() + Choose(DatePart("w", Date()), 1, 0, -1, -2, -3, -4, -5, -6)

assuming Sunday is the first day of the week.


ALSO, the added -1 value, I added in the IN list, and as second argument of
the iif, it represents anything in the past, not just the previous week, and
that, because of the iif construction.



Hoping it may help,
Vanderghast, Access MVP


Tricia Young said:
Thank you very much for your help. I think I have what I need. However,
I
am having some difficulty understanding how this works. Please explain.
First is my SQL Statement correct?

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.intNetAvail, Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.intNetAvail, qryOrdReq.blnDelivered
ORDER BY IIf([dtmdateneeded]<(Date()),"past
due",DateDiff("ww",Now(),[dtmDateNeeded]))
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",DateDiff("ww",Now(),[dtmDateNeeded])) In
(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);

If correct please explain what i've done. Does thes numbers represent
just
consecutive numbers or do they represent the number for the week?

The Totals field has the correct Qty. But does "0" rep 3/26/2007, "1" rep
4/2/2007, "2" rep 4/9/2007, etc.?


Michel Walsh said:
Use a PIVOT based on a DIFFERENCE of date (of difference of WEEK in
facts),
one of the date implied in the difference being a supplied parameter,
something like (for illustration only, it is kept simple):


PIVOT DateDiff( "ww", now, dtmDateNeeded)


so the fields, created, will be 0, 1, 2, 3, 4, ... constants! You can
thus
add the IN clause: IN(0, 1, 2, 3, 4)




Now, in a form, or in a report, it is somehow to you to specify WHERE,
physically, will be place field [0], field [1], ... while for its
'header',
you can dynamically change the text from '4' to DateAdd("ww", 4, now),
and
so on. (Again, if you want to display the Monday of the week, you have to
adjust the expression accordingly).



Hoping it may help,
Vanderghast, Access MVP



I have a crosstab query I am using on a form with a subform. For some
reason
I can not get the columns to change when the dates change in query.
Please
help!

The following is my SQL Statement:

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.blnDelivered
ORDER BY qryOrdReq.txtAssetDescription
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",Format$((DateAdd("d",1-Weekday([dtmDateNeeded],2),[dtmDateNeeded])),'Short
Date'));


I need the column headings to be Week 1, Week 2, Week 3, etc. matter
what
the actual week numbers are.

Currently my results looks like this:

Item ID Asset 3/26/2007 4/16/2007 4/2/2007
4/23/2007
4603 Mixer 2 1
1 1
2201 Buckets 1 4
8 4
2203 Sponges 4 20
2 5

If someone could tell me how to create the crosstab so that the dates
could
be sorted properly that would be great. Also I need the column
headings
to
on Forms and Reports to change when the dates change.

If that cannot be done then I would settle on using Weeks instead.

Please help!
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

That could be something like:


PIVOT IIf([dtmdateneeded]<(Date()), -1,
DateDiff("ww",Now(),dtmDateNeeded), 2 )
IN(-1, 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);




the optional fourth argument, 2, is the constant for vbMonday (in a query,
you are obliged to use the real constant, 2, not their VBA name, such as
vbMonday).


Hoping it may help,
Vanderghast, Access MVP


Tricia Young said:
Okay, I don't know how you ever figured this out. You are greatness!
Thank
you for your help. The code you have provided gives me what I need. One
last thing, you mentioned Monday being the first day of the week. The
code
you provided gave me Sunday as the first day. How do I customize the code
to
assume Monday is always the first day so that the dates can fall into the
proper column?

Thanks again,

Tricia

Michel Walsh said:
I would change the pivot clause a little bit:

PIVOT
IIf([dtmdateneeded]<(Date()),-1,DateDiff("ww",Now(),[dtmDateNeeded]))
IN(-1, 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);



The number (>=0) represent the week if actual week is week 0. So,
column
11, as example, would have its Monday equals to this Monday + 11*7. So,
for
this week, indeed, column 0 will be this week, 2007.03.26 being its
Monday.
If you run the same query next week, then column 0 will be that week.
Using
Now(), or Date(), that makes the week 0 best described as "the current
week", and the column [1], as one week after the current week, etc.


Note that in general "this Monday" can be given by:

Date() + Choose(DatePart("w", Date()), 1, 0, -1, -2, -3, -4, -5, -6)

assuming Sunday is the first day of the week.


ALSO, the added -1 value, I added in the IN list, and as second argument
of
the iif, it represents anything in the past, not just the previous week,
and
that, because of the iif construction.



Hoping it may help,
Vanderghast, Access MVP


Tricia Young said:
Thank you very much for your help. I think I have what I need.
However,
I
am having some difficulty understanding how this works. Please
explain.
First is my SQL Statement correct?

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.intNetAvail, Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.intNetAvail, qryOrdReq.blnDelivered
ORDER BY IIf([dtmdateneeded]<(Date()),"past
due",DateDiff("ww",Now(),[dtmDateNeeded]))
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",DateDiff("ww",Now(),[dtmDateNeeded])) In
(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);

If correct please explain what i've done. Does thes numbers represent
just
consecutive numbers or do they represent the number for the week?

The Totals field has the correct Qty. But does "0" rep 3/26/2007, "1"
rep
4/2/2007, "2" rep 4/9/2007, etc.?


:

Use a PIVOT based on a DIFFERENCE of date (of difference of WEEK in
facts),
one of the date implied in the difference being a supplied parameter,
something like (for illustration only, it is kept simple):


PIVOT DateDiff( "ww", now, dtmDateNeeded)


so the fields, created, will be 0, 1, 2, 3, 4, ... constants! You
can
thus
add the IN clause: IN(0, 1, 2, 3, 4)




Now, in a form, or in a report, it is somehow to you to specify WHERE,
physically, will be place field [0], field [1], ... while for its
'header',
you can dynamically change the text from '4' to DateAdd("ww", 4,
now),
and
so on. (Again, if you want to display the Monday of the week, you have
to
adjust the expression accordingly).



Hoping it may help,
Vanderghast, Access MVP



I have a crosstab query I am using on a form with a subform. For
some
reason
I can not get the columns to change when the dates change in query.
Please
help!

The following is my SQL Statement:

TRANSFORM Count(qryOrdReq.txtOrderType) AS CountOftxtOrderType
SELECT qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
Count(qryOrdReq.txtOrderType) AS Total
FROM qryOrdReq
WHERE (((qryOrdReq.blnDelivered)<>Yes))
GROUP BY qryOrdReq.intItemID, qryOrdReq.txtAssetDescription,
qryOrdReq.blnDelivered
ORDER BY qryOrdReq.txtAssetDescription
PIVOT IIf([dtmdateneeded]<(Date()),"past
due",Format$((DateAdd("d",1-Weekday([dtmDateNeeded],2),[dtmDateNeeded])),'Short
Date'));


I need the column headings to be Week 1, Week 2, Week 3, etc. matter
what
the actual week numbers are.

Currently my results looks like this:

Item ID Asset 3/26/2007 4/16/2007
4/2/2007
4/23/2007
4603 Mixer 2 1
1 1
2201 Buckets 1 4
8 4
2203 Sponges 4 20
2 5

If someone could tell me how to create the crosstab so that the
dates
could
be sorted properly that would be great. Also I need the column
headings
to
on Forms and Reports to change when the dates change.

If that cannot be done then I would settle on using Weeks instead.

Please help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query: Column Headings! 2
Column headings in a crosstab query 1
crosstab queries 1
Crosstab query and Lookup field problem 3
Crosstab Query Column headings 4
Change column headings in crosstab querry 1
Totalling Rows and Columns in a Crosstab Query 1
Weekly Crosstab Report 1

Top