create a new row for each table column in query (1 Viewer)

[Eyor]

I have a table which stores multiple materials in different columns, and the analysis results for each material in the associated cell. I need to create a query which will display a new row for each material and result with all the appropriate associations (location, date, time, etc.) Is there a way to do this in a single query? Do I have to create a separate query for each material?

Ultimately, I need to create an excel file which displays each material, the analysis result for that material, and the associated site, date/time values in a new row for each material.

Thanks for any help.

 

[meloncolly]

select commonfield1, commonfield2, analysisresultfield1
from yourtable
union
select commonfield1, commonfield2, analysisresultfield2
from yourtable
union
select commonfield1, commonfield2, analysisresultfieldx
from yourtable

should do what you need

 

[neileg]

Your problem stems from the wrong design. Your table is not normalised. In fact, the structure you want is the one you are trying to generate with your query. That's how the data should be stored anyway.

 

[Eyor]

Neil,

Thanks for the comment. I know this isn't normalized. The purpose of this query is to get the data into a normalized form and so it can be loaded into a normalized system.

Meloncolly,

I'm not sure I understand what you're suggesting. Are you saying that I should create a union between mytable and itself? Seems a little unusual but, I'll give it a try if I'm understanding correctly.

 

[Eyor]

Ok, I can't get meloncolly's suggestion to work. Possibly because the analysis results are cells in the table columns (each column is a different material) and I don't know how to just return the name of the column in one cell and the result of the column in another all in the same row.

Can anyone think of another way to do this? Or some other means by which I can normalize this table (possibly create a new table via query?)

 

[KeithG]

You are going to have to write a seperate SQL statement for each column contain two fields in each SQL statememnt. The first field will be the name of the material and the second field would be the field from the table. For example lets say you have a field named Metal which is of a boolean datatype and a field name Rubber of a boolean datatype. You SQL statement for this field would be

Select "MetalMaterial" as FieldDescription, Metal From [TableName]
Union
Select "RubberMaterial as FieldDiscription, Rubber From [TableName]

 

[Eyor]

Thanks Keith. I was hoping to avoid this since there are 60+ materials but, looks like this is the only way.

 

[Eyor]

Just giving this a little bump. I'm very much hoping that there is a more clever way to do this.

 

[KeithG]

Just giving this a little bump. I'm very much hoping that there is a more clever way to do this.

Normalize your tables

 

[Eyor]

Is there a way to normalize the tables and keep the data intact? The point here is to normalize the tables and get the data into a normalized form. If there is a good way to do this I'd love to hear it.

Looking over some threads about normalizing data, it looks as if this is non-trivial and can take some time. I suppose the question is; what is easier, to normalize this table directly or to create queries export to excel, rearange the data and then import this data into a normalized table.

Thanks

 

[Pat Hartman]

You can use the union query to copy the data from the spreadsheet like table to a new normalized table. It is unlikely that the union query can handle 60 selects at once so you will probably need to break up the process into 3 union queries that do 20 fields each.

I'm very much hoping that there is a more clever way to do this.

Without looking at your exact structure I can't say for sure but you might be able to port the data to Excel and use Excel's transpose feature to normalize the table for you.

 

[Eyor]

Pat,

You're awesome! Transposing in excell worked (with a few iterations due to the size of the table).

Thank you!