Save Union Query results to table fields

[uncleG]

Using Access 2003, WinXP Split FE, BE in Multi user enviorment.
I am currently trying to save the results of a union query to a table [tblBomMaterials] that shares a field named ItemNo (LI).
This info will be used at the time this Requisition is prepared and processed and then replaced with fresh data upon the next occurance.
Currently there are less then 400 records involved but in time there will be a few thousand so I felt a DLookup may not be the best choice here.
I plan to fire the action from a button on the form where the actual estimate takes place, thus the user can determine when to do so as this process may take place over a week or two.
Thus far it appears the Union Query creates a lock on the data and does not permit writing to the table, or is it me?

My Union Query: qryFalloutRates

SELECT DISTINCTROW qryProductionYield.ItemNo, Avg(qryProductionYield.SR) AS [Avg MS SR], Count(*) AS MsRuns
FROM qryProductionYield
GROUP BY qryProductionYield.ItemNo

Union All SELECT DISTINCTROW qryProductionYieldAssem.ItemNo, Avg(qryProductionYieldAssem.SR) AS [Avg Assem SR], Count(*) AS AsRuns
FROM qryProductionYieldAssem
GROUP BY qryProductionYieldAssem.ItemNo

UNION ALL SELECT DISTINCTROW qryProductionYieldTesting.ItemNo, Avg(qryProductionYieldTesting.SR) AS [Avg Testing SR], Count(*) AS TeRuns
FROM qryProductionYieldTesting
GROUP BY qryProductionYieldTesting.ItemNo
ORDER BY qryProductionYield.ItemNo;
[\code]

qryFalloutRates produces 3 fields:
ItemNo:      Long Integer
Avg MS SR:   Double
MSRuns:      Long Integer

I would like to write [qryFalloutRates].[Avg MS SR] into tblBomMaterials.FalloutRate and [qryFalloutRates].[MsRuns] into tblBomMaterials.Runs WHERE [qryFalloutRates].ItemNo = tblBomMaterials.ItemNo.
 
Thanks,
UncleG

 

[PHV]

Something like this (SQL code) ?

UPDATE tblBomMaterials INNER JOIN qryFalloutRates ON tblBomMaterials.ItemNo = qryFalloutRates.ItemNo
SET tblBomMaterials.FalloutRate = qryFalloutRates.[Avg MS SR], tblBomMaterials.Runs = qryFalloutRates.MsRuns

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[uncleG]

Hi PHV,
Thanks for your response.
When I tried running this from the query panel, it produced an error. "Operation must use an updateable query". And "The number of columns in the union don't match."I am suspecting I may have to step back behind the union query and run three updates. Any suggestions greatly appreciated.

 

[uncleG]

Got it. Followed the advice found at

http://www.fmsinc.com/microsoftaccess/query/non-updateable/index.html

In essence the work around was to put my union query into an make table query.
Run the make table query, which adds a table to the front end.
Next run the update query with my fields from the new temp table to update these fields within my original table in the Back End.
Next time the data is required for a new requisition simply run the make table query and the update query.

 

[Andrzejek]

Just FYI...
If you have UNIONs, the field (column) names for your outcome will be the field names from your first SELECT only, so it does not matter if you use aliases (or not) or use different aliases for your fields in the second or third Select in your Union (... AS [Avg MS SR], ... AS MsRuns)

Have fun.

---- Andy