use result as a FIELD in the design grid

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.

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" 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.

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" 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.


.

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" 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" 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.


.

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" 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" 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.


.



.

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


wrote in message
...
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" 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" 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.


.



.

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


wrote in message
...
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" 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"
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.


.



.



.

Duane, I took the time to write the stuff out. Did it not
make sense to you?

thanks, 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


wrote in message
...
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" 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"
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.


.



.



.

I have looked back at this thread several times but I wouldn't want to make
a suggestion without creating tables, entering data, and then testing. I
haven't had the time to do this. I would be willing to accept a small sample
mdb from you which would save me a ton of time. You would need to email me
off NG to find out where and how to send it to me so that my email client
would delete it. If you are interested, email me at duane AT hookom DOT net.

--
Duane Hookom
MS Access MVP
--

"VJ-mike" wrote in message
...
Duane, I took the time to write the stuff out. Did it not
make sense to you?

thanks, 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


wrote in message
...
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" 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"
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.


.



.



.