Number of sales per product SQL query

[paulbeckett]

Hi all

Another SQL query! I am getting up to speed with using Access but this one is a bit too complicated for me and I am not sure if it is even possible.

I have been trying to figure out a way of adding to the query below, a column that displays amount of sales for each product within a date range.

SELECT Product.[Product reference] AS Ref, Product.[Short description] AS [Product Name], qryFindAllCustomVars_Crosstab.SUPPLIERREF AS [Supplier Ref], Format(IIf([sTax1OpaqueData]="301=1750.00=0=" Or [sTax1OpaqueData]="301=1750=0=",(Product.price/100)*1.175,product.price/100),"Fixed") AS [Gross Price], Product.nStockOnHand AS [Stock Level], qryFindAllCustomVars_Crosstab.SRCHPRODUCT AS [Made in], qryFindAllCustomVars_Crosstab.SRCHCOLOUR AS Colour, qryFindAllCustomVars_Crosstab.SRCHBRAND AS Brand, qryFindAllCustomVars_Crosstab.NETPURCHASEPRICE AS [NET cost]
FROM [Catalog section] AS [Catalog section_1] INNER JOIN (qryFindAllCustomVars_Crosstab INNER JOIN ([Catalog section] INNER JOIN Product ON [Catalog section].nSectionID = Product.nParentSectionID) ON qryFindAllCustomVars_Crosstab.sProductRef = Product.[Product reference]) ON [Catalog section_1].nSectionID = [Catalog section].nParentSectionID
WHERE ((([Catalog section_1].bExcludeFromFroogle)=0) AND ((Product.bExcludeFromFroogle)=0));

I have assumed this needs to involve the Orders table but it is clearly alot more complicated than I can do!

Any suggestions or pointers would be appreciated.

Paul
KJ Beckett

[Bruce]

Paul,

I am running this past development for you, will update as soon as I hear back from them.

Kind regards,

[Bruce]

Hi Paul,

We will need the SQL for the subquery that is used (qryFindAllCustomVars_Crosstab.SUPPLIERREF ) etc in order to help you here, unless you want a totally new one that only displays amount of sales for each product within a date range.

If you want just a start base on what to do then its something like

SELECT Order.[Date Ordered], OrderDetail.ProductReference,
Sum(OrderDetail.TotalCost) AS SumOfTotalCost
FROM [Order] INNER JOIN (OrderDetail INNER JOIN Product ON
OrderDetail.ProductReference = Product.[Product reference]) ON
Order.[Order Sequence Number] = OrderDetail.OrderSequenceNumber
GROUP BY Order.[Date Ordered], OrderDetail.ProductReference
HAVING (((Order.[Date Ordered])>[start] And (Order.[Date
Ordered])<[end]));

or to exclude other product types this SQL

SELECT Order.[Date Ordered], OrderDetail.ProductReference,
Sum(OrderDetail.TotalCost) AS SumOfTotalCost
FROM [Order] INNER JOIN (OrderDetail INNER JOIN Product ON
OrderDetail.ProductReference = Product.[Product reference]) ON
Order.[Order Sequence Number] = OrderDetail.OrderSequenceNumber
WHERE (((OrderDetail.nLineType)<>2))
GROUP BY Order.[Date Ordered], OrderDetail.ProductReference
HAVING (((Order.[Date Ordered])>[start] And (Order.[Date Ordered])<[end])
AND ((OrderDetail.ProductReference)<>''));

Kind regards,

[paulbeckett]

Hi Bruce

Thanks. It would be good to make an amendment to the existing SQL because this would allow me to select and focus on particular groups using the customnavs.

Therefore there are two SQL scripts used:

qryFindAllCustomVars

SELECT HTMLVariables.nVariableID, HTMLVariables.sName, ProductProperties.sProductRef, ProductProperties.sString1, Product.[Short description]
FROM Product INNER JOIN (ProductProperties INNER JOIN HTMLVariables ON ProductProperties.nValue1 = HTMLVariables.nVariableID) ON Product.[Product reference] = ProductProperties.sProductRef
WHERE (((HTMLVariables.nVariableID)=30 Or (HTMLVariables.nVariableID)=31 Or (HTMLVariables.nVariableID)=32 Or (HTMLVariables.nVariableID)=28 Or (HTMLVariables.nVariableID)=29 Or (HTMLVariables.nVariableID)=80 Or (HTMLVariables.nVariableID)=39 Or (HTMLVariables.nVariableID)=53 Or (HTMLVariables.nVariableID)=54 Or (HTMLVariables.nVariableID)=80 Or (HTMLVariables.nVariableID)=81 Or (HTMLVariables.nVariableID)=82 Or (HTMLVariables.nVariableID)=83 Or (HTMLVariables.nVariableID)=84));

