Expressions and fields

[Kat]

Is it possible to create an expression(or something else) that will pull data
from different fields depending on certain data in the record that will put
it in one field? Ex. Records include the sales rep of either George, Bob or
Joe but the fields include Texas, California, and Colorado. i want an field
that will show data from the Texas field if George is in the record and
California if Bob is in the record. I know with this example there are other
ways such as cross tabs and forms but I have a really huge nasty looking
database that I can't change that has dozens of names and dozens of fields.
Right now my only solution is to create about a dozen queries isolating the
data and then creating a master query that adds them all together. Using it
won't be any different but adding an additional path would mean adding more
queries and changing expressions.

 

[KARL DEWEY]

First off you table structure is wrong. It should not include fields like
Texas, California, and Colorado. There should be a field name state that
would have the state name as data.

But you can use IIF statement.
IIF([sales rep] = "George", [Texas], IIF([sales rep] = "Bob",
[California], IIF([sales rep] = "Joe", [Colorado], "Error")))

 

[Kat]

First off I know that table is structured incorrectly but I don't have a
choice as that is how the raw data is organized. Second-I do not want to
write an iff statement because it would be nested to the umpteenth time and
adding an additional person would be extremely complicated. I am trying to
build a database that can be used in different buildings taht other can
somewhat easily adjust/add to. An Iff statement is out and reformating the
original table is out, adding the columns together is out. I have been
trying to use a nested dlookup but I can't seem to get it to pull numbers.

First off you table structure is wrong. It should not include fields like
Texas, California, and Colorado. There should be a field name state that
would have the state name as data.

But you can use IIF statement.
IIF([sales rep] = "George", [Texas], IIF([sales rep] = "Bob",
[California], IIF([sales rep] = "Joe", [Colorado], "Error")))
--
KARL DEWEY
Build a little - Test a little

Is it possible to create an expression(or something else) that will pull data
from different fields depending on certain data in the record that will put
it in one field? Ex. Records include the sales rep of either George, Bob or
Joe but the fields include Texas, California, and Colorado. i want an field
that will show data from the Texas field if George is in the record and
California if Bob is in the record. I know with this example there are other
ways such as cross tabs and forms but I have a really huge nasty looking
database that I can't change that has dozens of names and dozens of fields.
Right now my only solution is to create about a dozen queries isolating the
data and then creating a master query that adds them all together. Using it
won't be any different but adding an additional path would mean adding more
queries and changing expressions.

 

[KARL DEWEY]

Ok, alternate solution --
Use a union query to reformat the data into relational database format.
Then use a translation table in the query. It would have two fields - Name
& State. Place it in the query and join on name.
--
KARL DEWEY
Build a little - Test a little

First off I know that table is structured incorrectly but I don't have a
choice as that is how the raw data is organized. Second-I do not want to
write an iff statement because it would be nested to the umpteenth time and
adding an additional person would be extremely complicated. I am trying to
build a database that can be used in different buildings taht other can
somewhat easily adjust/add to. An Iff statement is out and reformating the
original table is out, adding the columns together is out. I have been
trying to use a nested dlookup but I can't seem to get it to pull numbers.

First off you table structure is wrong. It should not include fields like
Texas, California, and Colorado. There should be a field name state that
would have the state name as data.

But you can use IIF statement.
IIF([sales rep] = "George", [Texas], IIF([sales rep] = "Bob",
[California], IIF([sales rep] = "Joe", [Colorado], "Error")))
--
KARL DEWEY
Build a little - Test a little

Is it possible to create an expression(or something else) that will pull data
from different fields depending on certain data in the record that will put
it in one field? Ex. Records include the sales rep of either George, Bob or
Joe but the fields include Texas, California, and Colorado. i want an field
that will show data from the Texas field if George is in the record and
California if Bob is in the record. I know with this example there are other
ways such as cross tabs and forms but I have a really huge nasty looking
database that I can't change that has dozens of names and dozens of fields.
Right now my only solution is to create about a dozen queries isolating the
data and then creating a master query that adds them all together. Using it
won't be any different but adding an additional path would mean adding more
queries and changing expressions.

 

[KARL DEWEY]

Mistake ----
Join [Sales rep] to [Name]
--
KARL DEWEY
Build a little - Test a little

First off I know that table is structured incorrectly but I don't have a
choice as that is how the raw data is organized. Second-I do not want to
write an iff statement because it would be nested to the umpteenth time and
adding an additional person would be extremely complicated. I am trying to
build a database that can be used in different buildings taht other can
somewhat easily adjust/add to. An Iff statement is out and reformating the
original table is out, adding the columns together is out. I have been
trying to use a nested dlookup but I can't seem to get it to pull numbers.

First off you table structure is wrong. It should not include fields like
Texas, California, and Colorado. There should be a field name state that
would have the state name as data.

But you can use IIF statement.
IIF([sales rep] = "George", [Texas], IIF([sales rep] = "Bob",
[California], IIF([sales rep] = "Joe", [Colorado], "Error")))
--
KARL DEWEY
Build a little - Test a little

Is it possible to create an expression(or something else) that will pull data
from different fields depending on certain data in the record that will put
it in one field? Ex. Records include the sales rep of either George, Bob or
Joe but the fields include Texas, California, and Colorado. i want an field
that will show data from the Texas field if George is in the record and
California if Bob is in the record. I know with this example there are other
ways such as cross tabs and forms but I have a really huge nasty looking
database that I can't change that has dozens of names and dozens of fields.
Right now my only solution is to create about a dozen queries isolating the
data and then creating a master query that adds them all together. Using it
won't be any different but adding an additional path would mean adding more
queries and changing expressions.