December 6, 2011 at 7:54 pm
Great day guys!
Please help me with my Access SQL problem
I have this table structure:
TABLENAME:SalesDetail
Fields:
Barcode
Category
Qty
Amount
Date
All barcodes that has regular price starts with either M or D while all sales item starts with either A or B. What I needed is to view the following attributes in one SQL command:
1. Category
2. Sales Item Qty per Category
3. Regular Item Qty per Category
Any help will be greatly appreciated. Thanks! 🙂
December 6, 2011 at 9:22 pm
I tried this command but sadly it's not working correctly:
SELECT Category,SUM(Qty) AS RegQty,
(SELECT SUM(Qty)
FROM SalesDetail
WHERE LEFT(Barcode,1) IN('A','B')) AS SaleQty
FROM SalesDetail
WHERE LEFT(Barcode,1) IN('M','D')
GROUP BY Category
QUERY OUTPUT:
Category RegQty SaleQty
BARONG JUSI 2 2
JACKET FORMAL 1 2
O. BARONG S/S 1 2
SAMPLE RECORDS:
Barcode Category Qty Amount Date
MBH006WaLBI BARONG JUSI 1 2900 1/5/2007
MJF137NFLVD JACKET FORMAL 1 3500 1/24/2007
DOS033W1MPF O. BARONG S/S 1 800 1/23/2007
AOS009Y0FRM O. BARONG S/S 1 600 6/22/2007
BOS009NRFRM O. BARONG S/S 1 600 6/26/2007
MBH006WaLBX BARONG JUSI 1 2900 1/8/2007
OUTPUT SHOULD BE:
Category RegQty SaleQty
BARONG JUSI 2 0
JACKET FORMAL 1 0
O. BARONG S/S 1 2
HEEEEEELLLP! 🙂
December 6, 2011 at 10:31 pm
Help, hope you guys could give me helpful ideas
and not unrelated links....
Thanks 😉
December 6, 2011 at 11:01 pm
another solution...
select Category,
sum(case when left(Barcode, 1) in ('M', 'D') then Qty else 0 end)[Regular],
sum(case when left(Barcode, 1) in ('A', 'B') then Qty else 0 end)[Sales]
from SalesDetail
group by Category
"Often speak with code not with word,
A simple solution for a simple question"
December 6, 2011 at 11:13 pm
Thanks mhike2hale:
I tried your given statements in Access SQL as:
SELECT CATEGORY,
sum(case when left(Barcode, 1) in ('M', 'D') then Qty else 0 end) AS Sales,
sum(case when left(Barcode, 1) in ('A', 'B') then Qty else 0 end) AS Regular
FROM SalesDetail
GROUP BY CATEGORY
but it gave me this error:
Syntax error (missing operator) in query expression 'sum(case when left(Barcode,1)in('M','D') then Qty else 0 end)'.
I think the statement was correct, it's just that I'm missing some syntax to be able to run in Access.
Will wait for your feedback bro! 🙂
December 6, 2011 at 11:26 pm
(case when) statement is not supported in access.
can u try this?
select Category,
sum(iif(left(Barcode, 1) in ('M', 'D'), Qty, 0)[Regular],
sum(iif(left(Barcode, 1) in ('A', 'B'), Qty, 0)[Sales],
from SalesDetail
group by Category
"Often speak with code not with word,
A simple solution for a simple question"
December 6, 2011 at 11:33 pm
Yeah thru Google I found out that Access doesn't support CASE WHEN statement,
I tried your new suggestion and to my amazement it works really well. Perfect bro! Thanks! Maraming salamat! (Filipino way of saying Thank you very much)
Surely you can help a lot of programmers like me.
:Wow:
December 6, 2011 at 11:36 pm
Salamat 🙂
December 6, 2011 at 11:38 pm
Walang Anuman Sir! ^_^,
"Often speak with code not with word,
A simple solution for a simple question"
December 6, 2011 at 11:45 pm
Glad to know that you're also a Filipino?
Are you here in Manila?
Any FB account or YM?
Hope we could meet sometime and have some cold beer, haha!
🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply