naming fields unconventionally

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

Guest

Hello!
A while ago a user requested an Access database that would give some of the
same functionality as Excel. I stooped to naming columns with year names,
and now that the new year is approaching the only glitch will be one very
important column in a query:
Bill_Rates:
IIf(Year([Transaction_date])=2005,[Rates].[2005],IIf(Year([Transaction_date])=2004,[Rates].[2004],IIf(Year([Transaction_date])=2003,[Rates].[2003],IIf(Year([Transaction_date])=2002,[Rates].[2002],0))))

I would love to put in: bill_rates: [rates].[Year(transaction_date)]
but, that wouldn't work - all just to use the field: [rates].[2005].
QUESTION:
Within a query, is there any way to point to the correct field without using
it's name?

I appreciate your help,
Michele
 
Hi Michele,

If I read your question correctly, you have a table called "Rate" with
fields "2005", "2004", "2003", "2002", and "2001". Then the rate info is
store under those fields. That is why your query have to reference the field
name to get the rate.
This is your table looks like.

Rate:
2005 2004 2003 2002 2002
===== ====== ===== ==== ====
2% 3% 1.5% 0.5% 2%

I have not tested this. If you create your table differently, have a table
called "RateInfo" with field "Y_Fld" and "Rate".

This is your new table looks like.

RateInfo:
Y_Fld Rate
===== ======
2005 2%
2004 3%
2003 1.5%
2002 0.5%
2001 2%

Then your query will be like this:
DLookup("Rate", "RateInfo", "Y_Fld = '" & Year([Transaction_date]) & "'")

It is simplier than what you had. But it might not be what your are looking
for.

Hope this will help.
 
On Thu, 16 Dec 2004 10:13:06 -0800, Michele L <Michele
QUESTION:
Within a query, is there any way to point to the correct field without using
it's name?
Click to expand...

No.

Excel is a spreadsheet; Access is a database. Applying spreadsheet
thinking to a database WILL (as in this case) produce a flawed design.

I'd suggest normalizing the data to a tall-thin table and, if needed
for presentation purposes, running a Crosstab query to get the years
across the top.

If you can't do it that way, you'll need to write VBA code to
dynamically construct the SQL, and you'll also need to redesign your
tables, forms, and queries when your predefined yearnumber fields run
out.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top