Query to update finical amounts but return all data?

[Michael_Red]

Hi
I have a query that reports on 3990 records. The report runs fine, however it has come to light that we have to make 50 adjustments to the finical fields where the wrong amounts are shown the the database. We cannot update the database unfortunately so I'm attempting to adjust the data in the query. I have a table that has the client ref and then shows what adjustment is to be made, so for example client ref 12345 has an adjustment of -200.
So I link my query to the table of adjustments, have a join that returns all data from the query. I then have a field that add the amount form the query and then the adjustment, so the amount shown is correct. This works however the field only shows the adjustment not all the amounts. I believe this is because the records that are not present in the adjustment table cant find a matching record and just returning blank. Is there a way around this at all? Is there a better way within the query to make adjustments to data? I have looked as using an update query, the problem with that is the amounts could change, the adjustment is always the same.

Thanks
Michael

[CJ_London]

use a left join between your original query and your adjustment table - this will show all the records in your original query plus the adjustment table when a record exists.

With regards your adjustment, if it is a replacement figure the use

Corrected:nz(tblAdjustment.adjvalue,qryOriginal.or igValue)

if it is an adjustement to derive a new figure use

Corrected:qryOriginal.origValue+nz(tblAdjustment.a djvalue,0)

change all names to suit your real table and field names

[Michael_Red]

Hi
Thank you, worked as expected, thanks very much. I used the correct option.