I'll try to whittle this down... the data TableB is
500,000 records and 35 fields - a very ugly flat file
generated from a telemetry program (POS). what I'm trying
to clean up is that field 5 for 1 station (out of 60
stations) may equate to a flow value, while field 5 for
another station may equate to pump run hours - very bad,
right?
I have a key TableA that maps each station's flow (and
other) field.
<TableA:> (greatly simplified)
Station: FlowField: TankLevel: PumpRun:
"bismark" "post1" "post2" "post3"
"alder" "post2" "post3" "post33"
and so on for 60 stations...
so if I run a query for the FlowField of Alder the result
is "post2".
Now, <TableB>
Date: Station: Post1: Post2: Post3: (and so on)
"01/01/04" "bismark" "4200" "21.2" "2.2"
"01/02/04" "bismark" "4300" "21.1" "2.5"
"01/01/04" "alder" "" "2472" "x"
"01/02/04" "alder" "" "1356" "x"
and so on, 9000 records for each of 60 stations.
OK, now from the previous query, I know that the FlowField
of Alder the result is "post2".
and the tricky part is how to put the correct FlowField
into the grid... Is it post1, post2, etc? we know from
the previous Query that we need "post2".
So in a new query grid where TableA is linked to TableB
via the Station fields in each, I request the:
Date: Station: Post2:
criteria-"alder"
the results would be:
"01/01/04" "alder" "2472"
"01/02/04" "alder" "1356"
I want that last field name(object, or whatever you call
it!) to come from the previous query, not look it up and
type it manually. maybe that doesn't seem like extra work
to some folks, but I thought it would serve as an
opportunity to learn something. Perhaps this can only
happen with a macro or VB.
If I could also get the station name ("alder") from the
pre-query as well, that'd be even better.
thanks again,
mike.
-----Original Message-----
I'm a visual person and would still beg to have you type some sample records
and the desired output. Without it, I am giving up. What's so difficult
about entering 10-12 records (significant fields only) and then how you want
the final query to display?
--
Duane Hookom
MS Access MVP
thanks Duane, but I have Field 'post1' already available
in TableB...
TableB basically has all the data in it. TableA tells me
which field contains the data I need for a certain
record. So a query of TableA will tell me I need
field 'post1'. But in the new (later) query, how do I set
the query grid to:
Field : post1 (from tableB)
without typing 'post1' into the grid.
Thanks for the patience!
-----Original Message-----
Have you looked at using a Crosstab query? If you set
the
Column Heading to
[Data Location] then you will end up with a column named "post1".
--
Duane Hookom
MS Access MVP
"VJ-mike" <
[email protected]> wrote
in
message
Ok, TableA has a field 'data location', and for a
particular record the value there is 'post1'.
TableB has a field 'post1'.
In my query grid I want to use the found value of 'data
location' for the particular record (result = 'post1'),
and use that as the actual field name. It's like I need a
variable to hold the result of 'post1' and place it
in
the
field/table box in the grid. I haven't been able to use
SetValue to put the literal 'post1' into the field/table
box in the grid. and what I do get is always proceded
with an alias like 'Expr1:'.
This situation stems from using a really ugly flat
file
as
the source!
Thanks for help,
mike.
-----Original Message-----
This is one of those question that providing a little
sample data and
desired output would make this a lot clearer.
--
Duane Hookom
MS Access MVP
"VJ-mike" <
[email protected]>
wrote
in
message
I want to use a queried attribute value as a Field name
in
the query design grid. I tried a Select
statement,
but
at
best, was comming up with something that required an
expression name followed by the attribute - not the
attribute value alone.
thanks,
mike in olympia.
.
.
.
