How To create Dynamic Coloum names?

[Phil Smith]

I am building a query. I takes sales data for the last twelve months
based on now(), totals and manipulates it, and spits it out in 12
monthly intervals.

So, after the breakdown and grouping data, the query returns Month1,
month2, month3...

What I want to do now, is to change it so that instead of Month1.
Month2, I would get 2007-01, 2007-02, etc. I can generate the coloum
label, but how do I get that as the field name?

Thanx

 

[Allen Browne]

In query design view, type this into the field row:
Format([YourDateFieldNameHere], "yyyy\-mm")
Substitute your date field.

You can use that in place of the expression you get for Month1 etc.

If the crosstab will be used for a report, you need to consider that the
column names will change over time, and so the report fields will not be
found. For that purpose, the Month1 etc will be better. If you do need to
turn this into a report, you might check out Duane Hookom's suggestions
here:
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

 

[Phil Smith]

Sorry I am taking so long to get back to this. I think you
misunderstand what I am doing here. This is not a crosstab.

Category Item M1Sales M1Margin M1Margin% M2Sales M2Margin M2Margin%.....


Data will hit several sets of fields. By running it in Jan 2009

M1 represents sales in a range of Dec 2008 to Jan 2008
M2 represents sales in a range of Nov 2008 to Dec 2007
M3 represents sales in a range of Oct 2008 to Nov 2007

This is a 12x12 month trailing report, based on NOW(). And a sale will
show up in any Mx where the sale date is within that range.

Your solution creates a value, which a crosstab converts to a coloumn
heading.

What I need is a dynamic coloum heading, (based on one formula which
generates the range as a string) and a seperate value based on a formula
which determines whether or not a record fits the criteria for that field.

 

[Allen Browne]

You have *fields* named M1Sales, M2Sales, M3Sales etc for your different
months? That's not a normalized design. Whenever you see repeating fields
like that, it always means you need a table with many related records
instead of having many repeating fields in the one table (like a
spreadsheet.)

I'm not really clear what you are trying to do here, but you can alias a
field in your query just by typing another name in front of it. If you need
to to more than that, the solution will be to create a normalized design.
Post a reply if you need details on how to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry I am taking so long to get back to this. I think you misunderstand
what I am doing here. This is not a crosstab.

Category Item M1Sales M1Margin M1Margin% M2Sales M2Margin M2Margin%.....


Data will hit several sets of fields. By running it in Jan 2009

M1 represents sales in a range of Dec 2008 to Jan 2008
M2 represents sales in a range of Nov 2008 to Dec 2007
M3 represents sales in a range of Oct 2008 to Nov 2007

This is a 12x12 month trailing report, based on NOW(). And a sale will
show up in any Mx where the sale date is within that range.

Your solution creates a value, which a crosstab converts to a coloumn
heading.

What I need is a dynamic coloum heading, (based on one formula which
generates the range as a string) and a seperate value based on a formula
which determines whether or not a record fits the criteria for that field.

In query design view, type this into the field row:
Format([YourDateFieldNameHere], "yyyy\-mm")
Substitute your date field.

You can use that in place of the expression you get for Month1 etc.

If the crosstab will be used for a report, you need to consider that the
column names will change over time, and so the report fields will not be
found. For that purpose, the Month1 etc will be better. If you do need to
turn this into a report, you might check out Duane Hookom's suggestions
here:
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

 

[Phil Smith]

Yes. I have fields with those names, because I can not figure out how
to name them dynamicly.
No, this is not an issues of table design. It is an issue of query
design.

Another way to describe the problem: Have a report with several
different fields, the goal is to name the fields in the query based on
run-time user input, say from a form. Actually, The names will be
caculated strings, but same concept.

As far as what I am really creating, I will try to explain it more
thoroughly.

Data set is
Date, Brand, Cust#, Item#, Sale QTY, Price, Cost, commission.
Very basic stuff. It is the query that is a little less than basic...

Let us just look at Sales Dollars. The query might be grouped by Brand,
for instance, with twelve data fields, one for each month. The dataset
being represented will go back two full years, based on the current
date, so that it will change in real time as the report is run. Assume
it is being run in January 09.

Brand M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12.

The first field M1, will contain sales between Jan 08 and Dec 08.
Actually, between last month and 11 months before last month.
The 2nd field M2, will contain sales between Dec 07 and Nov 08.
Actually, between the month before last, and 11 months before the month
before last.
The third field M3 will contain sales between Nov 07 and Oct 08.
Actualy, three months ago, ...

Each field represents a full year of sales, that year long window
sliding back one month for each successive field, wiht a great deal of
overlap, which is the idea. Any sale in October 08 will be reflected in
M1, M2, and M3, but not in M4 and beyond, for instance.

The way I am doing this is as follows. (warning, getting too deep into
these formulas will require headache medicine. It is all nasty date
math. M1 It can be summed up as:
Sum (iif(date is within the year ending last month, (amount shipped
times unit price),0))

For M1, which represents sales Jan08-Dec08
M1:
Sum(IIf(invoice_h!invoice_date<=DateAdd("m",-1,DateAdd("d",-1,CDate(Month(Now())
& "/1/" & Year(Now())))) And
invoice_h!invoice_date>=DateAdd("m",-13,DateAdd("d",-1,CDate(Month(Now())
& "/1/" & Year(Now())))),(invoice_d!price*invoice_d!ship_qty),0))

This tallies the total sales if the date falls between last month, and
13 months ago. It is ugly because I have to go from the last day of
last month, whether that is the 28th, 29th, 30th or 31st, regardless of
whether today is Jan 1 09 or Jan 12 09.

M2:
Sum(IIf(invoice_h!invoice_date<=DateAdd("m",-2,DateAdd("d",-1,CDate(Month(Now())
& "/1/" & Year(Now())))) And
invoice_h!invoice_date>=DateAdd("m",-14,DateAdd("d",-1,CDate(Month(Now())
& "/1/" & Year(Now())))),(invoice_d!price*invoice_d!ship_qty),0))

If I run it in February, the date ranges of all of those 12 fields will
shift a month. I want to be able to name the fields "JAN08-DEC08",
"DEC07-NOV08", etc. without user intervention. I could restructure this
report as a crosstab if I was just using Sales, but for each of 12 ryear
long periods, I am actually pulling out 5 different values, putting it
beyond the ability of a crosstab.

I can build the formula to do the math, and I can build the formula to
create the strings "JAN08-DEC-08", "DEC07-NOV08", I just need to figure
out how to put that string in as the field name. The result will be
thrown out into an Excel Spreadsheet.

Clear as Mud, right?

Thanx

 

[Phil Smith]

Oh, Just to be clear. No fields in my data tables have names like m1,
m2, etc. Just the fields in my query, until I figure it out...

 

[Allen Browne]

Phil, there is probably an easier way to do this.

Would a crosstab query be possible? For the Column Heading, use:
Format([invoice_date], "yyyy\-mmm")

 

[Phil Smith]

Nope. A crosstab query takes each record, and assigns a single value to
a single bucket. In this case, that single record may end up in 1 of up
to all of twelve buckets. That is for a single value, and I will be
working with five different values, (dollars, ship qty, margin, margin
percentage, commission) for each of twelve year long windows.

If there is an easier way to do it, I would love to hear it, but true
twelve month trailing reports are a biatch.