Sales this month or year verses last years month or year

[Blitznb]

I am attempting to create a report based on [Sales Analysis] query which would pull suppliers, customers with values for Sales & Commission. This report would then show additional columns for Sales & Commission, however they would be for last years sales or same month last year.

I seem to be caught up in a design issue and where to start. I tired to create it in report itself. Tired query, tired 2 different crosstabs and tired subquery. My syntax as well as setup is likely wrong. So what is the best method to create something like this?

So for
Supplier1, customer1, please show sales & commission for 2011 and show sales and commission for 2010 on same report.

 

[spoole]

ignore posted twice

 

[spoole]

Base your report on a query.

The query needs to include two tables, the 2011 data and the other table is the 2010 data. In the query just create a join by month, supplier and customer.

 

[Blitznb]

Thank you for the reply Spoole,

Does this mean I should create an additional table?
Currently the querry Sales Analysis includes fields for supplier, customer, year, month, sales and commission. the majority of fields for query [Sales Analysis] is pulled from Orders table.

Is it safe to say I should pull 2011 from Sales Analysis and 2010 from Orders. It has to be two different tables/query?

 

[Blitznb]

I need a querry that is set up to ask the following

SELECT DISTINCTROW [Sales Analysis].Principal, [Sales Analysis].[Customer Name], [Sales Analysis].Year,
[Sales Analysis].Sales AS CurrentSales WHERE [Year] = 2011, [Sales Analysis].Commission AS CurrentCom WHERE [Year] = 2011, [Sales Analysis].Sales AS PrevSales WHERE [Year] = 2011 - 1, [Sales Analysis].Commission AS PrevComm WHERE [Year] = 2011 - 1
FROM [Sales Analysis] INNER JOIN Orders ON [Sales Analysis].[Order ID] = Orders.[Order ID];

But im not sure where to start? Subquery? two sperates union,,,,,,,,,,,URGH

 

[plog]

Show some sample data. Show me whats in your table(s) and then what you want the report to look like based on that sample data.

 

[Blitznb]

Hello,

Thank you for your help and review.

My [Sales Analysis] query contains fields
Principals, Customers, Order Date, Year, Month (is 1-12), Sales, Commission, Order ID

The attached screen shot shows report structure I was working on.
I would like to pull all sales for a particular customer, from a particular supplier/principal and have the first set of columns display that info based on current year or month, second column to be same time last year and third set of columns to be percentage of difference.

 

[plog]

This query should get you what you want (minus the percent change which you can calculate in the report):

SELECT Customer, Principal, Sum(Iif(Year(Date())=Year([Order Date]), [Commission], 0)) As CurrentCommission,Sum(Iif(Year(Date())=Year([Order Date]), [Sales], 0)) As CurrentSales, Sum(Iif((Year(Date())-1)=Year([Order Date]), [Commission], 0)) As PriorCommission,Sum(Iif((Year(Date())-1)=Year([Order Date]), [Sales], 0)) As PriorSales 
FROM [Sales Analysis] 
GROUP BY Customer, Principal;

The above query uses your Sales Analysis query, it would be cleaner to use the underlying table that Sales Analysis is built on.

 

[Blitznb]

Thank you Plog.