Action query?

[Guest]

Hi
I need to make a query that will either delete or update a table. I'm not sure what is the best

Example
I have a table (T1) with part number colum and quantity colum. Each day I import an excel spreadsheet to a new table (T2) with part numbers and quantities. I need a query or something that will look at both tables and update T1 from the information in T2.

So if T1 part number 23 has a quantity of 19 and T2 part number 23 has a quantity of 5 then it should subtracts a quantity of 5 from part 23 on T1. So T1 part number 23 would have 14 now

Unless there is some other way for updating the quantities in T1 from an excel spreadsheet

Thanks
Be

 

[Nikos Yannacopoulos]

Ben,

You don't need to import the spreadsheet in a local Access table T2, you can
work just as well T2 as a linked spreadsheet. In either case, what you need
is an update query with T1 and T2 (whichever form) joined on part number,
and T1's quantity field set to update to [T1].[Quantity] - [T2].[Quantity]
(change the names as appropriate).

HTH,
Nikos

Hi,
I need to make a query that will either delete or update a table. I'm not sure what is the best.

Example
I have a table (T1) with part number colum and quantity colum. Each day I

import an excel spreadsheet to a new table (T2) with part numbers and
quantities. I need a query or something that will look at both tables and
update T1 from the information in T2.

So if T1 part number 23 has a quantity of 19 and T2 part number 23 has a

quantity of 5 then it should subtracts a quantity of 5 from part 23 on T1.
So T1 part number 23 would have 14 now.

 

[Wayne Morgan]

See if this query will do the job. Try it on a copy first to make sure it
works.

UPDATE T1 INNER JOIN T2 ON T1.PartNumber= T2.PartNumber SET T1.Qty =
[T1].[Qty]-[T2].[Qty];

--
Wayne Morgan
MS Access MVP

Hi,
I need to make a query that will either delete or update a table. I'm not sure what is the best.

Example
I have a table (T1) with part number colum and quantity colum. Each day I

import an excel spreadsheet to a new table (T2) with part numbers and
quantities. I need a query or something that will look at both tables and
update T1 from the information in T2.

So if T1 part number 23 has a quantity of 19 and T2 part number 23 has a

quantity of 5 then it should subtracts a quantity of 5 from part 23 on T1.
So T1 part number 23 would have 14 now.

 

[Guest]

That worked great. Thanks for the help

Ben

 

[Guest]

One more thing

I need it (T2) to only delete a quantity of two from one of the locations in T1 not from bot

T1 (Table1
Part Location Quantit
12 13a
12 13a

T2 (Table2
Part Location Quantit
12 13a

Any Ideas?

 

[Wayne Morgan]

I guess the first question would be why both lines are in T1 instead of a
single line with Qty 12? Are there other fields that make these items
unique? If so, do you have a preference, based on these other fields, as to
which one the subtraction should be done on (i.e. first in, first out)?