Report To Track Daily Sales By Sales Person

[carl]

My data records look like so:

Date Type OrderStat Sales Qty
20070117 Order LM 2
20070117 Cancel Cancel CM 2
20070117 Order SG 10
20070117 Order LM 1
20070116 Cancel Cancel CM 1

Is it possible to create a report like this:

Date Sales Order OrderQty Cancel CancelQty
20070117 LM 2 3 0 0
20070117 CM 0 0 1 2
20070117 SG 1 10 0 0
20070116 CM 1 5 2 6
20070116 SG 1 4 0 0

So for each date in the database and each sales, Order Column is the count

of Type=Order, OrderQty Column is sum of qty where Type=Order, Cancel Column

is count of Type=Cancel, etc.

Thanks you in advance.

 

[Marshall Barton]

My data records look like so:

Date Type OrderStat Sales Qty
20070117 Order LM 2
20070117 Cancel Cancel CM 2
20070117 Order SG 10
20070117 Order LM 1
20070116 Cancel Cancel CM 1

Is it possible to create a report like this:

Date Sales Order OrderQty Cancel CancelQty
20070117 LM 2 3 0 0
20070117 CM 0 0 1 2
20070117 SG 1 10 0 0
20070116 CM 1 5 2 6
20070116 SG 1 4 0 0

So for each date in the database and each sales, Order Column is the count

of Type=Order, OrderQty Column is sum of qty where Type=Order, Cancel Column

is count of Type=Cancel, etc.

Play around with this kind of query:

SELECT orderdate, sales
Count(IIf(ordertype="Order", 1, Null)) As OrderCnt,
Sum(IIf((IIf(ordertype="Order", Qty, 0)) As OrderQty,
Count(IIf(ordertype="Cancel", 1, Null)) As CancelCnt,
Sum(IIf((IIf(ordertype="Order", Qty, 0)) As CancelQty
FROM yourtable
GROUP BY orderdate, sales

Once you get the query to produce the desired data, the
report should be trivial.