Multiple field query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with one id field and 20 operation fields. The operation
fields are text, into which the user has made various entries. Each id may
have values in one or more of the twenty operation fields.

I need a query that will give me single column list produced from all 20 of
the opeation fields (over the whole table). My need is to get unique list of
all operations entered into the table. Any help??
 
Sounds like you have not normalized your data. If the value could be in any
of the 20 fields, that tells me that these fields are not unique. It is a
One-to-many relationship. You should have a table that contains the ID and
the OPERATION only. If a particular ID has one operation, it would have one
entry in the table. If a particular ID has ten operations, it would have
ten records in the table. Etc.
 
I completely agree with you.. and the fields are not unique. However, I have
inherited this problem, and been asked if it is possible to come up with the
operations list as I described it. So far, I have not been able to.. Have
you any ideas?
 
I think you can create a UNION query that will pull each of the 20 columns
into one. I have seen posts before that pointed to this, but never done it
myself.

If it were me, I'd create my normalised table and then run 20 update queries
to get the data from you "bad" table into your "good" table. After that (15
minutes?) you would have the data in a normalized state and you'd be good to
go.
 
Thanks.. that sounds like a good way to go.

Rick B said:
I think you can create a UNION query that will pull each of the 20 columns
into one. I have seen posts before that pointed to this, but never done it
myself.

If it were me, I'd create my normalised table and then run 20 update queries
to get the data from you "bad" table into your "good" table. After that (15
minutes?) you would have the data in a normalized state and you'd be good to
go.
Click to expand...
 
Back
Top