Column Heading for a CrossTab query being the next 12 weeks

M

MarieT

Hello,

I have a table with a Qty value for each part. I need to create a query with
12 coulmn headings, where each column heading is the date of the first day of
the week for the next 12 weeks, and the Value I want under each column date
is the Qty value divided by 12. So under each week coulmn I should get the
same value (that is the Qty/12). For example:

If in the table I have:

Part Qty
Part1 84000
Part2 96000

The query will have:

Part 06/02/2008 06/09/2008 06/16/2008 .....

Part1 7000 7000 7000
Part2 8000 8000 8000

I know how to find the first week day of the current date:
Expr1: DateAdd("d",1-Weekday(Date()),Date())

But this only gives me one column, and I can only specify one column
heading. How can I make it give me 12 columns with the correct headings and
the correct same value under each coulmn? This sounds pretty simple, yet I
have not yet found a way to do it.

Any help will be greatly appreciated!

MarieT
 
K

KARL DEWEY

First create a table named CountNumber with field named CountNUM with numbers
from 0 (zero) through your maximum spread.

Your DateAdd("d",1-Weekday(Date()),Date()) gave me 6/1/2008 as first column
in this query --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],DateAdd("d",1-Weekday(Date()),Date()));

My Date()-Format(Date(),"w")+2 gave me 6/2/2008 but is not fully tested.
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date()-Format(Date(),"w")+2);

This gives 6/3/2008, today's date --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date());
 
M

MarieT

Thank you Karl. This definitely worked! I have a question though, Is it
possible to do the same thing with a Select Query instead of a crosstab; What
I mean is: Is there a way to define the column header dynamically depending
on the date, and do this in a Select query? In other word, can I make the
column "label" that precedes a : (colon) be evaluated as an expression and
not a constant?

If there is a way to do this, I will then be able to define my 12 columns
dynamically relative to today's date, in a Select Query.

Thank you so much!

MarieT

KARL DEWEY said:
First create a table named CountNumber with field named CountNUM with numbers
from 0 (zero) through your maximum spread.

Your DateAdd("d",1-Weekday(Date()),Date()) gave me 6/1/2008 as first column
in this query --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],DateAdd("d",1-Weekday(Date()),Date()));

My Date()-Format(Date(),"w")+2 gave me 6/2/2008 but is not fully tested.
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date()-Format(Date(),"w")+2);

This gives 6/3/2008, today's date --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date());

--
KARL DEWEY
Build a little - Test a little


MarieT said:
Hello,

I have a table with a Qty value for each part. I need to create a query with
12 coulmn headings, where each column heading is the date of the first day of
the week for the next 12 weeks, and the Value I want under each column date
is the Qty value divided by 12. So under each week coulmn I should get the
same value (that is the Qty/12). For example:

If in the table I have:

Part Qty
Part1 84000
Part2 96000

The query will have:

Part 06/02/2008 06/09/2008 06/16/2008 .....

Part1 7000 7000 7000
Part2 8000 8000 8000

I know how to find the first week day of the current date:
Expr1: DateAdd("d",1-Weekday(Date()),Date())

But this only gives me one column, and I can only specify one column
heading. How can I make it give me 12 columns with the correct headings and
the correct same value under each coulmn? This sounds pretty simple, yet I
have not yet found a way to do it.

Any help will be greatly appreciated!

MarieT
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not know of any. Search on 'Dynamic Labels' in this newsgroup.
--
KARL DEWEY
Build a little - Test a little


MarieT said:
Thank you Karl. This definitely worked! I have a question though, Is it
possible to do the same thing with a Select Query instead of a crosstab; What
I mean is: Is there a way to define the column header dynamically depending
on the date, and do this in a Select query? In other word, can I make the
column "label" that precedes a : (colon) be evaluated as an expression and
not a constant?

If there is a way to do this, I will then be able to define my 12 columns
dynamically relative to today's date, in a Select Query.

Thank you so much!

MarieT

KARL DEWEY said:
First create a table named CountNumber with field named CountNUM with numbers
from 0 (zero) through your maximum spread.

Your DateAdd("d",1-Weekday(Date()),Date()) gave me 6/1/2008 as first column
in this query --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],DateAdd("d",1-Weekday(Date()),Date()));

My Date()-Format(Date(),"w")+2 gave me 6/2/2008 but is not fully tested.
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date()-Format(Date(),"w")+2);

This gives 6/3/2008, today's date --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date());

--
KARL DEWEY
Build a little - Test a little


