July 10th, 2012, 11:13 AM
Normalize query for non-normalized table
I am having to extract data from a table with 120 fields. It is the most non-normalized table I have ever had to work with. There are about 40 fields that are essentially "flags", and each record could have any combination of those fields set.
What I need to end up with is a set of records where each set field becomes a text field in an individual row in the results. For example, assuming the table looked like this:
And the data looked like this:
Name Field1 Field2 Field3 Field4
George 0 1 1 0
Frank 0 0 1 1
I want the results to look like this:
July 10th, 2012, 11:53 AM
where Column1 = '1'
where Column2 = '1'
July 10th, 2012, 12:58 PM
notice how in his masterfully understated and subtle way, swampboogie is reminding you that tables have columns, not fields
July 10th, 2012, 01:02 PM
I was hoping there was a better way then having to do a massive union query. Oh well...
and btw - I cut my teeth on dBase III, back when they were called "fields". Old habits die hard.