Combining Queries

[Otacustes]

Hi all,

I have the following tables and need to update data between them:

TableA

Desc PrevQty
abc
def
ghi
xyz

TableB

Desc Qty
abc 10
def 15
ghi 20
abc 30
xyz 10

I need help with a query to get the total Qty from tableB and update the PrevQty in TableA.

When the query is run, TableA should look like this:

TableA

Desc PrevQty
abc 40
def 15
ghi 20
xyz 10

so far I have this for the query:

SELECT Desc, sum(Qty) as TotalQty
FROM TableB
Group By Desc;

This obviously gives me the total qty for each desc - the trouble I am having is combining this with an update query to modify TableA - I hope I am half way there There is every possibility that what I have started with is wrong!!

Any help or guidance offered is greatly appreciated.

Thanks

 

[jaxtell]

I'm not sure of the best ANSI SQL way to do this. You'll probably have to to an update for each Desc. If you tell me what database you're using, and what language interacts with the database, I'll see what I can come up with for a solution.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[PHV]

UPDATE TableA
SET PrevQty=(SELECT SUM(Qty) FROM TableB WHERE Desc=TableA.Desc)
WHERE Desc IN(SELECT Desc FROM TableB)

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

 

[Otacustes]

Hi guys,

@jaxtell, I am using a MS Access database

@PHV, When you view the the query it gives a result which doesn't look as if it has consolidated the part numbers. Although when you try to run the query it gives an error:
"Operation must use an updateable query"

Thanks for the help guys.

All help is greatly appreciated
Kind regards
Ota

 

[PHV]

MS Access database

UPDATE TableA
SET PrevQty=DSum("Qty","TableB","Desc='" & [Desc] & "'")

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