How To Name Columns With Code

[doyle60]

I have a query that has six key fields (Style, Color_Code, Lbl_Code,
Dimension, Size, and Year) and 12 months (Feb, Mar, Apr, May, Jun,
Jul, Aug, Sep, Oct, Nov, Dec, Jan). January is the last month of the
year, so January in the 2008 record is really Jan 2009. The query is
for users to enter Sales Projections.

Anyway, how do I get a query to return the months all on the same
record (not separating between years) with the current month being
named 1 and the months after being named 2, 3, 4, 5, 6, and so on?
For the months before the current month, I would want to return 0, -1,
-2, -3, -4 and so on down.

Hence, if I ran the query today, I would want to see:

Aug: -1
Sep: 0
Oct: 1
Nov: 2
Dec: 3
Jan: 4
Feb: 5
Mar: 6
etc.

I will be using these renamed column names in computations as well.
That is, I may want to add together columns that are greater than X
but lower than Y. That is why I am going with numeric column names
even though they are hard to deal with. Is that wise? I have a lot
of experience with queries but never referred to the column names
formulaicly. If it would be best to name the columns beginning with
letters anyway, please advise. I could name them M01, M02, M03 and
for the negatives MN00, MN01, MN02, etc. (I do not have to worry
about going over a hundred months, I will probably filter out years
that are two years old or two years in the future anyway.)

Thanks,

Matt

 

[Tom van Stiphout]

On Sat, 18 Oct 2008 03:36:17 -0700 (PDT), "(e-mail address removed)"

The Month function is going to be your friend, as well as perhaps the
DateDiff function.
select *
from Orders
order by Month(OrderDate)
will give you orders sorted by month regardless of year.

select Month(OrderDate)-Month(Date)+1 as MonthNo
from Orders
will give you the month numbers with the current month being 1.

-Tom.
Microsoft Access MVP

 

[doyle60]

Thanks but I'm a bit confused. I do not have an OrderDate field or
anything similar with a different name. My table is a grid that
appears like this:

Year Feb---Mar---Apr---May---Jun---...---Jan
2008 50-----50----100---300----50---...---300
2008 10---150----200---300---230---...--200
....

I took out the other five keys (Style, Color_Code, Lbl_Code,
Dimension, Size). There is no OrderDate, you see, but there are
fields called "Feb" and "Mar" and "Apr", etc. (Also "Jan" is really
the current year plus one.)

To use your method, I believe, I would have to turn this data around
first using a union query or something. I'm sorry, I'm just not at my
desk at the moment, being the weekend, so I'm just trying to figure
this out in my head. Are you saying I need to flip it into a union
query and assign my month names to a real date and than perform this
operation and then flip it back again? If so, is there a way to avoid
that?

Thanks,

Matt

 

[Tom van Stiphout]

On Sun, 19 Oct 2008 04:03:39 -0700 (PDT), "(e-mail address removed)"

Sorry I did not imagine such bad database design

I hope you can appreciate from my previous answer how nice it would be
IF you had a good db design. Perhaps it's not too late.

If you don't change the design, I think you'll be looking at a 12-part
Union query.

-Tom.
Microsoft Access MVP

 

[suruat]

Maybe this may help? First, create a table called tblFiscalYear with fields
Fiscal_Month and Fiscal_period, both text type. Populate Fiscal_Month and
Fiscal_period like Feb as 01, Mar as 02, ...,Jan as 12. For the query below,
I call your query tblProjections and refer to your field Year as
Projection_Year because Access gives a warning about using a reserved name.

SELECT tblProjections.Projection_Year, tblFiscalYear.Fiscal_period,
Sum(Choose([Fiscal_period],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec],[Jan])) AS Total
FROM tblProjections, tblFiscalYear
GROUP BY tblProjections.Projection_Year, tblFiscalYear.Fiscal_period;

 

[doyle60]

No, Tom, it is actually good database design. My other three tables
are exactly as you thought they should be. I turn them into a monthly
grid like the projection table through manipulations. When they enter
the projections they get to see the past months' histories: Past
Sales, Open Sales, and Production. The layout is the best possible
layout they could have for analyzing and entering Projections by
month. Whatever it takes to turn that table around is my task and I
don't believe making my task easier in expense of the user. If it is
possible, than it is possible. Twelve-part union queries are nothing
to me! I'm a union queriest!

Perhaps you would have done differently and created the table as you
want, perhaps using the first day of the month for each projection and
gone through a lot of trouble for the user to easily "see" and enter
the data when doing your form.

Matt

 

[doyle60]

Update:
I have built my queries and have obtained the results I need.

I made my job a bit easier by only figuring out the numbers for the
previous year, the current year, and the next year, fixed 36 months.
I renamed the months 1 through 12 in a query that filters for the
previous year, and did the same for the other two years, renaming them
13 through 24, and 25 through 36, respectively.

Than I did a union query that turned the months into real dates. Than
I did a query that renamed the months with "1" standing in for the
currect month, and progressing backwards and forwards from there for
the other months. The formula was:

(((Format([ProjDate],"yyyy")-
Format(Date(),"yyyy"))*12)+Format([ProjDate],"m")-Format(Date(),"m"))
+1

which I had used for earlier queries (based on well-designed
tables ).

Lastly, I performed a Fake Crosstab. (I always have trouble with the
parameter values in Crosstabs so when they have a finite number of
fields, I just build it myself (filtering and summing) and naming the
fields myself.

M04: IIf([MonthDiff]=4,[ProjUnits],0)

Anyway. Thanks for your input. I was just hoping there was an easier
way to do all this.

Matt