[RchLuvSlly]

Hello all,

I'm new to the community and would like to have some guides from you all, if you don't mind. Well, here's my case:
I have a table in SQL that lists products along with each of their quantity available. Let's call it table tblQty. Now, let's assume there's a process called Pre-Order (PO). So, if I have product A with available quantity of 10, yet, I pre-order 5 of product A. My program (developed with VB 2010) should tell me that I only have 5 available quantity for product A. This illustration may hopefully be of some help:
- tblQty:
Name: Product A, Quantity Available: 10
- tblPO (to handle the PO process):
Name: Product A, Quantity Reserved: 5
Name: Product A, Quantity Reserved: 3

As you can see, there're 2 rows in table tblPO with exactly the same product being reserved. What happened to me is that when I joined (INNER JOIN - I tried other joins as well already) these 2 tables together, what I get back from the SQL query is this:
Name: Product A, Quantity Available: 5 --> from 10 subtracted by 5
Name: Product A, Quantity Available: 7 --> from 10 subtracted by 3

This is wrong. What I want to get back is this (which I'm not getting at the moment):
Name: Product A, Quantity Available: 2 --> from 10 subtracted by 5 and then by 3

Table structures (hopefully it may help):
- tblQty:
Product Name, Product Quantity
- tblPO:
Product Name, Product Quantity Reserved

In tblQty, there's no duplicate product name whereas there're duplicate product names in tblPO (it's allowed).

Please, if anyone can help, I would be very grateful. Thank you in advance.

Best regards,
RchLuvSlly

[rgfirefly24]

you will want to join on an inner query. Because you have multiple rows that need to be summed up it is the only way you have to do it

Select TotAvailable = (q.Available - po.TotReserved)
FROM tblQty q 
INNER JOIN
	(
		SELECT ProductName,TotReserved = SUM(Reserved)
		FROM tblPO po
		GROUP BY ProductName
	) po ON q.ProductName = po.ProductName

[RchLuvSlly]

Thanks for the reply. I'll try it and confirm whether it really does what I need or not. Once again, thank you, rgfirefly24.

[RchLuvSlly]

Hello again, rgfirefly24,

I have tried the suggestion you provided and, what a charm, it works! I couldn't figure it out before but thanks to you, really, now my problem is solved. Once again, thank you very much indeed, rgfirefly24! thumbs up d(,)b