How to use an assigned field name as another's field name?

[inso18]

Hello dear users and experts.

I wonder if the following is possible: I have a few HotelXDept fields in a table, all of them contain dept data by date:

Fields:

DDate
Hotel1Dept
Hotel2Dept
Hotel3Dept
Hotel4Dept
...

Now, I need to write a criteria that would use data from HotelXDept according to X data that is found in another field.

I create a new field:

SELECT "Hotel" & [X] & "Dept" AS HotelXClone

How can I use HotelClone to use a value from an appropriate HotelXDept field for a calculation of an expression 1000 - dept?

I attempt this as an expression but it doesn't work:
1000-[&HotelXClone&]

Do you have any ideas? Please help.
Regards.

 

[inso18]

A minor correction: I attempted the expression

Expr1:1000-[&HotelXClone&]

which doesn't work

 

[PHV]

Have a look here:

http://r937.com/relational.html

If you can't normalize your database, a work around is to play with a normalization union query:
SELECT DDate, 1 AS X, Hotel1Dept AS HotelXDept FROM yourTable WHERE Hotel1Dept Is Not Null
UNION SELECT DDate, 2, Hotel2Dept FROM yourTable WHERE Hotel2Dept Is Not Null
UNION SELECT DDate, 3, Hotel3Dept FROM yourTable WHERE Hotel3Dept Is Not Null
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[inso18]

Thanks for your replay PHV. This is not my database, and what I wrote was just a demo for a specific thing.

I'm not sure if I was bright with my request: I was asking if there's a way to use record data as a field name,
for ex: Dlookup("myTable","X","Id='2') gives the result of 2 in
myTable
X:
1
2
3
4
5

I would like to be able to combine "Hotel"&X&"Dept" and use it as a field name for an expression, for ex: Avg(["Hotel"&X&"Dept"]). Is there a way to combine such a syntax?

 

[PHV]

In VBA you may try this:
X = 2
someVariable = DAvg("Hotel" & X & "Dept", "myTable")

In straight SQL, you can't.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[inso18]

Thanks.

Are you sure I can't?

Just to make it clear what I mean is in the condition row instead of

[Hotel2Dept]=10

, to have a string "Hotel2Dept" inside of the brakets

[&"Hotel2Dept"&]=10

 

[PHV]

In straight SQL you can't use parametized field names, period.
Either use a normalization query instead of your bad designed table, or generate the SQL from VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886