calculating number of days worked

[Brian R.]

I have a delivery order form that is the focus/heart of my novice database.
Drivers/employees rarely come to the office and therefore there are no formal
start/stop times/timecards. Instead, each driver is dispatched many orders
throughout the day. whist on the road. I also have an employeeForm with a
corresponding table where I would like to store the payroll info for each
driver. ie: number of days worked, number of orders done, average orders per
day, payrate per order, avg number of orders per day, etc. etc.
I'm having trouble figuring out the first 2 (number of days worked, number
of orders done) which are the basis for most other calculations. I've read
about subtracting the NowDate from the HireDate but that only tells me how
long they have been associated with the company and not How many days (based
on completed orderForms) they Actually worked. Also I would like to keep the
number of days worked as a running total somewhere, where up-to-the minute
payroll info could easily be seen for any employee. Any ideas/help would be
appreciated. As I'm a novice, the more detailed your suggestions, the better.
Thanks Brian.

 

[Ken Sheridan]

Brian:

The first thing is to make sure you have the necessary tables and the right
columns (fields) in each. In a relational database each table represents an
entity type and each column an attribute type of that entity. Its important
that there is no 'redundancy' i.e. the same fact is not stored more than once
in the database as other wise discrepancies (update anomalies in the jargon)
can arise. The processes of eliminating redundancy is known as
'normalization.

The first entity type in you case is Drivers so you need a table for them,
with one row per driver and with a unique primary key column, DriverID say,
which can be an autonumber as its actual value is not relevant. Don't use
names a s a primary key, they can be duplicated (I worked with two Maggie
Taylors once). Other columns in the Drivers table would be FirstName,
LastName, HireDate, daily PayRate (more about this later) etc.

Another entity type is Orders so you should have an Orders table, with
columns OrderID, DateShipped etc. To relate this to the Drivers table it
would also have a foreign key DriverID column, but this time a long integer
number, not an autonumber.

It would be possible to compute the days worked, orders per day etc solely
from these two tables, but it makes things a lot easier if you have a
Calendar table as well. This is simply a table of all dates over a date
range, 1 January 2007 to 31 December 2015 say, in a column CalDate. An easy
way to construct such a table is to serially fill down a column in Excel and
then import it into Access as a table. Alternatively it can be done in
Access with a VBA procedure (which I can post here if you wish).

When it comes to computing the payment values an important consideration is
that driver's rates of pay will change over time, so if you store the PayRate
solely in the Drivers table, when a driver's rate of pay changes any previous
orders delivered by that driver would not show the rate of pay when the order
was delivered, but the current rate of pay. What you have to do therefore is
to store the rate of pay at the time of the order in another PayRate column
in the Orders table, using the rate in the Drivers table to get the current
rate. You'll find an example of this in the Orders Subform in the sample
Northwind database, where the unit price of a product is looked up from the
Products table and stored in the Order Details table.

To get information out of the data stored in these tables you use queries.
For number of days worked per driver:

SELECT DriverID, FirstName, LastName,
COUNT (*) As DaysWorked
FROM Drivers, Calendar
WHERE EXISTS
(SELECT *
FROM Orders
WHERE Orders.DriverID = Drivers.DriverID
AND DateShipped = Calendar.CalDate)
GROUP BY DriverID, FirstName, LastName;

The way this works is that the outer query joins every row in Drivers to
every row in Calendar. This is what's known as a CROSS JOIN and returns
what's known as the Cartesian product of the two tables. The subquery then,
for every row returned by the outer query looks to see if at least one row
with a matching date exists in the Orders table for that driver on that date.
Only those where the subquery finds any rows are returned by the outer
query, which counts all the rows to give the number of days worked.

To return the average number of orders per day per driver is a question of
dividing the number of orders per driver in total by the number of days
worked by the driver, as returned by the above, so requires an extension of
the above query with another subquery, this time in its SELECT clause, to
count the number of orders per driver:

SELECT DriverID, FirstName, LastName,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.DriverID = Drivers.DriverID)/
COUNT (*) As AverageOrdersPerDay
FROM Drivers, Calendar
WHERE EXISTS
(SELECT *
FROM Orders
WHERE Orders.DriverID = Drivers.DriverID
AND DateShipped = Calendar.CalDate)
GROUP BY DriverID, FirstName, LastName;

To return the number of orders per day per driver is a simple query which
joins Drivers and Orders on DriverID, groups by the driver and shipped date
and counts the rows:

SELECT ShippedDate, DriverID, FirstName, LastName,
COUNT(*) AS OrdersHandled
FROM Drivers INNER JOIN Orders
ON Drivers.DriverID = Orders.DriverID
GROUP BY ShippedDate, DriverID, FirstName, LastName;

To return the pay cost per order, assuming a simple averaging per day as no
detailed time records are kept, you divide the number of orders per day per
driver by the pay rate stored in the Orders table (not that in the drivers
table) which is an extension of the above:

SELECT OrderID, ShippedDate,
(SELECT COUNT(*)
FROM Orders AS O2
WHERE O2.ShippedDate = O1.ShippedDate
AND O2.DriverID = O1.DriverID)/
PayRate AS PayCost
FROM Orders AS O1;

Note that in this case the two instances of the Orders table in the outer
query and subquery are distinguished from each other by giving each an alias,
O1 and O2. This allows the subquery to be 'correlated' with the outer query
on the ShippedDate and DriverID columns.

For a running total of days you can use a variation on the first query, this
time including a subquery in its SELECT clause which counts the number of
days where the date is on or after the earliest order date for the driver (by
means of another subquery) and there is at least one order delivered by the
driver on each day. As the outer query does not include any aggregation
operations its not necessary to group the query this time, but it can be
ordered:

SELECT CalDate, DriverID, FirstName, LastName,
(SELECT COUNT (*)
FROM Calendar AS C2
WHERE C2.CalDate <= C1.Caldate
AND C2.Caldate >=
(SELECT MIN(OrderDate)
FROM Orders
WHERE Orders.DriverID = Driver.DriverID)
AND EXISTS
(SELECT *
FROM Orders
WHERE Orders.DriverID = Drivers.DriverID
AND DateShipped = C2.CalDate))
AS DaysWorkedToDate
FROM Drivers, Calendar AS C1
WHERE EXISTS
(SELECT *
FROM Orders
WHERE Orders.DriverID = Drivers.DriverID
AND DateShipped = C1.CalDate)
ORDER BY CalDate, LastName, FirstName, DriverID;

Ken Sheridan
Stafford, England

 

[John W. Vinson]

I have a delivery order form that is the focus/heart of my novice database.
Drivers/employees rarely come to the office and therefore there are no formal
start/stop times/timecards. Instead, each driver is dispatched many orders
throughout the day. whist on the road. I also have an employeeForm with a
corresponding table where I would like to store the payroll info for each
driver. ie: number of days worked, number of orders done, average orders per
day, payrate per order, avg number of orders per day, etc. etc.
I'm having trouble figuring out the first 2 (number of days worked, number
of orders done) which are the basis for most other calculations. I've read
about subtracting the NowDate from the HireDate but that only tells me how
long they have been associated with the company and not How many days (based
on completed orderForms) they Actually worked. Also I would like to keep the
number of days worked as a running total somewhere, where up-to-the minute
payroll info could easily be seen for any employee. Any ideas/help would be
appreciated. As I'm a novice, the more detailed your suggestions, the better.
Thanks Brian.

A Totals query will do this, based on the dispatch table. Could you
describe your tables? You are - I hope!! - using separate tables for
the Employee and for orders? What is the structure of these tables?

Note that you need not and SHOULD not store the running total. Once
you store it to disk it becomes instantly INCORRECT the moment you add
a new order for that employee! Instead, calculate it on the fly - not
from the Employees table, but from the orders. If you'll post the
relevant fields from the table one of us can help construct the needed
query.

John W. Vinson [MVP]

 

[Brian R.]

Thank-you Gentlemen,
The relevant fields in the OrdersTbl are:OrderId(autonum),
CustomerId(number), EmployeeID(number), OrderDate(Date/Time),
OrderTime(Date/Time),OrderTakenBy(Text), DispatchedBy(Text),
1stStopChrg(Currency), XtraStopChrgs(Cur), OtherChrgs(Cur), OrderTotal(Cur),
CompanyCallFee(Cur), CompanyCallXtraFees(Cur), DriverCallFee(Cur),
DriverCallXtraFees(Cur).

Driver and CompanyCallFees are a proportioning of the delivery fees
collected. ie:if the customer is charged $6 for 1st stop and $2 for 2nd stop
for a total of $8, the company may collect, say, $2 for the 1st stop and $1
for the 2nd stop with the driver reciveing the balance. I'd like to be able
to see the running breakdown totals(not necessarily stored), of each
employee as well as the companyies earnings for any given time period.

