Creating Sales Reports
Build report from query
Obviously, to build a Sales report we'll need to first create a query.
The first report we'll build is the Sales by Rep report. It will show the sales for each rep for the last week (last 7 days, including today) and in a second column, the sales year-to-date.
If you're using the sample database to test the various reports, you'll notice that the dates will probably not match the current date that you're working with. You can either change your system date temporarily to something around 2006-03-10 or, run an update query on the SalesM table to set mDateSubmit up by a number of months to a date close to the current date.
At the request of the client, we want the sales report to show all the reps, even those that didn't have sales in the periods specified.
To do that we have to first create a query called a left join as in the picture below. We do that by specifying the join properties for the tables in the query (right-click on the join to get the join properties).
Then, we create a query to obtain the sales data.
Notice that this may be a bit inefficient because we always extract all the sales data from the tables, regardless of dates. The actual selection for the time frame is done in the report. There are obviously other ways of doing this. You should experiment with different techniques that would work better. In our case, there will never be huge quantities of data in the database so that this technique will serve for now.
The CPA, which is the actual commission calculation is done for every record in the table.
The IIF ( ) function is used to calculate CPA because a different field is used in the calculation depending on whether the Product is a "Mutual fund" or not.
The actual Sales report is based on the SalesbyRep query.
The important point to remember is that since it is a summary report (not every transaction is shown, only summaries) most of the printing is done in the Footer section of the report.
The CPA is calculated for each rep based on the transaction date and the current date. The client specified that he wanted summaries for the current week and current year-to-date and that is why it was done this way.
The other sales report are just variations on the same theme.
In all cases, Sorting and Grouping is very important to determine how the summaries will be calculated.
In the case of the SalesByPeriod report we added a Dates table to select the period for which the report will be printed. The Dates table is simplay a control table that simplifies the processing.