summing help 1

[HOMMER]

I want to sum my items by the criteria I choose. I have a status coloumn and a items coloumn and qty coloumn. I want to sum up the qty by items by the criteria in my status.

Item qty status
stuff 5 on order
stuff 10 received
stuff 2 adjust inventory


Answer for stuff is 12 because the on order is not in inventory yet. Hope this makes sense.

 

[KyleS]

SELECT Tablename.Item, Sum(TableName.qty) as SumOnHand
FROM Tablename
WHERE Tablename.status <> 'on order'

Would give you the sum of each item that's on hand.

Let me know if you need more. Kyle

 

[HOMMER]

For some reason it gives me a error invalid syntax.
(SELECT tbl_orederd/received.description, Sum(tbl_ordered/received.qty) as SumOnHand)
FROM[tbl_ordered/received]
WHERE tbl_ordered/received.status <> '1')
1= on order (from my combo box.)

 

[KyleS]

DOH!

Ordered is misspelled in a few places, but most importantly there's an issue with the Table name... Access (and just about every dB system) hates it when there are weird characters (spaces, commas, / etc.) in the table name. So change:
(SELECT tbl_orederd/received.description, Sum(tbl_ordered/received.qty) as SumOnHand)
FROM[tbl_ordered/received]
WHERE tbl_ordered/received.status <> '1')

To:
SELECT [tbl_ordered/received].[description], Sum([tbl_ordered/received].[qty]) as SumOnHand
FROM[tbl_ordered/received]
WHERE [tbl_ordered/received].[status] <> '1'
Kyle

 

[HOMMER]

Thanks It almost worked by with your direction and a bit of a twist I was able to get it to work. For others who use this post this is what worked for me.

KyleS, Thanks so much for the help!!!

SELECT DISTINCTROW [tbl_ordered/received].description, Sum([tbl_ordered/received].qty) AS [received qty]
FROM tbl_items_list LEFT JOIN [tbl_ordered/received] ON tbl_items_list.ID = [tbl_ordered/received].description
WHERE ((([tbl_ordered/received].status)<>1))
GROUP BY [tbl_ordered/received].description;

 

[KyleS]

Happy to help HOMMER! Kyle