Weekly Report

[ipuff]

Hi,
I am trying to create a weekday report on the sales activity. I have a main sales_table. I am just using two field from the table. SalesDate and CRC (the CRC is the status of that record).
So i have a query that runs the counts of CRC by each sales day with Between [Enter Begin Sales Date] And [Enter End Sales Date]

This is query in SQL view:
SELECT Invoice.sales_date_internal, Count(Invoice.CRC) AS CountOfCRC, Invoice.CRC
FROM Invoice
GROUP BY Invoice.sales_date_internal, Invoice.CRC
HAVING (((Invoice.sales_date_internal) Between [Enter Begin Sales Date] And [Enter End Sales Date]) AND ((Invoice.CRC) Is Not Null));


Now I wanna create report in the following format:

Monday Tuesday Wednesday Thursday Friday Total
Sales 1 2 5 7 2 17
Cancel 3 1 3 2 0 9
Paid 3 1 3 2 1 10

Total 7 4 11 11 3 36

Can anyone please help me how to achieve this.

Thanks so much

 

[shudini]

Does your sales_date_interval return a date or day?

 

[ipuff]

hi,
it replies a date.

thanks

 

[shudini]

Your easiest option would be to create a pivot chart (if you use the AutoForm wizard it makes it easy for you). In either case, you need to modify the query if you want it to show you weekdays instead of dates (wouldn't you want to keep the field with a date though?)

Modify your query to the following:

SELECT Invoice.crc, Count(Invoice.crc) AS CountOfCRC, Format(([sales_internal_date]),"dddd") AS iDay
FROM Invoice
WHERE (((Invoice.sales_internal_date) Between [Enter Begin Sales Date] And [Enter End Sales Date]) AND ((Invoice.crc) Is Not Null))
GROUP BY Invoice.crc, Format(([sales_internal_date]),"dddd");

Save this query (i named it MyQuery).

Then create a crosstab query as the following:

PARAMETERS [Enter Begin Sales Date] DateTime, [Enter End Sales Date] DateTime;
TRANSFORM Sum(MyQuery.CountOfCRC) AS SumOfCountOfCRC
SELECT MyQuery.crc
FROM MyQuery
GROUP BY MyQuery.crc
PIVOT MyQuery.iDay;

 

[ipuff]

hi,
works like a charm. Thanks very much. I would show Date range on the top of the report.

How do you suggest I make a report from the this query. I mean its a cross tab query. How do i design report from that. If you could please suggest me something about that.

thanks in advance.

 

[shudini]

Reports are tricky from crosstab queries given that there is a dynamic number of columns returned depending on the data. As a result, you would need to use VBA to create a report. I don't know how comfortable with VBA, but if you don't mind experimenting a little, check out http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm. I hope this helps. I'm sure there are other ways, but this is how I would do it.

 

[ipuff]

hi shuie,
thanks very much. I'm not very proficient with VBA, so I guess, i wont be able to achieve a report. basically, I just wanted the report to show the report with the totals. Do you think I would be able to get the totals for each date in the bottom of the each column. can you please help me with getting the totals. If its possible i can simply write a preview query macro.

many many thanks

 

[shudini]

If that's all you wanted, why not try a PivotChart (use the PivotChart wizard). This way, it will come out looking like what you want. See my attachment.

 

[shudini]

Another option you have is, if you want to use days of the week as column headers, you can do the following;

PARAMETERS [Enter Begin Sales Date] DateTime, [Enter End Sales Date] DateTime;
TRANSFORM Sum(MyQuery.CountOfCRC) AS SumOfCountOfCRC
SELECT MyQuery.crc
FROM MyQuery
GROUP BY MyQuery.crc
PIVOT MyQuery.iDay in ("Monday","Tuedays","Wednesday","Thursday","Friday");

By doing this, you can now create a regular report, and in the report footer calculate the totals.

Good Luck!

 

[ipuff]

hi shuie,
thanks so much for all the help. this looks great.

cheers