Customer Sales Report in MS Access 2000

Recently i had to create a customer sales report in ms access 2000, well from the sound of it, it sounds pretty easy and standard way… but boy it came with so many conditions…

1) Client should be able to pick any range of dates and should compare with those date ranges for example he can pick from 01/01/07 to 12/31/07 vs 01/01/08 vs 12/31/08 . thats like pulling 2 annual sales report of the customer.

2) should be precise to the date, you cannot compare with month vs month

3) pretty looking…means when client look into it, he shouldnt scratch his head!!

4) client wants it fast

Now the problem with CUSTOMER sales report is that it should include all the customers within that date range, the issue is customers are added every day or month into the database… so a customer in 2008 will not be in 2007 and vise versa

I have 3 tables available, one customer table of course, 2nd invoice table, 3rd detail_invoice table

After alot of thought, i figure I should have a form created with drop down calendar to pick one date range and 2nd date range. this makes my life easier in working out reports and queries.

 Now i need 2 queries, subsequently these queries will be used in 2 sub reports. 

Date Range1 From and Date Range1 To will pull the data from one report , which will be used in Report1, similar for Report2

Doing so I can now view all the customer sales within those date ranges in both the reports, i am not missing anyone

Now I need a main report to incorporate these 2 sub report, so that matching customers are tagged together for easy comparison.

The main report query will have criteria to pull date ranges1 OR date ranges2

Ok so now we are having matching customers , now all we need is having calculations done to show:

1) per month per customer total

2) i have customer groups , so total per month per groups

3) total for the yr per customer, per group 

4) overall total per customer per group per yr 

After playing around i started using hidden text fields in reports that usese RUNNING SUM function in the properties box. This was a life saver.

To sort and group , I used the sorting and grouping in reports to sort by:

1) By  Groups

2) Customer ID

3) Sale Date 

4) Per Year

5) Per Month

My final report looks like this:

 

Since i used the Month function to get the month per sale date, Month(SaleDate), i only got numerical values like 1,2,3 etc till 12. To make this user friendly, i used the simple iif statements:

IIf([mot]=1,”January”,IIf([mot]=2,”February”,IIf([mot]=3,”March”,IIf([mot]=4,”April”,

IIf([mot]=5,”May”,IIf([mot]=6,”June”,IIf([mot]=7,”July”,IIf([mot]=8,”August”,

IIf([mot]=9,”September”,IIf([mot]=10,”October”,IIf([mot]=11,”November”,IIf([mot]=12,”December”,”Error”))))))))))))