Calculation/Function as part of the field name.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all:
I am trying to create a query where I need to perform a calculation and/or
function to help determine the field name.

The situation is this........my table has a serries of fields named
BudgetPr1, BudgetPr2,....BudgetPr12. A budget for each of our 12 fiscal
periods and the fiscal period number is not the same as the month (1-Jan,
2-Feb, 3-Mar, etc.) I can figure out the math to calculate/determin the
fiscal period from the month.

What I need to know is how, if there is a way, to assign the value
calculated/determin as part of the field name.

Can anyone please help me.

Thanks,
FatMan
 
Step away from the keyboard! You are trying to "commit spreadsheet". Your
table has serious normalization problems and just will NOT work correctly in
a relational database. Period.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceding any further
on this database.
 
Jerry:
Thanks for your input. I understand the the rules of normalization.

The data I am working with is just from one of many tables in our accounting
system that I am accessing through ODBC. In fact, I don't think the table
breaks any rule of normalization at all. The field names are named the way
they are to indicate which fiscal period the budget belongs to. There is no
duplication of data, each record represents the 12 month budget for a
department.

Even if the field names break the "first normal form", I didn't create it
and there is no way for me to change it. As far as not working....there are
thousands or users using the same accounting system that I am.

Once again thanks for your input. Can anyone offer me some real help?

Thanks,
FatMan
 
"Can anyone offer me some real help?"

That was uncalled for. I too was under the impression that you had a
spreadsheet sort of thing going, particularly when you asked about changing
field names, which is very different from changing label captions or
something else that a user will see on a form or report. If you are
familiar with relational databases in general (as you suggest you are), you
must have realized that renaming a field on the fly is unusual, and that
more explanation would be needed clarify what you need to do.
 
Bruce:
It was not my intention to offend Jerry or anyone else with my reply to him.
If I did then I am sorry. It must have been my frustration in working with
tables designed by others and trying to access the data within our accounting
system.

If you and he thought I was trying to do a spreadsheet thing then I must not
have explained by situation correctly. I will try again...

My db Table has a series of fields called BudgetPeriod1, BudgetPeriod2,
BudgetPeriod3....BudgetPeriod12. Each department has a budget for each
fiscal period. What I am trying to do is extract the sales data from our
accounting system for a user specified date and then bring out the budget for
that period. The period numbers do not match the month (1-Jan, 2-Feb, 3-Mar,
etc) but I can do the calculation to match them up.

What I need to know is this…
How do I create the field name in a query where I can substitute a
variable in the field name? For example: If the user
provides the date
01-Sep-06, I will do a calculation to determine that it is
period X. What
I need to know is how do I create a field name in my query
that will
allow me to have X change to the period associated with the
user
provided date. The field name should look like
BudgetPeriodX . Where
X is the period determine by the user provided date.

If anyone can offer me some help I would greatly appreciate it.

Thanks,
FatMan
 
Frustration is understandable, plus it's Friday the 13th.

I should have made it clear, if I did not, that I do not know how to change
a field name. I also have a difficult time imagining why you would want to.

Let me try to get some clarification here. As I understand it, each budget
period is a month long, but starting on the fifteenth or some such, so that
half of January would be in one budget period and half in the next one, or
something like that. Then, you have written a calculation to determine the
budget period for any given date. In that case, the same code that
determined the correct budget period can be used to generate a text string
that can be used as a label caption or the contents of an unbound text box.

This is all very vague because I still do not understand your database's
structure. Do you have a separate Department table? In non-database terms,
what are you trying to do? You say that "Each department has a budget for
each fiscal period". Is that figure entered somewhere? Then you say you
are trying to "extract the sales data from our accounting system for a user
specified date and then bring out the budget for that period". When you say
'bring out the budget", do you mean display it? Use it as a query
parameter? Is this one department's budget, or the company budget, or what?
Do you mean that you would like the user to enter a date, then view budget
information for the period to which that date belongs? If so, is this user
to view information for all departments, or one department, or what exactly?

Remember, you have the database in front of you. We have your description
only.
 
About the only way I can think of to handle this type of situation is to
build the SQL statement in VBA and then use that. As far as I know there is
no way to do what you are asking if SQL directly.

One other possibility might be to use a union query to normalize the data.
And then use that normalized query to extract your information. With a lot
of data this would be slow since there would not be any indexes available.

SELECT Field1, Field2, BudgetPeriod1, "Period1" as PeriodName
FROM SomeTable
UNION ALL
SELECT Field1, Field2, BudgetPeriod2, "Period2"
FROM SomeTable
UNION ALL
SELECT Field1, Field2, BudgetPeriod3, "Period3"
FROM SomeTable
....
UNION ALL
SELECT Field1, Field2, BudgetPeriod12, "Period12"
FROM SomeTable

WIth that saved as qUniAllPeriods (or whatever name you choose) you could
return all records for period1 using

SELECT *
FROM qUniAllPeriods
WHERE PeriodName ="Period1"
 
Back
Top