Update query using a query???

[pdbowling]

Good morning all,

I have a query that returns

DeptCode TotalRepairs
IA702 500
MA702 700
etc.
query name is getTotals


I need to update an existing table with this data depending on the month chosen from a drop down box.

Table
DeptCode January February March etc
IA702 350 400 0
MA702 100 250 0

Table name is yearCostRecord

I'll probably use DoCmd.RunSQL to execute it once it's built so I can capture the month variable. I just don't know how to write the query. This was my try but it fails.

UPDATE yearCostRecord SET yearCostRecord.March = 500
WHERE (((yearCostRecord.DeptCode)=[getTotals].[DeptCode]));

I was just using a generic month and total value to test....

Any suggestions on how to correctly run this update??

Thanks everyone.
PB

 

[RV]

>This was my try but it fails. <

What do you mean by "it fails"
No updates, error messages,....

RV

 

[pdbowling]

ah, I'm being too vague...

It gives me a dialog box asking me for

getTotals.DeptCode

instead of using the data in getTotals to fill in the value for that month in yearCostRecord.

PB

 

[pdbowling]

More

This update query failing isn't really important since I was just trying to get started with the real one and didn't even get that far.

I need an update query that will use the values in
getTotals to update yearCostRecord. If this is impractical I apologize for my over zealous desire for effieciency....

I can always output getTotals to a spreadsheet and step through the rows until .value = "" and do a seperate update query for each row.

I hope I am not wasting your time.
PB

 

[Rich]

It sounds as though you're trying to store the results of a Totals query, is that correct?

 

[pdbowling]

Yes, exactly. I get the totals out of a query and need to store them in a table that already exists. This will be done monthly in the fiscal year. Like I said, if the update query is unreasonable, I can use alternate methods...
Thanks pb

 

[Rich]

Storing calculated fields in a table is not recommended generally. Since you can return these totals via a query is it necessary?
Are you dealing with large recordsets?

 

[RV]

Try this:

UPDATE yearCostRecord SET yearCostRecord.March =
(
SELECT TotalRepairs
FROM getTotals
WHERE yearCostRecord.DeptCode)[getTotals].[DeptCode]
);

RV

 

[pdbowling]

Yeah

I agree that storing calculations is not a grand idea but alas, most of what I do is based on what I am told. Also, the data the calculations are pulled from is discarded after the calculations so any future queries aren't possible. Sooo, I store the appropriate totals in my little running record to make everyone smile (and to keep my job LOL)
PB

Oh and I'll give the above query a go and see what happens.