Create report by Year 2

[xdia]

I want to create 2 reports from table such as below.

Tble
Cust Prod Sale$ date
abc a1 10 200010
abc b2 20 200105
abc c3 10 200208
bcd a1 30 200204
def c3 10 200006

I want report like this

cust 2000 20001 2002
abc 10 20 10
bcd 0 0 30
def 0 0 10

another report like this
Prod 2000 2001 2002
a1 10 0 30
b2 0 10 0
c3 10 0 10

Should I create query for each year and join them together or is there other simpler way. I think this should be very easy but I don't know how. Thanks for any input^^

 

[67peshawar294]

xdia,
Create a crosstab query using the cust as the first selection, date for the second and choose year, then sales$ as the sum.
If you want to restrict the date. Select another datefield in the query and for 2000,2001,2002 make the criteria >#12/31/1999#
The SQL is
TRANSFORM Sum(tablename.sales$) AS [The Value]
SELECT tablename.cust, Sum(tablename.sales$) AS [Total Of sales]
FROM tablename
WHERE (((tablename.DATE)>#12/31/1999#)
GROUP BY tablename.cust PIVOT Format([DATE],"yyyy"

Very similar procedure for the other table.

 

[xdia]

Thanks it works perfect. Can I ask one more question? How would I put sales for <b>this month</b> with sales by year?

 

[67peshawar294]

Not real sure. Are you looking for the percent of sales by month?
jim

 

[xdia]

Somthing like

Cust LastYr ThisMonth YTD
CO A 1500 230 1300
CO B 2000 500 1800
CO C 3000 100 1500

I have table with sales data for last 2 or 3 years and when I get the report I would like to show sales$ for lastyr thismonth and yeartodate.

Thanks again

 

[67peshawar294]

xdia,
might be a day or so, i'm alone at the office and busy as can be...
jim

 

[67peshawar294]

I'm not a Guru but here are a couple of SQL's to get you started.
note: change [yourtable]to your table name. [datein] and [price] to your field names.

(Regular query to select current year)

SELECT DISTINCTROW yourtable.DATEIN, yourtable.company, Format$([yourtable].[DATEIN],&quot;mmmm yyyy&quot AS [DATEIN By Month], Sum(yourtable.PRICE) AS SumOfPRICE
FROM yourtable
GROUP BY yourtable.DATEIN, yourtable.company, Format$([yourtable].[DATEIN],&quot;mmmm yyyy&quot
HAVING (((yourtable.DATEIN)>#1/1/2002#));

(To select previous year)
SELECT DISTINCTROW yourtable.DATEIN, yourtable.company, Format$([yourtable].[DATEIN],&quot;mmmm yyyy&quot AS [DATEIN By Month], Sum(yourtable.PRICE) AS [Sum Of PRICE]
FROM yourtable
GROUP BY yourtable.DATEIN, yourtable.company, Format$([yourtable].[DATEIN],&quot;mmmm yyyy&quot, Year([yourtable].[DATEIN])*12+DatePart(&quot;m&quot;,[yourtable].[DATEIN])-1
HAVING (((yourtable.DATEIN)>#1/1/2001# And (yourtable.DATEIN)<#1/1/2002#));
----------------
(Crosstab query for first SQL)

TRANSFORM Sum(1stqname.[Sum Of PRICE]) AS [The Value]
SELECT 1stqname.company, Sum(1stqname.[Sum Of PRICE]) AS [Total Of Sum Of PRICE]
FROM 1stqname
GROUP BY 1stqname.company
PIVOT 1stqname.[DATEIN By Month];

(Crosstaba query for 2nd SQL)
The following SQL requires the &quot;2ndqname query&quot;

TRANSFORM Sum([2ndqname Query].SumOfPRICE) AS [The Value]
SELECT [2ndqname Query].company, Sum([2ndqname Query].SumOfPRICE) AS [Total Of SumOfPRICE]
FROM [2ndqname Query]
GROUP BY [2ndqname Query].company
PIVOT [2ndqname Query].[DATEIN By Month];

This gives you the total for the year for each company, and totals for each month for the year selected.

Make a report
Reporttab
new
report wizard and select the 1st crosstab query.
select
company
totalofsumofprice
month you want.

The report will list;
company Totalofsumofprice month
compa 123,456.78 16,123.00
compb 144,234.12 31,345.19
compc 2,210.59 241.95
(The sales rep really needs to pay more attention to compc)

The only problem is you have to change the month in the report for each succeeding month.

Does anyone know how to default to the present month?

I finally figured out how to get the last years totals. They will be in the report footer but they are there...
Create another report based on the 2nd crosstab SQL and use only one field.&quot;totalof sum of price&quot;
Open the first report in design view and drag the new report to the reportfooter.
Adjust position as needed and add labels.

Hope this gives you something like what you are after.
Jim
10/18/02 8:40am PDT
I tried to post this but got a server error will try in a few minutes