MarieT said:
Hello,

I have a table with a Qty value for each part. I need to create a query with
12 coulmn headings, where each column heading is the date of the first day of
the week for the next 12 weeks, and the Value I want under each column date
is the Qty value divided by 12. So under each week coulmn I should get the
same value (that is the Qty/12). For example:

If in the table I have:

Part Qty
Part1 84000
Part2 96000

The query will have:

Part 06/02/2008 06/09/2008 06/16/2008 .....

Part1 7000 7000 7000
Part2 8000 8000 8000

I know how to find the first week day of the current date:
Expr1: DateAdd("d",1-Weekday(Date()),Date())

But this only gives me one column, and I can only specify one column
heading. How can I make it give me 12 columns with the correct headings and
the correct same value under each coulmn? This sounds pretty simple, yet I
have not yet found a way to do it.

Any help will be greatly appreciated!

MarieT
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

You might check out this posting ----
:
I'm posting this in case someone in the future needs this same thing. Notice
in my original post that I was looking to change _either_ the field name in
the query OR the labels in the report based on that query.

I found the following page on Microsoft's Developer's Network website:
http://msdn.microsoft.com/en-us/library/aa172343(office.11).aspx

Basically it walks you through setting up a macro that will rename your
controls in a report (or form) based on an expression. So, I created a report
based on my original query (the one that had the label names "m2", "m3" etc.
that I didn't like), then created a macro that opened that report in layout
view and renamed those labels using Karl's suggested DateAdd function. It
looks like this:

OpenReport qry_ForecastChanges, Layout, , , Normal
SetValue [Reports]![qry_ForecastChanges]![m2_Label].[Caption],
Format(DateAdd("m",1,(Date())),"mmm-yy")
SetValue [Reports]![qry_ForecastChanges]![m3_Label].[Caption],
Format(DateAdd("m",2,(Date())),"mmm-yy")
.....
SetValue [Reports]![qry_ForecastChanges]![m26_Label].[Caption],
Format(DateAdd("m",25,(Date())),"mmm-yy")
OpenReport qry_ForecastChanges, Report, , , Normal


Thanks again, Karl, for setting me on the right path!!

Ryan C.

--
KARL DEWEY
Build a little - Test a little


MarieT said:
Thank you Karl. This definitely worked! I have a question though, Is it
possible to do the same thing with a Select Query instead of a crosstab; What
I mean is: Is there a way to define the column header dynamically depending
on the date, and do this in a Select query? In other word, can I make the
column "label" that precedes a : (colon) be evaluated as an expression and
not a constant?

If there is a way to do this, I will then be able to define my 12 columns
dynamically relative to today's date, in a Select Query.

Thank you so much!

MarieT

KARL DEWEY said:
First create a table named CountNumber with field named CountNUM with numbers
from 0 (zero) through your maximum spread.

Your DateAdd("d",1-Weekday(Date()),Date()) gave me 6/1/2008 as first column
in this query --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],DateAdd("d",1-Weekday(Date()),Date()));

My Date()-Format(Date(),"w")+2 gave me 6/2/2008 but is not fully tested.
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date()-Format(Date(),"w")+2);

This gives 6/3/2008, today's date --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date());

--
KARL DEWEY
Build a little - Test a little


MarieT said:
Hello,

I have a table with a Qty value for each part. I need to create a query with
12 coulmn headings, where each column heading is the date of the first day of
the week for the next 12 weeks, and the Value I want under each column date
is the Qty value divided by 12. So under each week coulmn I should get the
same value (that is the Qty/12). For example:

If in the table I have:

Part Qty
Part1 84000
Part2 96000

The query will have:

Part 06/02/2008 06/09/2008 06/16/2008 .....

Part1 7000 7000 7000
Part2 8000 8000 8000

I know how to find the first week day of the current date:
Expr1: DateAdd("d",1-Weekday(Date()),Date())

But this only gives me one column, and I can only specify one column
heading. How can I make it give me 12 columns with the correct headings and
the correct same value under each coulmn? This sounds pretty simple, yet I
have not yet found a way to do it.

Any help will be greatly appreciated!

MarieT
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

Dynamic Headings in a crosstab query 2
Crosstab Query Column headings 4
Crosstab Queries by week 2
Using Crosstab query Wizard to sum the quantities in a month. 2
Help with crosstab query 10
Crosstab Query Omitting Some Items 1
Crosstab Query based on Ordinal Values 6
Select Query & Crosstab Query 1

Top