Count off calculated fields

  • Thread starter Thread starter Flick Olmsford
  • Start date Start date
F

Flick Olmsford

We run Access 2003

I have a report for salespersons which lists all salepersons and the total
sales each one made for each week of the month. There are 5 fields on the
line, one per week of the month.

E.g. John Doe Wk1Sales Wk2Sales Wk3Sales Wk4Sale Wk5Sales

Each of the Wk#Sales textboxes get fed by a function that runs a dsum
function over the database.

All sales persons will appear whether or not they met the quota for every
week of the month

How do I calculate the number of salespersons who met their weekly quota for
ALL weeks of the month. Using the average over the month will not do as
they need to meet or exceed the quota every week to be in this count. I do
not want to run another function since this should be doable in the report
section events (I hope)
 
Flick Olmsford said:
We run Access 2003

I have a report for salespersons which lists all salepersons and the total
sales each one made for each week of the month. There are 5 fields on the
line, one per week of the month.

E.g. John Doe Wk1Sales Wk2Sales Wk3Sales Wk4Sale Wk5Sales

Each of the Wk#Sales textboxes get fed by a function that runs a dsum
function over the database.

All sales persons will appear whether or not they met the quota for every
week of the month

How do I calculate the number of salespersons who met their weekly quota for
ALL weeks of the month. Using the average over the month will not do as
they need to meet or exceed the quota every week to be in this count. I do
not want to run another function since this should be doable in the report
section events (I hope)
Click to expand...

I don't think you can do this in the report alone. You'll have to go back
to the query.
Whatever the weekly quota is, put a calculated field in the query of:

ExceedQuotaWk1:([Wk1]>#)
with # being the quota.

In the report you can put a textbox into the footer of:
=Count([ExceedQuotaWk1)]

You will have to create a calculated field and a textbox for each Week.
 
You can do this easily (and much more efficiently) if you have correctly
normalized tables underneath this data.

You will need tables like these:
a) Staff table, with fields for StaffID, Surname, FirstName, etc.
b) Sales table, with fields for StaffID, SaleDate, Amount, etc.
c) Target table, with fields like this:
- StaffID relates to Staff.StaffID (i.e. who this target is for)
- WeekStart Date/Time first date of the sales week (the Sunday)
- SalesTarget Currency how much this person should sell this week

1. Create a query with the Sales table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. Add the StaffID field to the grid.
Accept Group By in the Total row under this field.

4. In the Field row, enter:
WeekStart: DateValue([SaleDate]) - Weekday([SaleDate]) + 1
This returns the Sunday of the week, so you can match to target.
In the Total row under this field, accept Group By.

5. Add the Amount field to the grid.
In the Total row under this field, choose Sum.

6. Test: for each sales person, you should see their weekly total.
Save as (say) qryWeeklyStaffSales. Close this query.

7. Create a new query using the Target table and qryWeeklyStaffSales.
Join them on StaffID, i.e. drag Target.StaffID and drop onto
qryWeeklyStaffSales.StaffID.

8. Double-click the join line you just created.
Access pops up a dialog offering 3 options.
Choose the one that says:
All records from Target, and any matches from qryWeeklyStaffSales.
(This gives you an outer join, so the targets show up even if a staff made
no sales.)

9. Drag Target.WeekStart onto qryWeeklyStaffSales.WeekStart.
Change it to an out join. (The arrow heads are now both pointing the same
way.)

10. Drag down into the grid the fields you want to see, e.g.:
StaffID WeekStart SalesTarget SumOfAmount
Test: you can see whether the the person met their target or not.

11. (Optional) Type this expression into a new column in the Field row:
MissedTarget: Nz([SumOfAmount],0) < [SalesTarget]
This will show True (-1) or False (0)
 
Back
Top