Problem with update query 1

[TerryEA]

Hi all - Help please.

I am simply trying to store totals of transactions held in a table as child records, in the header records of another table which is essentially the parent. The UPDATE query I've made seems to work fine for some of the header records but not all and I don't know why it leaves some out.

Table1 is tblNOMINALS (one)
Table2 is tblAUDITTRAIL (many)

The link field is [NOMCODE] in both, ie [tblNOMINALS.NOMCODE] and [tblAUDITTRAIL.NOMCODE]


This is what I'm trying to do:

There is a field (named NOMDEBIT) in tblNOMINALS which needs to show the total of transaction values in a field (named also NOMDEBIT) in tblAUDITTRAIL, where there is a one to many relationship between tblNOMINALS AND tblAUTDITTRAIL.

My update query has the following in the update field of [tblNOMINALS.NOMDEBIT]:

[tblNOMINALS.NOMDEBIT]=[tblNOMINALS.NOMDEBIT]+[tblAUDITTRAIL.NOMDEBIT]

So I am trying to simply add up the records. As I say it works for some of the records in tblNOMINALS but not all and there's no apparent reason for leaving any out. I haven't done any selections at all. Incidentally, after running the query, header values that were previously "0.00" seemed to have been replaced with nulls, only in the records that didn't work.

I've run out of ideas. Am I approaching this the right way? I would appreciate some help.

Terry

 

[PHV]

What is the actual SQL code of your query ?
For me you should try this (SQL code);

UPDATE tblNOMINALS
SET NOMDEBIT = Nz(DSum("NOMDEBIT","tblAUDITTRAIL","NOMCODE='" & [NOMCODE] & "'"),0)

If NOMCODE is defined as numeric then get rid of the single quotes.

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

 

[TerryEA]

PHV

I put your code in a got a message saying the field names could refer to more than one table, so I modified it to the following:

"SET NOMDEBIT"=Nz(DSum("tblAUDITTRAIL.NOMDEBIT","tblAUDITTRAIL","tblAUDITTRAIL.NOMCODE='" & [tblAUDITTRAIL.NOMCODE] & "'"),0)

Have I got this the rght way?

It runs, and this time it keeps the previous values of "0.00", but it doesn't add up the records.

Terry

 

[TerryEA]

Still can't get this working - can anyone help?

Terry

 

[PHV]

What is your actual SQL code ?

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

 

[TerryEA]

Hi PH

AS I said in the original post, this was the code I put into the update field of the update query:

[tblNOMINALS.NOMDEBIT]=[tblNOMINALS.NOMDEBIT]+[tblAUDITTRAIL.NOMDEBIT]

which seemed to work for 95% of the records in tblNOMINALS I was trying to update. For somoe reason it simply left out selected ones. I then tried your code but got a message saying that [NOMCODE] could refer to fields in more than one table. I played about with that but couldn't get it to work. I then read that Access can't update records on the "one" side of relationships, which is what I am trying to do, only the many side, so am I approaching this the right way? or is there a simpler way.

Terry

 

[PHV]

Have you tried my suggested [!]SQL code[/!] on 2 Jun 07 11:54 ?
i.e. not in the query grid but the SQL view pane.

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

 

[TerryEA]

Hi PH

I'll certainly give it another go and get back to you. It'll be later, since I've got to be somewhere now. btw, is it your impression that Access can't update the "one" side of the relationship, in the way I'm trying to do?

Terry

 

[TerryEA]

Hi PH

Sorry for the delay in responding. Had to go away on business but now that I'm back I've had the chance to try your code again. As you suggested I put the code into the sql view pane and it worked perfectly, which I'm really pleased about because I was determined to be able to store totals of child records in parent records. It's perfect for my requirements.

I thinkg I've given myself some headaches using identical field names in two tables, in terms of referencing. So much to learn.

Thanks very much for your patience and the solution to the problem.

Terry