which is used by qryFindAllCustomVars_Crosstab:

TRANSFORM First(qryFindAllCustomVars.sString1) AS FirstOfsString1
SELECT qryFindAllCustomVars.sProductRef
FROM qryFindAllCustomVars
GROUP BY qryFindAllCustomVars.sProductRef
PIVOT qryFindAllCustomVars.sName;

I would apprecaite your advice further but in the meantime I will have a play with the SQL you have posted.

Many thanks

Paul
KJ Beckett

[paulbeckett]

Hmmm

I'm definately no expert at databases however I think I need to create some sort of new query that combines OrderDetail and Order tables and lists the total number of sales for each ProductReference number. Then I need to cross reference this query in my original query.

Am I on the right track? I will keep playing!

Many thanks

Paul

[pinbrook]

I don't know what you want to do with the info you are gathering so I may be off base with this suggestion, and again I may be wrong but I think Mole Ends marketing module gives you some of the info you are after, ie it will create data on who bought what and when etc etc

It may be worth downloading a 30 day trial to see what it can do.

The same marketing module is included in V8 as standard

[paulbeckett]

Hi Jo

Jan's Marketing download is excellent but doesnt quite do what I want to do.

If I can just add a single column to my original query that allows me to display the amount of units sold per product I will have exactly what I need.

Bruce, BTW, it is likely that I am wrong (becuause I am much deeper into this than my skills allow) but I am not sure if any changes would need to be made to the other SQL queries that my main query refers to? Instead I think a seperate query is required that adds up the total sales for each product (within a set range) and is then linked the original query?

Paul

[Bruce]

Paul,

We will need the Database to get this correct. We had to delete so many of the fields in your qry just to get it running, but obviously I have no records displayed etc as it is only filtering certain products which only works on your DB.

If you can zip the database and email it to me please ( if smaller than 10 MB), else drop me a note at bksupport at actinic.co.uk and I will send over FTP details for you to upload to.

Kind regards,

[paulbeckett]

Hi Bruce

I emailed you bksupport at actinic.co.uk. It is much bigger than 10MB so I will have to FTP it. Please can you email office AT kjbeckett.com with the FTP instuctions.

Also if you post the code so far I may be able to have a play with it, test it and give any suggestions / feedback. If I can help you help me that would be good!

Kind regards
Paul
KJ Beckett

[paulbeckett]

Hi Bruce

Got your email...many thanks. Please can you send me the instructions to FTP my database to you?

Kind regards

Paul

[paulbeckett]

Hi Bruce

Emailed and FTP'd about 5 minutes ago so should be with you soon.

Many thanks!

Paul

[Bruce]

Is it the file called Backup.zip? Thats a lot smaller that what you said it would be.

Kind regards,

[paulbeckett]

Hi Bruce.

Yes it is. I forgot that I could zip it up and reduce the size dramatically.

Paul

[Bruce]

The SQL would look something like

SELECT OrderDetail.ProductReference, Sum(OrderDetail.TotalCost) AS
SumOfTotalCost
FROM [Order] INNER JOIN (OrderDetail INNER JOIN Product ON
OrderDetail.ProductReference = Product.[Product reference]) ON
Order.[Order Sequence Number] = OrderDetail.OrderSequenceNumber
WHERE (((Order.[Date Ordered])>=[Start] And (Order.[Date Ordered])<=[End])
AND ((Order.bOrderIsDeleted)=0) AND ((OrderDetail.nLineType)<>2))
GROUP BY OrderDetail.ProductReference
HAVING (((OrderDetail.ProductReference)<>''))
ORDER BY OrderDetail.ProductReference;

If for example you enter a start date of 2006/09/01 and end date of 2006/09/08 then it will list these orders with total values etc.

Kind regards,

[paulbeckett]

Hi Bruce

Further to our emails, I dont think your posting was submitted successfully. Would you be able re post?

Kind regards

Paul