Sales Statistics Query - Summarising Data

D

David Saywell

Hi All,

I need to create a query that displays the following (for a given Month):

Note: The query needs to be based on Financial Year (1 July - 30 June) not
calendar year.

qMonthlyStats
StoreID
ActualSales
ActualSalesLastYear
BudgetSales
ActualYTDSales
BudgetYTDSales
NumberofDocuments
NumberofDocumentsLastYear
NumberofLineItems(SumofQuantity)
QuantityExtendedWarrantyProductsSold

The data for all years is stored in:

tblOrderTrans:
StoreID
TransactionDate
SalesPersonID
ProductCode
DocumentID
Quantity
Sales
Cost

The Budget Data is stored in:

tblBudget:
StoreID
Month
Budget

There are about ten ProductCodes for Warranty Products, they can either be
filtered for in a query or listed in a table.

OrderTrans.DocumentID does not contain unique values e.g. one document could
contain four products therefore tblOrderTrans will contain four records with
the same DocumentID.

In my attempts to produce qMonthlyStats I have not been able to find an
efficient method. Using queries I can get the data to a certain point and
then using multiple Excel Pivot Tables I am able to bring the data together.
However I need to make this report available to a number of people to run
for themselves and having the results compiled in one location is most
desirable.

What is an appropriate line of attack? What resources are available for me
to learn the skills required to complete this task? How should I be
approaching the task?

If further information is required please let me know.

Many thanks,

David
 
Top