Using Query to De-normalize Data!

[DNRTech]

Hi, I know this sounds bad, but I need to de-normalize my data in a query.

I have a results table that is related to a location table and a date table. I want a query that makes a table that divides the results based on analyte, a field in the results table.

I thought it would be as easy as building a query with:

1. date in column one
2. location in column two
3. results in remaining columns with different analyte criteria in each column



I get correct results when I query one analyte. Any more than one and I get no records. All the fields are present but have not records.

I should mention that not every criteria has data for each date, and I think my problem is that. When the query attempts to call a record that has missing data for one of the two analytes it shows no records at all.

I've tried Is Null in the "or" row, and using the Nz() function in the field row, of the query builder.

Any ideas? Thanks!

[kennejd]

I'm a little fuzzy on your problem description...perhaps you could post your SQL? There could be a problem with your joins.

[DNRTech]

SELECT dbo_Activity.Date, dbo_Location.LocationIdentifier, dbo_Result.Result AS Con, dbo_Result.Result AS Turb
FROM dbo_Location RIGHT JOIN (dbo_Activity LEFT JOIN dbo_Result ON dbo_Activity.ActivityIdentifier = dbo_Result.ActivityIdentifier) ON dbo_Location.LocationIdentifier = dbo_Activity.LocationIdentifier
WHERE ((([dbo_Result]![Analyte])="Turbidity" And ([dbo_Result]![Analyte])="Conductivity"));

In the end I want this to include several analytes from the Results column, but I'm starting with just two.

Thanks again.

[kennejd]

Oh, I see the problem....it's your condition. That should be an 'Or'. An Analyte can only be a single value. Another thing you can do is use an in condition in the criteria:

in("Turbidity","Conductivity")


WHERE ((([dbo_Result]![Analyte])="Turbidity" And ([dbo_Result]![Analyte])="Conductivity"));

[DNRTech]

yes, I do get results when I do this, but what it seems to do is populate each result column with both conductivity and turbidity values. What I am looking for is each result column to be populated with Turbidity and Conductivity respectively.

What I am trying to do is query data from a single field in a table, in my case Results, into separate columns based on various parameters, in my case analytes.

[kennejd]

I think I know what you want. For your result columns, use an iif function:

, dbo_Result.Result AS Con becomes iif(Analyte = 'Conductivity', Result,0) as Con
, dbo_Result.Result AS Turb becomes iif(Analyte = 'Turbidity',Result,0) as Turb

This could be a bit time-consuming and difficult to maintain if you have many categories....have you thought of a crosstab query?

[DNRTech]

No, I am unfamiliar with crosstab queries, but I will look at them and see if they are right for this case. The reason I want to perform this query is to allow users to export a table of all the results to use in other calculation programs such as EXCEL or Aquarius, although I understand that SPSS will want all the results in the same column (thank goodness).

I'll work with the iif function and see if I can get it to split the column. I'll let you know how it goes!

[DNRTech]

Yep that did it! The iif function worked!

Thanks again!

[DNRTech]

Nearly a year later I have an update to this problem. After developing a work around for this problem with R's reshape library, a friend showed me pivot tables. Pivot tables are exactly what I needed to, as I put, "De-normalize" the data. For anyone thinking about doing this kind or rearrangement this would be your best bet I think. Also, R's cast library is great if you are using R and want to rearrange data on the fly.