December 13, 2011 at 2:16 am
Guys, I wanted to create SQL statement for Access
that will show a Comparative Analysis of the Sales QTY.
For example I have this Table: (SALES)
IDBARCODE CATEGORY DATE QTY
1>MBH006WaLBI>BARONG JUSI >1/5/2007 >1
2>MBH006WaMBI>BARONG JUSI >1/6/2007 >2
3>MWS295W9MM2>WOVEN S/S >1/7/2008 >3
4>DOS037KfSPN>O. BARONG S/S >1/8/2008 >4
5>DOsA09WCMPU>O. BARONG S/S P >1/9/2007 >5
6>ABH314M0FWN>BARONG JUSI >1/10/2007>6
7>AOS033W0DPF>O. BARONG S/S >1/11/2008>7
8>BOs068G1LPF>O. BARONG S/S P >1/12/2007>8
9>BWLA02W0MLb>WOVEN L/S >1/13/2008>9
10>BOs078GXCPI>O. BARONG S/S P >1/14/2008>10
NOTE: I used greater than symbol (>) to separate records but it's not included in actual records.
Desired output:
CATEGORY REG07 REG08 SAL07 SAL08 T2007 T2008
BARONG JUSI >3 >0 >6 >0 >9 >0
O. BARONG S/S >0 >4 >0 >7 >0 >11
O. BARONG S/S P >5 >0 >8 >10 >13 >10
WOVEN L/S >0 >0 >0 >9 >0 >9
WOVEN S/S >0 >3 >0 >0 >0 >3
NOTE: I used greater than symbol (>) to separate records but it's not included in actual output.
I need a statement that will view the Item per category.
All barcodes that starts with M and D are regular items,
otherwise barcodes that starts with A and B are sales item.
REG07 column should contain the total qty of regular items
for the year 2007
REG08 column should contain the total qty of regular items
for the year 2008
SAL07 column should contain the total qty of sale items
for the year 2007
SAL08 column should contain the total qty of sale items
for the year 2008
T2007 column should contain the total qty for all items
for the year 2007
T2008 column should contain the total qty for all items
for the year 2008
Guys, I really need your help. Kindly post your suggestions.
TAKE NOTE: What I need is SQL statement for Access
and not for SQL server.
Thanks in advance everyone!
🙂
December 13, 2011 at 6:25 am
Couple of problems: -
1) You haven't explained how we can tell what is a "regular", "sale" or "all" item. I'm guessing that "all" is just a combination of "regular" and "sale", but don't know which are "regular" and which are "sale" from your sample data.
2) I'm not all that familiar with the flavour of SQL used by Access, but am willing to have a go at prodding you in the correct direction.
I'd guess you're looking at something like this -
SELECT CATEGORY,
SUM(CASE WHEN DATE BETWEEN '1/1/2007' AND '12/31/2007'
THEN QTY
ELSE 0 END) AS REG07,
SUM(CASE WHEN DATE BETWEEN '1/1/2008' AND '12/31/2008'
THEN QTY
ELSE 0 END) AS REG08
FROM TableSALES
GROUP BY CATEGORY
You need to include in the CASE expression the way that you differentiate between a "regular" and a "sale" item, as currently the above CASE expression will return the "all" instead of a particular one.
December 13, 2011 at 7:59 am
CASE isn't available in Access you would need to use iif.
December 13, 2011 at 9:04 am
Chrissy321 (12/13/2011)
CASE isn't available in Access you would need to use iif.
D'Oh, yep that's correct.
Hopefully the OP understood the concept I was trying to get across so was able to convert it into Access syntax.
December 13, 2011 at 6:35 pm
Thanks Cadavre:
I need a statement that will view the Item per category.
All barcodes that starts with M and D are regular items,
otherwise barcodes that starts with A and B are sales item.
Will wait for your cool solutions.
🙂
December 13, 2011 at 11:03 pm
Chrissy321,
Yeah, Case doesn't work in Access. Anyway any idea using
IIF. Kindly read my sample table and desired output specifications.
Salamat! (Filipino way of saying Thanks)
🙂
December 14, 2011 at 1:42 am
omi_real (12/13/2011)
Thanks Cadavre:I need a statement that will view the Item per category.
All barcodes that starts with M and D are regular items,
otherwise barcodes that starts with A and B are sales item.
Will wait for your cool solutions.
🙂
As I said, the flavour of SQL used by Access is not something I'm overly familiar with.
I think this is the syntax you need
SELECT CATEGORY,
SUM(IIF((MID(BARCODE,1,1)="M" OR MID(BARCODE,1,1)="D") AND DATE BETWEEN "1/1/2007" AND "12/31/2007", QTY, 0) AS REG07,
SUM(IIF((MID(BARCODE,1,1)="M" OR MID(BARCODE,1,1)="D") AND DATE BETWEEN "1/1/2008" AND "12/31/2008", QTY, 0) AS REG08,
SUM(IIF((MID(BARCODE,1,1)="A" OR MID(BARCODE,1,1)="B") AND DATE BETWEEN "1/1/2007" AND "12/31/2007", QTY, 0) AS SAL07,
SUM(IIF((MID(BARCODE,1,1)="A" OR MID(BARCODE,1,1)="B") AND DATE BETWEEN "1/1/2008" AND "12/31/2008", QTY, 0) AS SAL08,
SUM(IIF(DATE BETWEEN "1/1/2007" AND "12/31/2007", QTY, 0) AS T2007,
SUM(IIF(DATE BETWEEN "1/1/2008" AND "12/31/2008", QTY, 0) AS T2008
FROM TableSALES
GROUP BY CATEGORY
It's the general idea anyway, so you should be able to shoe-horn it into Access since I'm sure you're more familiar with it than me.
December 14, 2011 at 1:57 am
Cadavre:
Thanks, I actually tried nested IIF but when I try
to run the statement the computer stocked-up.
So maybe using AND instead of multi IIF would
make a difference.
Will give you feedback about the result.
Kudos! 🙂
December 14, 2011 at 11:38 pm
Cadavre:
Is their any other way,
the computer hanged-out
due to many IIF.
I think their are other ways,
help!
🙂
December 15, 2011 at 1:30 am
omi_real (12/14/2011)
Cadavre:Is their any other way,
the computer hanged-out
due to many IIF.
I think their are other ways,
help!
🙂
Pivot ?
December 15, 2011 at 9:00 pm
Cadavre:
Could you please give me an example!
I tried to search google but I don't find what I need.
I see the TRANSFORM statement and tried to use it but I think it's limited to one field that I can assign in transform statement.
Actually the statement that I design contains a lot of where condition with 3 inner joins table.
I have two general conditions and other 2 condition that applies to particular header only. I am so confused on how to make it work. I'm having trouble with the filtering of date.
For example I want to create a monthly report for particular store,
that report should also contain header for the comparison on previous month and previous year.
Help! 🙂
December 18, 2011 at 6:46 pm
Cadavre,
Help! 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply