Inherited Un-normalized data!!!!

[Kila]

Can anyone help me!!! I have inherited a project that has un-normalized data. The project is a survey, and we need data back ASAP, so I do not have time to restructure the thing. The designer set it up so that each possible answer is a seperate field, about 102 of them. For example, we have 10 different sites in our organization. Each site has it's own field, so site 1 is saved in Field 1, site 2 is saved in Field 2, etc. i.e., 37 out of 1500 surveys have selected "GenericCity" as the answer to Field1, and all the other answers in Field1 are blank. The same thing for all the surveys from "AnotherCity" in Field2.

Can anyone recommend the easiest way to sort this data by city & answer (to the other 90+ questions) without writing 102 queries?

 

[uncle-lai]

Would it be easier to export you tables to Excel, re-arrange the data, then import them back to Access to new tables?

Just a suggestion before you find other better way.

Peter

 

[Len Boorman]

Without looking at the data its a bit difficult but one thing I have done when faced with a similar problem was to use a UNION query.

Basically the problem I had was that there were 10 tables when there should have been 1 with an extra field to determine Location.

Added Location field to each table and then a UNION query

Select *
From Table 1
UNION
Select *
From Table 2

etc

This then gave me a single record set that I could query to extract the data required.

Maybe you could sort of re-arrange your data using a UNION and then query the result for your answers
Apart from that how fast can you write queries

len B

 

[Kila]

Len, will the Union query work to join fields in the same table? There is only one table, but each question may have, say, 5 fields...one for each of the 5 answers.

Uncle. Excel is a good idea, but how can I combine the many fields (above) into one? Is there an easy way to do that?

OR, for either of you, is there an easy way to normalize the data I already have, using some kind of query or Excel?

Thanks for your suggestions!

 

[Len Boorman]

Len, will the Union query work to join fields in the same table? There is only one table, but each question may have, say, 5 fields...one for each of the 5 answers.

The rules on a UNION query are quite specific

1) The field names will be those specified in the first Select Statement
2) There must be the same number of columns in each Select Statement
3) Data Types must be compatible. You cannot have acolumn that for one field in Date/Time and anothert field Text. Common sense really.

Yes you can work on one table to the best of my knowledge

Advice.
Do one select statement and get that right.
Then do one UNION and get that right.
Save the Query
Now move on adding one UNION at a time. Use alias field if you need to pad to ensure equal number of columns in every select statement.
like
Pad1:A or something you can easily omit with criteria in your analysis queries that will take the UNION as source.

There is a limit on the number of Selects in a UNION. Think it is about 20 may be more or less. Cannot remember exactly. That's why you add it in parts as well as making sure it works.

If you hit the limit then do 2 UNION queries and then UNION the UNIONS

is there an easy way to normalize the data I already have,

Yeah Get somebody else to do it. Thats the best I could offer


Len

 

[uncle-lai]

Hi Kila,

Could you show us more specificly the structure of your table? As Lan said it's a bit difficult to suggest a solution without looking at the data.

Peter