By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,619 Members | 2,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,619 IT Pros & Developers. It's quick & easy.

help with update table

P: n/a
hello all

here is my problem:

I have 2 table
1:
K_POS SALDO_A_D SALDO_A_K
11100 105 5
11200 5 105

2:
JurnalID K_POS DEBET KREDIT
GJ00000001 11100 101 0
GJ00000001 11200 0 101
GJ00000002 11100 102 0
GJ00000002 11200 0 102

here is my problem,
i want to update table 1, with the total query from table 2, any body
can help?

so far, i got this
UPDATE NERACA INNER JOIN DETJURNAL ON NERACA.KD_POS = DETJURNAL.K_POS
SET NERACA.SALDO_A_D = DETJURNAL!DEBET, NERACA.SALDO_A_K =
DETJURNAL!KREDIT;

but it update the last one

thank you in advance

regards
budi sentosa

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Sounds like the sort of thing you can use an Append query in. But what do you
want to happen to the existing totals in Table 1? Do you want to overwrite
the values in that table, do you want to add the Total from Table 2 to the
existing total, or what?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1
Nov 13 '05 #2

P: n/a
i want to make total in table2, per K_POS, & update it to table 1
i want to update the value in table1, field SALDO_A_D & SALDO_A_K,by
the total value in table2

thank you

Nov 13 '05 #3

P: n/a
The total is easy, you just use a GROUP BY eg. Table2Total
SELECT Table2.K_POS, Sum(Table2.DEBET) AS SumOfDEBET, Sum(Table2.KREDIT) AS
SumOfKREDIT
FROM Table2
GROUP BY Table2.K_POS;

Updating it into Table1 is harder. You could write some VBA to open the query
above and read it record by record, getting the corresponding totals out of
Table2Total and updating the fields. I'm not very good with VBA - you'll need
to ask again, probably - but you could do the same thing using two queries,
one to delete the records that you want to update:
DELETE *
FROM Table1
WHERE K_POS in (SELECT K_POS FROM Table2Total);

and then another to append the records again:
DELETE *
FROM Table1
WHERE K_POS in (SELECT K_POS FROM Table2Total);
--
Message posted via http://www.accessmonster.com
Nov 13 '05 #4

P: n/a
Oops, pasted the DELETE again - the APPEND query is actually:
INSERT INTO Table1 ( K_POS, SALDO_A_D, SALDO_A_K )
SELECT Table2Total.K_POS, Table2Total.SumOfDEBET, Table2Total.SumOfKREDIT
FROM Table2Total;
--
Message posted via http://www.accessmonster.com
Nov 13 '05 #5

P: n/a
thank you david,

i manage it by vbscript (cause i use asp clasic)
but it is posible, just use UPDATE command ?

regards

baonks

Nov 13 '05 #6

P: n/a
>i manage it by vbscript (cause i use asp clasic)
but it is posible, just use UPDATE command ?


Not if you're referring to the UPDATE SQL Statement - unless you can always
be sure that there will be a record for in Table1 for every valid combination
in Table2. The UPDATE statement can't add records to the table, it can only
update existing records.
--
Message posted via http://www.accessmonster.com
Nov 13 '05 #7

P: n/a
David S via AccessMonster.com wrote:
i manage it by vbscript (cause i use asp clasic)
but it is posible, just use UPDATE command ?


Not if you're referring to the UPDATE SQL Statement - unless you can
always be sure that there will be a record for in Table1 for every
valid combination in Table2. The UPDATE statement can't add records
to the table, it can only update existing records.


Actually, if used with an outer join an UPDATE query can add new records quite
easily.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #8

P: n/a
>Actually, if used with an outer join an UPDATE query can add new records quite
easily.


It can? Whenever I've tried this in the past, I seem to get the "This
recordset is not updateable" error. Can you post some SQL using the tables
above to demonstrate how this might be done?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1
Nov 13 '05 #9

P: n/a
David S via AccessMonster.com wrote:
Actually, if used with an outer join an UPDATE query can add new
records quite easily.


It can? Whenever I've tried this in the past, I seem to get the "This
recordset is not updateable" error. Can you post some SQL using the
tables above to demonstrate how this might be done?


I agree that using a totals query inside an update or append query gives that
error. I was merely responding to the generic statement made that "The UPDATE
statement can't add records to the table, it can only update existing records".
That statement is incorrect because (In Access at least) an UPDATE query can in
fact also append new records to the table being updated.

In a test where Table1 contains five rows and Table2 contains 10 rows the
following UPDATE query results in Table1 having 10 rows in it after being
executed.
UPDATE Table1 RIGHT JOIN Table2
ON Table1.Field1 = Table2.Field1
SET Table1.Field1 = Table2.Field1,
Table1.Field2 = Table2.Field2

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.