Update query (1 Viewer)

[johnb19]

Confused...trying to create an update query to Budget table using the Access Design View:
Field: PctSls (in tblBudget) Update to: [Expense] / [Sales]. The update query always returns 0. However, if I create a Select query using the same calculation, the correct results is displayed. PctSls is defined in the Budget table.

I haven't worked with Access (2003) for several years but this seems too simple to be causing me such frustration. (Was only a casual user even then).

Thanks in advance for any advice.

 

[jdraw]

Please show the SQL for the SElect that works. and the Update query that's giving the problem.

 

[johnb19]

Hope this helps...Thanks for the reply.

SELECT QUERY
SELECT tblBudget.GBYR, tblBudget.GBLOC, tblBudget.[GBGL#], tblBudget.GLDESC, tblBudget.TOTAL, tblLoc.YtdSls, [TOTAL]/[YtdSls] AS Pct
FROM tblLoc INNER JOIN tblBudget ON tblLoc.Loc = tblBudget.GBLOC;

UPDATE QRY

UPDATE tblLoc INNER JOIN QS36F_BUDGET ON tblLoc.Loc = QS36F_BUDGET.GBLOC SET QS36F_BUDGET.SlsPct = [TOTAL]/[YtdSls]
WHERE (((QS36F_BUDGET.[GBGL#])>3001000));

 

[jdraw]

What is QS36F_BUDGET in your update query??

 

[johnb19]

A file imported from an IBM i Series (contains Sales & Expense data).

 

[jdraw]

Samples I have found and used with an Update query with Inner Join(s)
always have the Table to be updated identified first. I don't know that that is a
required format...??? Nope, I found an example with table to update after the inner join keyword.

My sample

UPDATE AnimalCapture INNER JOIN AnimalLocs
 ON AnimalCapture.AnimalId=AnimalLocs.AnimalId 
SET AnimalLocs.CaptureDateX = AnimalCapture!CaptureDate;

You don't get any error messages, right??

 

[johnb19]

My wife (Oracle DBA) said the statement looked unusual to her as well.
To add a little more confusion: Make table and Append table queries work fine...just didn't want to give in.