Access Product Sales query against v9 ActinicCatalog.mdb tables

[paulbeckett]

Hi all

I have just created a really handy query that lists products sold by day (and time). My intention is to dump the list into a spreadsheet and do a pivot table to get some really useful sales information.

The query lists the following information:

Product Ref , Product Price , Quantity Ordered , Total Sales (Price * Qty Ordered), Date/Time of Sale (plus a couple of custom properties listing categories which I have removed from the query to simplify it whilst building).

However the figures are out by about 10% when comparing to the Actinic sales report. The Access query is always under. I'm obviously missing/excluding certain product/sales...

This is the SQL:

SELECT Product.[Product Reference], [Product.Price]/100*1.175 AS [Product-Price], OrderDetail.QuantityOrdered, Order.[Date Ordered]
FROM ([Order] INNER JOIN OrderDetail ON Order.[Order Sequence Number] = OrderDetail.OrderSequenceNumber) INNER JOIN Product ON OrderDetail.ProductReference = Product.[Product Reference]
WHERE ((([Product.Price]/100*1.175)<>0) AND ((Order.Status)="F") AND ((OrderDetail.QuantityCancelled)=0) AND ((Order.bOrderIsDeleted)=0) AND ((OrderDetail.nLineType)<>2));

I've gone through it and I think I must be excluding a value from one of the following fields. I currently have the following set:

Order.Status = F
OrderDetail.Quantity Cancelled = 0
OrderDetail.nLineType = <>2
Order.bOrderIsDeleted = 0

Can anyone see what I might have missed to make the query match the Actinic Sales Report output.

Or can anyone confirm what values are contained in Order.Status, OrderDetail.nLineType, etc that I might need to include?

Any advice or pointers appreciated.

Paul

[paulbeckett]

Hi all

Upon search the forums again I just found this post... http://community.actinic.com/showpos...93&postcount=3 which contains:

The orderlines have a status and the order also has a status

Order :

N = Normal
F = Complete
S = Shipped

Order Detail :

N = Normal
S = Shipped
C = Cancelled
F = Finished

Order status is also affected by the flag called bOrderIsDeleted (in the order table), if this is true then the order has been marked for deletion.

Does anyone know what Norman and Finished means?

Paul

[paulbeckett]

Hi all

I have improved the accuracy of the query by changing:

Order.Status = F

to show all values (not just F).

This seems to have reduced the GAP significantly although we are still not quite there and the query is still under.

I'm now wondering if the OrderDetail.nLineType = <>2 might need tweaking. However there's nothing at all in the forums about "nLineType"

Does anyone have any pointers regarding getting accurate sales figures out of the database?

Paul

[cbarling]

Paul, this is a sheer guess, but could the difference be shipping? It's held in a separate field, I believe.

If you email Ben, I am sure he could get you the "Files and Tables" document which explains the use of every field.

Chris

[paulbeckett]

Hi Chris

Thanks.

I've excluded shipping from the total (and included tax).

I will email Ben to see if I can get the document to give a better understanding of the values.

Paul

[HowardW]

Hi Paul
It all depends on your definition of an order
If you search on orders where the flag is "F" then it will exclude items that are on back order (order line flag = "N") i.e. not shipped.
You need to generate 2 tables
1 where the order detail flag is "S" i.e. the product has been shipped
2 where the order detail flag is "N" i.e. the product has not been shipped
The total should then be the same as the Actinic report.
This is the SQL for our table for "products Outstanding:

SELECT Order.[Date Ordered], OrderDetail.ProductReference, Sum(OrderDetail.QuantityOrdered) AS SumOfQuantityOrdered, OrderDetail.sProductDescription, Order.[Order Number], OrderDetail.QuantityShipped, OrderDetail.Status
FROM [Order] INNER JOIN OrderDetail ON Order.[Order Sequence Number] = OrderDetail.OrderSequenceNumber
GROUP BY Order.[Date Ordered], OrderDetail.ProductReference, OrderDetail.sProductDescription, Order.[Order Number], OrderDetail.QuantityShipped, OrderDetail.Status
HAVING (((OrderDetail.ProductReference) Is Not Null) AND ((Sum(OrderDetail.QuantityOrdered))>0.1) AND ((OrderDetail.QuantityShipped)=0) AND ((OrderDetail.Status)="n"))
ORDER BY OrderDetail.ProductReference;

Regards
Howard

[paulbeckett]

Hi Howard

It all depends on your definition of an order

Good point!

We are trying to reconcile with the standard Actinic "Sales Analysis" report which is set to:

Payment Recieved = Y
Invoice Printed = Y
Goods Shipped = Y

So we are only interested in "definate" sales that we have fulfilled (we don't really have back-orders). We are also going to run this report a month later so that it includes any returns.

So I think we only want to have the S and not N. Also, thinking about it, we also need to add something to only show where the "Payment Revieved" is set to Y.

Paul

[paulbeckett]

Also, thinking about it, we also need to add something to only show where the "Payment Recieved" is set to Y.

Y = 10

By setting Order.Order Number to 10 it only shows orders where payment has been received and therefore excludes any "Payment Pending" orders.