function applied to multiple fields

[shelspa]

My form is based on a query that returns about twenty records. It has 5 numeric fields upon which I want to use StDevP. This of course applies to all five fields in all records to give the standard deviation of all values. In thread702-475136 I was able to use a temp table to do this for a record but not multiple records. Ideas?

 

[shelspa]

Not form, "report" sorry.

 

[dhookom]

Attempting to calculate across fields rather than across records suggests an un-normalized table structure. To normalize a structure that you can't change, use a union query.

SELECT ID, Num1 as Num
FROM tblTooWide
UNION ALL
SELECT ID, Num2
FROM tblTooWide
UNION ALL
SELECT ID, Num3
FROM tblTooWide
UNION ALL
--etc--
FROM tblTooWide;

You can then use standard sql to calculate a standard deviation of the Num field in the union query.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shelspa]

So I used the following to establish a union query but I must have something wrong:

SELECT ID, Site_1_postThickness as Num
FROM qryTPolyLotReport
UNION ALL
SELECT ID, Site_2_postThickness
FROM qryTPolyLotReport
UNION ALL
SELECT ID, Site_3_postThickness
FROM qryTPolyLotReport
UNION ALL
SELECT ID, Site_4_postThickness
FROM qryTPolyLotReport
UNION ALL
SELECT ID, Site_5_postThickness
FROM qryTPolyLotReport
UNION ALL
FROM qryTPolyLotReport


Should I use the table instead or does it matter?

 

[dhookom]

Stop after the final select statement:

SELECT ID, Site_1_postThickness as Num
FROM qryTPolyLotReport
UNION ALL
SELECT ID, Site_2_postThickness
FROM qryTPolyLotReport
UNION ALL
SELECT ID, Site_3_postThickness
FROM qryTPolyLotReport
UNION ALL
SELECT ID, Site_4_postThickness
FROM qryTPolyLotReport
UNION ALL
SELECT ID, Site_5_postThickness
FROM qryTPolyLotReport

This assumes you have a field named ID in your table.

Next time you post because you are having a problem, please include an error message or your results.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shelspa]

Thanks, this puts me well on my way.