Please help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Please help

Author

Topic

sentiboy
Starting Member

3 Posts

Posted - 2006-02-01 : 14:09:30

I am having 2 tables in MS-Access database.

Table 1: Procurement

Slipno. itemid qtyrecd supplierId
00001 1 25 2
00012 2 89 3
00051 1 75 1
00104 1 50 2
08007 2 11 3

Table 2: Issued_details

Slipno. itemid qtyissued customerId

00109 1 40 5
00702 1 30 2
00885 1 5 4
00904 2 12 1
09009 2 8 2

I need resulting table as

Resulting Table:

ItemId Total Received Total Issued Net Balance
1 150 75 75
2 100 20 80

Please tell me sql command for getting the resulting table.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-01 : 14:17:34

Is ItemID the primary key or a unique index in either table, or is their a third table that contains all ItemIDs?

Can there be an ItemID in Procurement that does not exist in Issued_details, or vice-versa?

sentiboy
Starting Member

3 Posts

Posted - 2006-02-02 : 14:02:58

quote:
Originally posted by blindman

Is ItemID the primary key or a unique index in either table, or is their a third table that contains all ItemIDs?

Can there be an ItemID in Procurement that does not exist in Issued_details, or vice-versa?

Yes ItemId is a field in another table (in item_master).

Yes it is possible that ItemID is in procurement table and it is not there in issuedd_details, not vice-versa.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-02 : 15:45:08

This is how you would do it in TSQL. In Access, you may need to need sub-queries instead of nested queries.

select	item_master.item_id,
	coalesce(ReceivedTotals.TotalReceived, 0) as TotalReceived,
	coalesce(IssuedTotals.TotalIssued, 0) as TotalIssued,
	coalesce(ReceivedTotals.TotalReceived, 0) - coalesce(IssuedTotals.TotalIssued, 0) as NetBalance
from	item_master
	left outer join --ReceivedTotals
		(select	item_id,
			sum(qtyrecd) as TotalReceived
		from 	Procurement
		group by item_id) ReceivedTotals
		on item_master = ReceivedTotals.item_id
	left outer join --IssuedTotals
		(select	item_id,
			sum(qtyissued) as TotalIssued
		from 	Issued_details
		group by item_id) IssuedTotals
		on item_master = IssuedTotals.item_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources