Input Criteria into Union query from a Form

[Scottyk537]

Based on information from a earlier thread.... I created a Union query that pulls information from multiple tables and fields.


SELECT AG_B_R1 as Num FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R2 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R3 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R4 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R1 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R2 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R3 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R4 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R1 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R2 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R3 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL SELECT AG_e_R4 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll];


And then I created another query to get the STDEV of the above query


SELECT StDev([Num]) AS StDev
FROM Q_cals_ag_bme_STDEV_Union;

The result will be on a subform on my main page. How do I get my form to input the [roll] automatically and requery the subform, showing my result.
Thanks

[Scottyk537]

Play around long enough and your bound to get it.
Here's what I did. Union query:

SELECT Rollnmbr as RN, AG_B_R1 as Num FROM dbo_ADC_Ag_B_Res
UNION ALL
SELECT Rollnmbr, AG_B_R2 FROM dbo_ADC_Ag_B_Res
UNION ALL
SELECT Rollnmbr, AG_B_R3 FROM dbo_ADC_Ag_B_Res
UNION ALL
SELECT Rollnmbr, AG_B_R4 FROM dbo_ADC_Ag_B_Res
UNION ALL
SELECT Rollnmbr, AG_m_R1 FROM dbo_ADC_Ag_m_Res
UNION ALL
SELECT Rollnmbr, AG_m_R2 FROM dbo_ADC_Ag_m_Res
UNION ALL
SELECT Rollnmbr, AG_m_R3 FROM dbo_ADC_Ag_m_Res
UNION ALL
SELECT Rollnmbr, AG_m_R4 FROM dbo_ADC_Ag_m_Res
UNION ALL
SELECT Rollnmbr, AG_e_R1 FROM dbo_ADC_Ag_e_Res
UNION ALL
SELECT Rollnmbr, AG_e_R2 FROM dbo_ADC_Ag_e_Res
UNION ALL
SELECT Rollnmbr, AG_e_R3 FROM dbo_ADC_Ag_e_Res
UNION ALL SELECT Rollnmbr, AG_e_R4 FROM dbo_ADC_Ag_e_Res;

Second Query:

SELECT Q_cals_ag_bme_STDEV_Union.RN, StDev(Q_cals_ag_bme_STDEV_Union.Num) AS StDev
FROM Q_cals_ag_bme_STDEV_Union
GROUP BY Q_cals_ag_bme_STDEV_Union.RN;