Create field/source name by expression?

[aero-spaces]

I'm posting this in General because I think it overlaps many different areas.

I have a table that I receive from a customer that shows forecasted need
over the next 2 years. Their database is set so that the current month is in
field named "m1", and "m2" would therefore be next month (current month + 1),
and "m3" is the following month (current month + 2), etc.

Is it possible to dynamically rename the field names either in the query OR
the labels in the report based on a query so that, for example, [m2] becomes
something LIKE {=Month() +1} and have it display the name of the month in a
Jul-08 type date format?

It seems like this would venture into the realm of modules/VB, which I am
very inexperienced at, but am willing to explore further.

Let me know if you need more information to give the best advice!

Thanks!
Ryan C.

 

[KARL DEWEY]

That kind of table structure is a spreadsheet.
It should be like this --
ActionDate - DateTime
DataValue - Number
etc.

Use this query substituting your field names to re-organize the data.
SELECT [YourTable].[FirstFieldName], DateAdd("m",0,(Date() AS ActionDate,
[M1] AS DataValue
FROM [YourTable]
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",1,(Date() AS
ActionDate, [M2] AS DataValue
FROM [YourTable]
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",2,(Date() AS
ActionDate, [M3] AS DataValue
FROM [YourTable]
.......
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",23,(Date() AS
ActionDate, [M24] AS DataValue
FROM [YourTable];

 

[aero-spaces]

Thanks for your reply. With a little doing, here's what I have, and what I
have come to understand. Let's get a little sample data going here.. current
table (or spreadsheet, as you describe it) layout:

ptno, m1, m2,...., m26 (ptno= part number, m1=current month, etc.)
1234A, 15, 23,...., 45
7890B, 7, 15,...., 20
4567C, 1, 0,...., 1

By applying the suggested query (with a couple of syntax corrections):
SELECT [tbl_CombinedForecasts].[ptno], DateAdd("m",0,(Date())) AS ActionDate,
[m1] AS DataValue
FROM [tbl_CombinedForecasts]
UNION ALL SELECT [tbl_CombinedForecasts].[ptno], DateAdd("m",1,(Date())) AS
ActionDate, [m2] AS DataValue
FROM [tbl_CombinedForecasts]
UNION ALL SELECT [tbl_CombinedForecasts].[ptno], DateAdd("m",25,(Date())) AS
ActionDate, [m26] AS DataValue
FROM [tbl_CombinedForecasts];

I yield the following query results:

ptno, ActionDate, DataValue
1234A, 6/2/2008, 15
7890B, 6/2/2008, 7
4567C, 6/2/2008, 1
1234A, 7/2/2008, 23
7890B, 7/2/2008, 15
4567C, 7/2/2008, 0
1234A, 7/2/2010, 45
7890B, 7/2/2010, 20
4567C, 7/2/2010, 1

I kinda see what this is doing--basically converting from a spreadsheet
layout where each part number is a row and all the various months are columns
to a more database friendly version where each part number/month combines to
form a single record. I can see where this will be beneficial for other
things I am working on, but I'm having trouble applying it to what I'm doing
here. I am taking the customer's spreadsheet for today and comparing it to
their spreadsheet for yesterday and having it return only values that have
changed and making a report based on that query (and just using Conditional
Formatting to highlight the values that have changed). In this case, keeping
it in spreadsheet format works well for me so far, so I'm having a hard time
figuring out how to take the suggested query and making it work here.

Do I do a make table query from the results of my initial query, then run
this suggested query on THAT table to put those results in this new format?
If so, then what do I do to get it back in the format I need (same basic
layout as original)?

Thanks for your help so far!
Ryan C.

Use this query substituting your field names to re-organize the data.
SELECT [YourTable].[FirstFieldName], DateAdd("m",0,(Date() AS ActionDate,
[M1] AS DataValue
FROM [YourTable]
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",1,(Date() AS
ActionDate, [M2] AS DataValue
FROM [YourTable]
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",2,(Date() AS
ActionDate, [M3] AS DataValue
FROM [YourTable]
......
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",23,(Date() AS
ActionDate, [M24] AS DataValue
FROM [YourTable];

 

[KARL DEWEY]

what do I do to get it back in the format I need (same basic layout as
original)?
A crosstab query would put it back.
--
KARL DEWEY
Build a little - Test a little

Thanks for your reply. With a little doing, here's what I have, and what I
have come to understand. Let's get a little sample data going here.. current
table (or spreadsheet, as you describe it) layout:

ptno, m1, m2,...., m26 (ptno= part number, m1=current month, etc.)
1234A, 15, 23,...., 45
7890B, 7, 15,...., 20
4567C, 1, 0,...., 1

By applying the suggested query (with a couple of syntax corrections):
SELECT [tbl_CombinedForecasts].[ptno], DateAdd("m",0,(Date())) AS ActionDate,
[m1] AS DataValue
FROM [tbl_CombinedForecasts]
UNION ALL SELECT [tbl_CombinedForecasts].[ptno], DateAdd("m",1,(Date())) AS
ActionDate, [m2] AS DataValue
FROM [tbl_CombinedForecasts]
UNION ALL SELECT [tbl_CombinedForecasts].[ptno], DateAdd("m",25,(Date())) AS
ActionDate, [m26] AS DataValue
FROM [tbl_CombinedForecasts];

I yield the following query results:

ptno, ActionDate, DataValue
1234A, 6/2/2008, 15
7890B, 6/2/2008, 7
4567C, 6/2/2008, 1
1234A, 7/2/2008, 23
7890B, 7/2/2008, 15
4567C, 7/2/2008, 0
1234A, 7/2/2010, 45
7890B, 7/2/2010, 20
4567C, 7/2/2010, 1

I kinda see what this is doing--basically converting from a spreadsheet
layout where each part number is a row and all the various months are columns
to a more database friendly version where each part number/month combines to
form a single record. I can see where this will be beneficial for other
things I am working on, but I'm having trouble applying it to what I'm doing
here. I am taking the customer's spreadsheet for today and comparing it to
their spreadsheet for yesterday and having it return only values that have
changed and making a report based on that query (and just using Conditional
Formatting to highlight the values that have changed). In this case, keeping
it in spreadsheet format works well for me so far, so I'm having a hard time
figuring out how to take the suggested query and making it work here.

Do I do a make table query from the results of my initial query, then run
this suggested query on THAT table to put those results in this new format?
If so, then what do I do to get it back in the format I need (same basic
layout as original)?

Thanks for your help so far!
Ryan C.

Use this query substituting your field names to re-organize the data.
SELECT [YourTable].[FirstFieldName], DateAdd("m",0,(Date() AS ActionDate,
[M1] AS DataValue
FROM [YourTable]
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",1,(Date() AS
ActionDate, [M2] AS DataValue
FROM [YourTable]
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",2,(Date() AS
ActionDate, [M3] AS DataValue
FROM [YourTable]
......
UNION ALL SELECT [YourTable].[FirstFieldName], DateAdd("m",23,(Date() AS
ActionDate, [M24] AS DataValue
FROM [YourTable];

 

[aero-spaces]

Thanks again for your help. I'm also fairly new to crosstab queries

I think I've *almost* got this to the format I'm looking for. My crosstab
query now looks like this:

TRANSFORM Sum(qry_ForecastExperiment.[DataValue]) AS SumOfDataValue
SELECT qry_ForecastExperiment.[ptno]
FROM qry_ForecastExperiment
GROUP BY qry_ForecastExperiment.[ptno]
PIVOT Format([ActionDate],"mmm-yy");

Pretty much the _only_ issue I have with this now is that the column
headings--while in the Jun-08 format I want--when sorted in Ascending order,
are sorted alphabetically, not by date, thus Jul-08 is before Jun-08, etc. Is
this a matter of Access not recognizing this as a date field? Any way around
this, or will I just need to manually order the fields when I produce my
report based on this?

Thanks again!
Ryan C.

 

[KARL DEWEY]

There are two ways.
First is the change format like this --
PIVOT Format([ActionDate],"yy-mmm");

The other means constant updating of the query --
PIVOT Format([ActionDate],"mmm-yy") IN("JAN-08", "FEB-08", "MAR-08",
....."DEC-08");

--
KARL DEWEY
Build a little - Test a little

Thanks again for your help. I'm also fairly new to crosstab queries

I think I've *almost* got this to the format I'm looking for. My crosstab
query now looks like this:

TRANSFORM Sum(qry_ForecastExperiment.[DataValue]) AS SumOfDataValue
SELECT qry_ForecastExperiment.[ptno]
FROM qry_ForecastExperiment
GROUP BY qry_ForecastExperiment.[ptno]
PIVOT Format([ActionDate],"mmm-yy");

Pretty much the _only_ issue I have with this now is that the column
headings--while in the Jun-08 format I want--when sorted in Ascending order,
are sorted alphabetically, not by date, thus Jul-08 is before Jun-08, etc. Is
this a matter of Access not recognizing this as a date field? Any way around
this, or will I just need to manually order the fields when I produce my
report based on this?

Thanks again!
Ryan C.

 

[KARL DEWEY]

The first is wrong -- needs to be --
PIVOT Format([ActionDate],"yy-mm");
OR
PIVOT Format([ActionDate],"yy-mm mmm");

--
KARL DEWEY
Build a little - Test a little

Thanks again for your help. I'm also fairly new to crosstab queries

I think I've *almost* got this to the format I'm looking for. My crosstab
query now looks like this:

TRANSFORM Sum(qry_ForecastExperiment.[DataValue]) AS SumOfDataValue
SELECT qry_ForecastExperiment.[ptno]
FROM qry_ForecastExperiment
GROUP BY qry_ForecastExperiment.[ptno]
PIVOT Format([ActionDate],"mmm-yy");

Pretty much the _only_ issue I have with this now is that the column
headings--while in the Jun-08 format I want--when sorted in Ascending order,
are sorted alphabetically, not by date, thus Jul-08 is before Jun-08, etc. Is
this a matter of Access not recognizing this as a date field? Any way around
this, or will I just need to manually order the fields when I produce my
report based on this?

Thanks again!
Ryan C.

 

[aero-spaces]

Yea, after I posted that I resigned myself to accepting "yyyy-mm" as an
alternative. Not exactly what I wanted, but it rarely is exactly what I want
when I'm working in Access.

Thanks again for your help!
Ryan C.

The first is wrong -- needs to be --
PIVOT Format([ActionDate],"yy-mm");
OR
PIVOT Format([ActionDate],"yy-mm mmm");

--
KARL DEWEY
Build a little - Test a little

Thanks again for your help. I'm also fairly new to crosstab queries

I think I've *almost* got this to the format I'm looking for. My crosstab
query now looks like this:

TRANSFORM Sum(qry_ForecastExperiment.[DataValue]) AS SumOfDataValue
SELECT qry_ForecastExperiment.[ptno]
FROM qry_ForecastExperiment
GROUP BY qry_ForecastExperiment.[ptno]
PIVOT Format([ActionDate],"mmm-yy");

Pretty much the _only_ issue I have with this now is that the column
headings--while in the Jun-08 format I want--when sorted in Ascending order,
are sorted alphabetically, not by date, thus Jul-08 is before Jun-08, etc. Is
this a matter of Access not recognizing this as a date field? Any way around
this, or will I just need to manually order the fields when I produce my
report based on this?

Thanks again!
Ryan C.

what do I do to get it back in the format I need (same basic layout as
original)?
A crosstab query would put it back.

 

[aero-spaces]

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.