The relevant fields in the EmployeeTbl are:EmployeeID(autonum),
#ofShiftsWorked(Num), CallRate(Cur), CallXtraStopRate(Cur),
#ofCallsCompleted(Num), #ofCancellations(Num), #ofNoServices(Num),
Total#ofCalls(Num), Total#ofXtraStops(Num), #ofComplaints(Num),
TotalEarningsForCallsCompleted(Cur), TotalEarningsForXtraStopsMade(Cur),
OtherEarnings(Cur), TotalBaseEarnings(Cur), AvgCallsPerShift(Num),
AvgEarningsPerShift(Cur), AvgEarningsPerCall(Cur).

These are the fields I need help with. Are the querys/formulas to be
assigned in the tables or on the forms properties? Thanks again for your
advice. Brian.

PS: the orderstbl fields: OrderTakenBy and DispatchedBy may not be relevant
at this time but will be later, for an unrelated follow up question. Also if
you have any doubt, this is for a small local residential Liquor Delivery
Company. Unlike other companies, my Customer orderform is based on customer
addresses. Phone numbers are not feasible for me to track customers as they
often call from different locations with different phone numbers(both cell
and landlines) and some folks even use payphones to order. Therefore the
address is the only part of my business that stays-put and is what I use to
track sales. Unfortunately this may/can and will result in problems with
different street spellings from different order takers but I can not see
anyway around this. Can you?

 

[Jamie Collins]

It would be possible to compute the days worked, orders per day etc solely
from these two tables, but it makes things a lot easier if you have a
Calendar table as well.

To get information out of the data stored in these tables you use queries.
For number of days worked per driver:

SELECT DriverID, FirstName, LastName,
COUNT (*) As DaysWorked
FROM Drivers, Calendar
WHERE EXISTS
(SELECT *
FROM Orders
WHERE Orders.DriverID = Drivers.DriverID
AND DateShipped = Calendar.CalDate)
GROUP BY DriverID, FirstName, LastName;

Apologies if this is a duplicate.

Your 'unfiltered' CROSS JOIN between drivers and calendar is expensive. With
a modest 830 row orders table (derived from Northwind), my regular 11K row
(30 years) calendar table and a single row drivers table, your query took
just over 11 seconds to execute on my machine. With a 100 row drivers table,
I had to kill the process

I'm not sure whether the calendar table is justified here. A calendar table
is useful for plugging gaps in dates e.g. count the number of dates between a
start date and an end date.

Why not indeed just count distinct shipped dates? e.g.

SELECT D2.DriverID, D2.FirstName, D2.LastName, COUNT(*) AS DaysWorked
FROM Drivers AS D2,
(
SELECT O1.DateShipped, D1.DriverID
FROM Orders AS O1, Drivers AS D1
WHERE O1.DriverID = D1.DriverID
AND O1.DateShipped IS NOT NULL
GROUP BY O1.DateShipped, D1.DriverID
) AS DT1
WHERE D2.DriverID = DT1.DriverID
GROUP BY D2.DriverID, D2.FirstName, D2.LastName;

Jamie.

--

 

[Jamie Collins]

Its important
that there is no 'redundancy' i.e. the same fact is not stored more than once
in the database as other wise discrepancies (update anomalies in the jargon)
can arise.

Other columns in the Drivers table would be FirstName,
LastName, HireDate, daily PayRate

When it comes to computing the payment values an important consideration is
that driver's rates of pay will change over time, so if you store the PayRate
solely in the Drivers table, when a driver's rate of pay changes any previous
orders delivered by that driver would not show the rate of pay when the order
was delivered, but the current rate of pay. What you have to do therefore is
to store the rate of pay at the time of the order in another PayRate column
in the Orders table, using the rate in the Drivers table to get the current
rate.

Sorry if this gets duplicated.

"Have to"?

I'd consider the driver's rate as being an attribute of the payroll and
model it as a history table with a start and end date for each rate for each
driver. Repeating the driver's rate in the orders table would therefore be
redundant. However, it may be possible to 'override' the driver's normal rate
on an individual order basis, in which case storing the rate in the orders
table is correct. Maybe there is no flat rate at all...?

I don't think there is a solution to be *prescribed* here.

Jamie.

--

 

[Ken Sheridan]

Brian:

I think you need to reconsider the appropriate logical model for your
database. Your OrdersTbl table for instance exhibits a number of flaws:

1. The OrderTotal is presumably the sum of the other three charges columns,
so can be derived from them at any time, either with a computed column in a
query or a computed control in a form or report. As it stands the total
value could be inconsistent with the sum of its parts, so as well as being
unnecessary, therefore, the existence of this column in the table gives rise
to potentially erroneous data being stored.

2. Having the three different charges columns in the table is in itself
open to question. A better solution would be to have a separate Charges
table with a foreign key OrderID column, a ChargeAmount column and a
ChargeType column, so for three distinct charges for one ordre the table
would have three rows. You could of course have as few or as many different
charges per order as necessary, each with a different ChrageType. You'd need
a separate ChargeTypes table as well referenced by the Charges table.

3. Do the the OrderTakenBy and DespatchedBy columns contain employee names?
If so it would be far better for them to be long integer numbers as foreign
keys referencing the primary key EmployeeID column of the EmployeeTbl. Names
can be duplicated, whereas a unique number always points to the one employee.

4. A les fundamental point, but you don't need separate OrderDate and
OrderTime columns. Date/Time values in Access are precisely that; a date
value can't exist without a time and vice versa. When you enter a date
without a time the value is midnight at the start of that day; a time entered
without a date is actually the time on 30 December 1899, which is day-zero in
Access's implementation of date/time values (the underlying value is in fact
a 64 bit floating point number). If you add the two together it does give
the correct date/time value, but its more efficient to store it as a single
value.

When it comes to your EmployeeTbl most of the columns represent aggregations
of values which will be available in the OrdersTbl table and the other
related tables outlined above, though you might well need more tables than
I've mentioned. Consequently they should be obtained via queries whenever
necessary and not stored as values in columns in the table. The queries,
once written, will give you the correct figures at any time, and can be the
basis of forms and/or reports.

As regards customers then the key (quite literally) to keeping track of them
is to have a unique unchanging CustomerID. I see that your OrdersTbl
includes such a column as a foreign key. This suggest that you do have a
Customers table, in which each 'attribute' of a customer, name, address, etc
is stored just once. Phone numbers are probably best in a separate table
with CustomerID foreign key column, PhoneNumber and NumberType column, the
last for home, office, mobile, fax etc as necessary.

I appreciate that the above may sound somewhat discouraging and that you
have already invested a lot of time and effort which I might seem to be
writing off, but it is crucial to the design of a successful database that
the logical model is correct and arrived at after a careful analysis of the
real world situation which the database is modelling. One thing which I'd
strongly recommend is that you draw the model out diagrammatically on paper
first, with boxes representing the tables and the lines between them
representing the relationships. Before doing so, however, its worth looking
at sample databases like the Northwind.mdb which comes with Access and seeing
how the business there is modelled by tables and relationships between them,
and how forms (some with subforms) are used to input data, and how the
information derived from the data is presented via queries, particularly when
used as the basis of reports. Also its well worth getting hold of a good
general book on Access. Theses usually come with sample files on CD which
toy can work through along with reading the text of the book. While these
will not be directly applicable to your business model, the important thing
is to get an understanding of the principles involved.

Ken Sheridan
Stafford, England

 

[Brian R.]

Brian:

I think you need to reconsider the appropriate logical model for your
database. Your OrdersTbl table for instance exhibits a number of flaws:

1. The OrderTotal is presumably the sum of the other three charges columns,
so can be derived from them at any time, either with a computed column in a
query or a computed control in a form or report. As it stands the total
value could be inconsistent with the sum of its parts, so as well as being
unnecessary, therefore, the existence of this column in the table gives rise
to potentially erroneous data being stored.

The Orderform is modeled after the NW. It contains a subform for products
just like the NW. The ordertotal is actually the total of the product +
delivery fee, which we use to quote to the customer for an approximate price
of their order. The products are somewhat irrelavant to me as we do not
stock anything. We merely pick whatever product a customer may order up from
a/any store, pay for it, and then tack on our delivery fee. We collect the
money from the customer at the door. I keep a table of known ordered products
with their last known price as a rough quide in quotes to a customer. The
OrderTotal field is primarily for the orderform. There is No data being
stored in that field in the table. Why then do I have such a field? I don't
know. But on the form, which is where I need it to, the orderstotal works
fine.

2. Having the three different charges columns in the table is in itself
open to question. A better solution would be to have a separate Charges
table with a foreign key OrderID column, a ChargeAmount column and a
ChargeType column, so for three distinct charges for one ordre the table
would have three rows. You could of course have as few or as many different
charges per order as necessary, each with a different ChrageType. You'd need
a separate ChargeTypes table as well referenced by the Charges table.

I'm not sure about this. The delivery charges and the proportions that go to
the driver and the company often vary from order to order and are necessary,
on the orderfrom as well as a table to keep track of respective earnings. Why
do I need a charge table when the employee&Order tables currently has all
those fields?

3. Do the the OrderTakenBy and DespatchedBy columns contain employee names?
If so it would be far better for them to be long integer numbers as foreign
keys referencing the primary key EmployeeID column of the EmployeeTbl. Names
can be duplicated, whereas a unique number always points to the one employee.

Yes they do contain names. As I earlier mentioned, they are a separate
problem I was planning on addressing later after I fiqured out the Breakdown
of Calculations part. Basically my problem is if I link them up with the
employeeID, only one employee gets credited with the order. ie: OrderTakenBy
and DespatchedBy and Dispatched To(which is currently set up to employeeId).
3 fields, 3 diff names but only one receives all the data. In other words, 3
emplyees but only 1 employee number shows up for all/every records.
Complicated to explain, thats why I thought I'd tackle it later.

4. A les fundamental point, but you don't need separate OrderDate and
OrderTime columns. Date/Time values in Access are precisely that; a date
value can't exist without a time and vice versa. When you enter a date
without a time the value is midnight at the start of that day; a time entered
without a date is actually the time on 30 December 1899, which is day-zero in
Access's implementation of date/time values (the underlying value is in fact
a 64 bit floating point number). If you add the two together it does give
the correct date/time value, but its more efficient to store it as a single
value.

Most if not all of our orders are initially taken by hand and later entered
into the database. We enter in the time/date the order was taken, which may
have been a day or two earlier. We need to enter them both separately. I will
be using the time of day and not necessarily the date for future frequent
calculations/predictions of busy time periods.

When it comes to your EmployeeTbl most of the columns represent aggregations
of values which will be available in the OrdersTbl table and the other
related tables outlined above, though you might well need more tables than
I've mentioned. Consequently they should be obtained via queries whenever
necessary and not stored as values in columns in the table. The queries,
once written, will give you the correct figures at any time, and can be the
basis of forms and/or reports.

This is my initial question.
What are the queries I need to calculate these fields?

As regards customers then the key (quite literally) to keeping track of them
is to have a unique unchanging CustomerID. I see that your OrdersTbl
includes such a column as a foreign key. This suggest that you do have a
Customers table, in which each 'attribute' of a customer, name, address, etc
is stored just once. Phone numbers are probably best in a separate table
with CustomerID foreign key column, PhoneNumber and NumberType column, the
last for home, office, mobile, fax etc as necessary.

Yes I do have a separate customer table and it seems to be correlated and is
working well in conjunction with the form. As I've said, I have one of the
few businesses where phone numbers are somewhat irrelavant. Customer names
are also irrelavant because alot of callers ordering alcohol do not give
their correct identity when placing an order. No, addresses are the key to my
business. I believe I'm stuck with the downfalls of using them.

I appreciate that the above may sound somewhat discouraging and that you
have already invested a lot of time and effort which I might seem to be
writing off, but it is crucial to the design of a successful database that
the logical model is correct and arrived at after a careful analysis of the
real world situation which the database is modelling. One thing which I'd
strongly recommend is that you draw the model out diagrammatically on paper
first, with boxes representing the tables and the lines between them
representing the relationships. Before doing so, however, its worth looking
at sample databases like the Northwind.mdb which comes with Access and seeing
how the business there is modelled by tables and relationships between them,
and how forms (some with subforms) are used to input data, and how the
information derived from the data is presented via queries, particularly when
used as the basis of reports. Also its well worth getting hold of a good
general book on Access. Theses usually come with sample files on CD which
toy can work through along with reading the text of the book. While these
will not be directly applicable to your business model, the important thing
is to get an understanding of the principles involved.

I have been self-studying deligently for the past few months. Many trips to
the library, online, bookstores. I find access to be fascinating and
frustrating. I do believe i have done a fairly good job in planning albiet
without any practical experience. There is more to my database than what I've
described. For the sake of complicity I tried to mention only the
fields/tables I felt were relevant to my problem. Thanks for your help and
advice. Brian.

 

[Ken Sheridan]

Brian:

