Problem with Update Query?

[emarchant]

I have an update query in my macro to update a field in my form. The macro runs a Make Table query, so that the data in my query is stored in a temporary table, then runs the update query to put the temp table data into my form/permanent table. Here is the trouble:

When my update query runs, it returns zeroes..... the data is all there, it should be updating to percentages... I am not sure how to troubleshoot!

Here is the code:



UPDATE tblExecutionTracking LEFT JOIN tblPercentage ON (tblExecutionTracking.[Entry #] = tblPercentage.[Entry #]) AND (tblExecutionTracking.[BU / Project] = tblPercentage.[BU / Project]) SET tblExecutionTracking.[BU %] = [tblPercentage]![SumOfRatio];

The data matches... but it updates everything to zero!?!

[weekend00]

UPDATE tblExecutionTracking LEFT JOIN tblPercentage ON (tblExecutionTracking.[Entry #] = tblPercentage.[Entry #]) AND (tblExecutionTracking.[BU / Project] = tblPercentage.[BU / Project]) SET tblExecutionTracking.[BU %] = [tblPercentage]![SumOfRatio];

can you try to change the "!" to "."?

[emarchant]

No, that did not work... it is so strange. The data is in my temp table, all the field types match... but it updates everything to a zero!

[weekend00]

I don't think they match.

try to change left join to inner join, then you will know how many records match.

[emarchant]

Okay, I did that... I am getting the same result. I am guessing that means that something does not match... but I don't understand what! I have looked at all the fields in both of the temp table and the table I am trying to update... everything looks the same...... what else am I missing?

[weekend00]

change to a select query then you will see:

select [tblPercentage].[SumOfRatio], tblExecutionTracking.* inner JOIN tblPercentage ON (tblExecutionTracking.[Entry #] = tblPercentage.[Entry #]) AND (tblExecutionTracking.[BU / Project] = tblPercentage.[BU / Project])

[emarchant]

SELECT tblExecutionTracking.[Entry #], tblExecutionTracking.[BU / Project]
FROM tblExecutionTracking INNER JOIN tblPercentage ON (tblExecutionTracking.[BU / Project] = tblPercentage.[BU / Project]) AND (tblExecutionTracking.[Entry #] = tblPercentage.[Entry #]);

Returned all expected data; everything matches.

??

[weekend00]

You must have looked at the wrong table