help with update table

[baonks@gmail.com]

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!KREDI T;

but it update the last one

thank you in advance

regards
budi sentosa

[David S via AccessMonster.com]

Re: help with update table

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200509/1

[baonks@gmail.com]

Re: help with update table

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

[David S via AccessMonster.com]

Re: help with update table

The total is easy, you just use a GROUP BY eg. Table2Total
SELECT Table2.K_POS, Sum(Table2.DEBE T) AS SumOfDEBET, Sum(Table2.KRED IT) 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

[David S via AccessMonster.com]

Re: help with update table

Oops, pasted the DELETE again - the APPEND query is actually:
INSERT INTO Table1 ( K_POS, SALDO_A_D, SALDO_A_K )
SELECT Table2Total.K_P OS, Table2Total.Sum OfDEBET, Table2Total.Sum OfKREDIT
FROM Table2Total;


--
Message posted via http://www.accessmonster.com

[baonks@gmail.com]

Re: help with update table

thank you david,

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

regards

baonks

[David S via AccessMonster.com]

Re: help with update table

>i manage it by vbscript (cause i use asp clasic)[color=blue]
>but it is posible, just use UPDATE command ?[/color]

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

[Rick Brandt]

Re: help with update table

David S via AccessMonster.c om wrote:[color=blue][color=green]
> > i manage it by vbscript (cause i use asp clasic)
> > but it is posible, just use UPDATE command ?[/color]
>
> 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.[/color]

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

[David S via AccessMonster.com]

Re: help with update table

>Actually, if used with an outer join an UPDATE query can add new records quite[color=blue]
>easily.[/color]

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200510/1

[Rick Brandt]

Re: help with update table

David S via AccessMonster.c om wrote:[color=blue][color=green]
> > Actually, if used with an outer join an UPDATE query can add new
> > records quite easily.[/color]
>
> 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?[/color]

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