The Orderform is modeled after the NW. It contains a subform for products
just like the NW. The ordertotal is actually the total of the product +
delivery fee, which we use to quote to the customer for an approximate price
of their order. The products are somewhat irrelavant to me as we do not
stock anything. We merely pick whatever product a customer may order up from
a/any store, pay for it, and then tack on our delivery fee. We collect the
money from the customer at the door. I keep a table of known ordered products
with their last known price as a rough quide in quotes to a customer. The
OrderTotal field is primarily for the orderform. There is No data being
stored in that field in the table. Why then do I have such a field? I don't
know. But on the form, which is where I need it to, the orderstotal works
fine.

If the OrderTotal is an unbound computed *control* on a form, or a control
bound to a computed column in the form's underlying query, that's fine, but
in your response to John you showed it as a column of currency data type in
the OrdersTbl table, which it should not be. But don't get too hung up on
the form; that's only a window on the underlying data. It’s how the data is
arranged, the 'logical model', which is the key. The forms and reports will
fall into place easily if the logical model is right.

I'm not sure about this. The delivery charges and the proportions that go to
the driver and the company often vary from order to order and are necessary,
on the orderfrom as well as a table to keep track of respective earnings. Why
do I need a charge table when the employee&Order tables currently has all
those fields?

The short answer is that this is how its done most easily and efficiently in
a relational database. Its not the presence of these multiple charge columns
in controls on the form that's the issue, it’s having multiple columns for
the various different types of charges in the table. By having the charges in
a separate table related with a foreign key OrderID column the Orders form
can then have a charges subform in which each charge amount is entered in a
single column and the relevant charge type selected in another column, with
complete flexibility over the number and type of charges applied per order.
When aggregating the values you then work with a single column of currency
data. The total amount per order for instance is the Sum of the column's
values grouped by OrderID, the average amount per day for instance is the Avg
of the column grouped by order date (from the referenced Orders table), and
so on. If you want aggregated values for each driver then you group by
EmployeeID (again from the referenced Orders table).

Yes they do contain names. As I earlier mentioned, they are a separate
problem I was planning on addressing later after I fiqured out the Breakdown
of Calculations part. Basically my problem is if I link them up with the
employeeID, only one employee gets credited with the order. ie: OrderTakenBy
and DespatchedBy and Dispatched To(which is currently set up to employeeId).
3 fields, 3 diff names but only one receives all the data. In other words, 3
emplyees but only 1 employee number shows up for all/every records.
Complicated to explain, thats why I thought I'd tackle it later.

The point here is that the OrderTakenBy, DespatchedBy and DispatchedTo
columns should each contain an EmployeeID value, not a name because of the
non-uniqueness of names (remember my two Maggie Taylors – you might have only
one of her now but the second could come along any day!). When referencing
another table, Employees here, you should always reference its primary key,
not a non-key column. Take Maggie, if I referenced her by name in two rows
in a table, whether it’s the same Maggie in each or not, and then joined that
table to Employees on the name column(s) the query would return 4 rows and if
I summed any values each would be double counted, which might make my figures
look good, but would not be the truth. If I reference her by her EmployeeID,
however, then only two rows are returned, the figures are correct and I don't
have the District Auditor asking awkward questions.

Most if not all of our orders are initially taken by hand and later entered
into the database. We enter in the time/date the order was taken, which may
have been a day or two earlier. We need to enter them both separately. I will
be using the time of day and not necessarily the date for future frequent
calculations/predictions of busy time periods.

You can still have the time and date in one column. For computations based
on the time of day you can return the time of day from the single date/time
value with the TimeValue function. The issue is not an unduly significant
one however.

This is my initial question.
What are the queries I need to calculate these fields?

Lets take a simple one as an example, which hopefully will illustrate the
benefits of the Charges table with a single Amount column, the
TotalEarningsForCallsCompleted, which I assume to be the sum of all charges
for all orders per employee:

SELECT FirstName, LastName,
SUM(Amount) As TotalEarnings
FROM Employees INNER JOIN
(Orders INNER JOIN Charges
ON Orders.OrderID = Charges.OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY FirstName, LastName;

As you'll see this is a simple summing of the Amount column, whereas with
multiple columns the values from each would have to be added then summed.
That would not be too difficult in this case, but when it comes to more
complex aggregations then things could get very messy.

Ken Sheridan
Stafford, England

 

[Brian R.]

Ken,
It will take me a little time to absorb all your advice but I'll try to add
a ChargeTbl. to the mix. I'll no doubt have further questions to follow.
Thank-you for your time.
Brian.