tables merge or math

[Guest]

hi,

cleaning up a somewhat messy duplication; have an issue where table1 has
records with values and table2 does also. I need to subtract the values of
table 2 from table 1

can either create a new table 3 or update table 1...have lots of leeway in
method...just too many records to do manually....

but while there is correct alignment between IDs....table 2 doesn't have a
value for all records...so it looks like this:

Table 1 Table2
ID Qty ID Qty
A 5 A 1
B 3 C 1
C 4

and need to result:
ID Qty
A 4
B 3
C 3

thought it would be no problem but have found myself going around in circles
for awhile so need someone to kick me and point out what I'm missing....have
kind of pinged around with update query, sql statement, and such but kind of
not clicking for me....would welcome comments....

 

[John Vinson]

Table 1 Table2
ID Qty ID Qty
A 5 A 1
B 3 C 1
C 4

and need to result:
ID Qty
A 4
B 3
C 3

Well, MAKE A BACKUP of your database first - this may be risky. Then
create a Query joining Table1 to Table2 by ID. This will leave your
record B untouched, since it has no counterpart in Table2.

Change the query to an Update query, and update [Table1].[Qty] to

[Table2].[Qty]

using the actual table and field names. The square brackets ARE
ESSENTIAL (or it will try to update the field to the text string
"Table2.Qty"... which you DON'T want!).

Run the query by clicking the ! icon and you should then have the data
you want. Don't run it twice or you'll subtract twice (you *did* check
your backup... right?)


John W. Vinson[MVP]

 

[John Spencer]

Pardon me, but I think the poster wanted to subtract quantities in table2
from the quantities in Table1 where the ID fields matched.

This type of query should do it, but as John Vinson noted - run it only
once.
UPDATE Table1 INNER JOIN Table2
On Table1.ID = Table2.ID
SET Table1.Qty = [Table1].[Qty] - [Table2].[Qty]

If you wanted to just see the results, but make no permanent change
SELECT Table1.ID, Table1.Qty, Table2.Qty
, Table1.qty - Nz(Table2.qty,0) as ChangedQty
FROM Table1.ID LEFT JOIN Table2.ID

If you want to create the changed qty in a new field in table1, then
UPDATE Table1 LEFT JOIN Table2
On Table1.ID = Table2.ID
SET Table1.[SomeNewField] = [Table1].[Qty] - Nz([Table2].[qty],0)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Table 1 Table2
ID Qty ID Qty
A 5 A 1
B 3 C 1
C 4

and need to result:
ID Qty
A 4
B 3
C 3

Well, MAKE A BACKUP of your database first - this may be risky. Then
create a Query joining Table1 to Table2 by ID. This will leave your
record B untouched, since it has no counterpart in Table2.

Change the query to an Update query, and update [Table1].[Qty] to

[Table2].[Qty]

using the actual table and field names. The square brackets ARE
ESSENTIAL (or it will try to update the field to the text string
"Table2.Qty"... which you DON'T want!).

Run the query by clicking the ! icon and you should then have the data
you want. Don't run it twice or you'll subtract twice (you *did* check
your backup... right?)


John W. Vinson[MVP]

 

[Guest]

thanks both John & John...the sql code suggestion worked great. I have the
book 'SQL for Mere Mortals' but still sometimes have trouble finding the
correct solution....
--
NTC

Pardon me, but I think the poster wanted to subtract quantities in table2
from the quantities in Table1 where the ID fields matched.

This type of query should do it, but as John Vinson noted - run it only
once.
UPDATE Table1 INNER JOIN Table2
On Table1.ID = Table2.ID
SET Table1.Qty = [Table1].[Qty] - [Table2].[Qty]

If you wanted to just see the results, but make no permanent change
SELECT Table1.ID, Table1.Qty, Table2.Qty
, Table1.qty - Nz(Table2.qty,0) as ChangedQty
FROM Table1.ID LEFT JOIN Table2.ID

If you want to create the changed qty in a new field in table1, then
UPDATE Table1 LEFT JOIN Table2
On Table1.ID = Table2.ID
SET Table1.[SomeNewField] = [Table1].[Qty] - Nz([Table2].[qty],0)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Table 1 Table2
ID Qty ID Qty
A 5 A 1
B 3 C 1
C 4

and need to result:
ID Qty
A 4
B 3
C 3

Well, MAKE A BACKUP of your database first - this may be risky. Then
create a Query joining Table1 to Table2 by ID. This will leave your
record B untouched, since it has no counterpart in Table2.

Change the query to an Update query, and update [Table1].[Qty] to

[Table2].[Qty]

using the actual table and field names. The square brackets ARE
ESSENTIAL (or it will try to update the field to the text string
"Table2.Qty"... which you DON'T want!).

Run the query by clicking the ! icon and you should then have the data
you want. Don't run it twice or you'll subtract twice (you *did* check
your backup... right?)


John W. Vinson[MVP]