SQL Question

[carl]

My data looks like this:

DateField Group Quantity Price
20090102 G0 1 1.66
20090102 WS 2 7.31
20090102 G0 10 1.09
20090102 G0 1 2.72
20090102 I8 5 50.01
20090102 G0 35 0.51

I need a query that will sum the quantity grouped by Month (note the month
is the 5th and 6th character of my "DateField"), Group, and Price <=3,
Price>3.

Is this possible ?

Thank you in advance.

 

[vanderghast]

Yes, it is possible.



I assume you are also interested in "how"




Sure, it would be easier if your date field was really a date. I assume it
is a NUMBER, not a string, so dateField \ 100 returns the date-month.

In SQL view of a new query:



TRANSFORM SUM(quantity)
SELECT dateField \ 100
FROM tableName
GROUP BY dateField \ 100
PIVOT SWITCH(Price<= 3, "Price<=3", true, "Price>3")



Vanderghast, Access MVP