Help Need for Sales Comparison Report

[Terry]

I have a table of daily sales figures. Fields are:
WE (Numberic for Week Number)
Date (Date)
CashSales (Currency)
ChargeSales (Currency)
Credits (Currency)

What I need to be able to do is produce a report that shows a comparison of
all sales by Week Number from 2007 vs current 2008 year, grouped by Week
Numbers.

Example:

W/E 1

Date Cash Sales Charge Sales Credits
Date Cash Sales Charge Sales Credit
1-1-07 $100.00 $50.00 $0
1-2-08 $110.00 $65.00 $0
1-2-07 $200.00 $65.00 $0
1-3-08 $125.00 $50.00 $0

Etc, etc, etc

I'm stumped on how to start a report like this. Any suggestions would be
greatly appreciated.

Thanks
Terry

 

[Duane Hookom]

You could create two totals queries that group by Week Number, one for the
current year and the other for the previous year. Then join them together in
another query. You may want to use a join type that includes all the records
(week numbers) from the previous year query.

This should give you everything you need for your comparison report.

 

[Terry]

I have two queries created . One for 2007 and one for 2008. Each query
pulls from the same table. But now, I don't understand how to link these
two queries together to get the results that I need for the report.

Thanks,
Terry

 

[Duane Hookom]

Without seeing your 2 queries, I can only take a wag at:
SELECT c.*, p.*
FROM qselCurrent c RIGHT JOIN qselPrevious p on c.WeekNum = p.WeekNum;

 

[Terry]

Here are my two queries: Can you help please?

SELECT DISTINCTROW STCF_DailySales.Year, STCF_DailySales.WE,
STCF_DailySales.Date, STCF_DailySales.CashSales,
STCF_DailySales.ChargeSales, STCF_DailySales.OtherSalesCredits
FROM STCF_DailySales
GROUP BY STCF_DailySales.Year, STCF_DailySales.WE, STCF_DailySales.Date,
STCF_DailySales.CashSales, STCF_DailySales.ChargeSales,
STCF_DailySales.OtherSalesCredits
HAVING (((STCF_DailySales.Year)="2008"))
ORDER BY STCF_DailySales.Year, STCF_DailySales.WE;


and

SELECT DISTINCTROW STCF_DailySales.Year, STCF_DailySales.WE,
STCF_DailySales.Date, STCF_DailySales.CashSales,
STCF_DailySales.ChargeSales, STCF_DailySales.OtherSalesCredits
FROM STCF_DailySales
GROUP BY STCF_DailySales.Year, STCF_DailySales.WE, STCF_DailySales.Date,
STCF_DailySales.CashSales, STCF_DailySales.ChargeSales,
STCF_DailySales.OtherSalesCredits
HAVING (((STCF_DailySales.Year)="2007"))
ORDER BY STCF_DailySales.Year, STCF_DailySales.WE;

 

[Duane Hookom]

Your queries don't "group by Week Number" as suggested. If you want weekly
totals from two different years, you need to summarize the results by week.

 

[Terry]

What I want to achive is a report that has a group section. This group
section is the week number (example Week 1). Under this group section I
want to have two columns, on the left data for the year 2008 and on the
right data for the year 2007. In this grouping section I want to list all
the dates from the respective years that fall within this week. My table is
as follows:
Field 1=WE (Numberic for Week Number)
Field 2=Date (Date)
Field 3=CashSales (Currency)
Field 4=ChargeSales (Currency)
Field 5=Credits (Currency)

I can use the queries that I have now, one at a time to prepare two
different reports (one for 2007 and one for 2008), but I can't figure out
how to get them on the same report.

Thanks,
Terry

 

[Duane Hookom]

If you want to display to the daily detail level then I would create a single
query that has both the Year and Week Number as calculated columns. Create a
subreport based on this query.

Then create another query that contains only week numbers (no year, no date,
no nothing). Use this as the record source for a main report. In the detail
section of the main report, add a text box bound to the week number. Add two
more text boxes:
Name: txtCurrentYear
Control Source: =Year(Date())

Name: txtPreviousYear
Control Source: =Year(Date()) -1

Then add two copies of the same subreport to your main report side-by-side
in the detail section. Set the Link Master properties to:
txtCurrentYear, WeekNumber
and
txtPreviousYear, WeekNumber

Set both the Link Child properties to:
[YearField], [WeekNumber]