Using queried value to select field in a table

[KBrent]

Seems like this should be easy - I just don't know how.

I have a table of temperatures recorded every minute for several tanks. Each
tank is a field labeled Tank01 through Tank06.

A separate query generates a value that is identical to the Tanks field
name. I would like to use this query generated value to select only one field
from the table of temperature.

The final query would display only two fields both from the temperature
table, Time and Temperature of select Tank ID.

Thanks,
KBrent

 

[KARL DEWEY]

Your database should look like this --
Time Tank Temp

Use a union query to put it in a normalized fashion --
SELECT Time, 1 AS Tank, Tank01 as Temp
FROM YourTable
UNION ALL SELECT Time, 2 AS Tank, Tank02 as Temp
FROM YourTable
UNION ALL SELECT Time, 3 AS Tank, Tank03 as Temp
FROM YourTable
UNION ALL SELECT Time, 4 AS Tank, Tank04 as Temp
FROM YourTable
UNION ALL SELECT Time, 5 AS Tank, Tank05 as Temp
FROM YourTable
UNION ALL SELECT Time, 6 AS Tank, Tank06 as Temp
FROM YourTable;

Then use a select query with Tank number as criteria.

 

[KBrent]

Thanks for the Reply.

I have consider this solution. The problem is that my temp table is as
follows:
Date/Time Tank01 Tank02 .... Tank60

where Temp values are recorded of every 5 minutes for 60 tanks. I can't
really change the structure of this table because it is generated from a
external source and having an individual record for each of 60 tanks at 5
minutes intervals would result in a massive table.

The queried value is generated from a linked table. It looks like this:
TankID BeginTime EndTime

The query only returns 1 set of values. Example:
Tank45 1/5/09 14:45 1/9/09 09:05

I'd like to use this result to query back to the temp table and generate an
output like this by selecting only the Tank45 column in the temp table
limited to the range of the Begin to End times generated in the first query:
Date/Time Tank45temp
1/5/09 14:45 75.0
1/5/09 14:50 75.1
..
..
..
1/9/09 09:05 89.1

Is this possible
Thanks again,
KBrent

 

[KARL DEWEY]

Use the union query as I said naming it TankTempUnion.
Then in the select query do this --
SELECT TankTempUnion.*
FROM TankTempUnion, YourLinkedTable
WHERE [TankTempUnion].[Tank]=[TankID] AND ([TankTempUnion].[Time] Between
BeginTime AND EndTime);

You need to change the field from [TankTempUnion].[Time] to something else
as Time is a rerserved word.