Query problem

[kryten]

Hi everyone,

I have 4 tables

tblOrderDetails
tblTradeOrderDetails
tblReturnDetails
tblExchangesDetails

these are all related to tblProducts and all have the same three fields -Code (primary key) description and qty. I am trying to create an inventory tracking system by calculating the goods that come in (tblGoodsIn) and the goods that are sent out (tblGoodsOut). I need to create a query that sums the total of all goods that are sent out, but I'm not sure how to make the query return just one set of totals for each code using the 4 tables.

I would be most grateful for any help you could give me.

 

[ecniv]

How about one table with a foreign key to a look up table which has Normal, Trade, exchange, return etc?


If you insist/wanna try this layout, you'll need to have a union query (read up on it) with sub queries for totals and a surrounding query to total the union query.

eg
Select whatever
From
(
(select from ) as qryorders
union
(select from ) as qrytradeorders
union
(select from ) as qryreturnorders
union
(select from ) as qryexchangeorders
)
group by...


Vince

 

[kryten]

Thanks for your advice Vince, I'm not sure I understand how to do what you suggest though. Could you be more explicit? (fogive my ignorance)

Thanks