queries to look up multiply and add

[gbpg]

I have a training database. I want to have a running sum of the costs related
to different types of training. The table that contains information which
includes the following fields:
employee_id - which is a number (that contains a leading zero if this matters)
first name
last name
trd_code - a charge out rate
orientation - a check box that is a yes/no field
orientation time - a field that contains a text field to enter the hours
per training session
orientation cost - a field that is in currency

I have many other fields that have the same structure for training (i.e.
training name that is yes/no, the time it takes, and the cost)

The employees also have charge out rate based on a code (for instance, T1 to
T7 and P1 to P7 and A1 to A3) each of these codes have a cost per hour i.e.
T1 is 90.00 and hours.

How could best create a query (that can then be put on a report) that:

looks up the total costs for each type of training (by checking which
employees has taken the training x the time it takes for the training x the
actual cost (which is can be 0 if internal or some other figure if external)
+ the charge out rate (I will call this the extended cost which means the
time it takes x the charge out rate)?

 

[Smartin]

I have a training database. I want to have a running sum of the costs related
to different types of training. The table that contains information which
includes the following fields:
employee_id - which is a number (that contains a leading zero if this matters)
first name
last name
trd_code - a charge out rate
orientation - a check box that is a yes/no field
orientation time - a field that contains a text field to enter the hours
per training session
orientation cost - a field that is in currency

I have many other fields that have the same structure for training (i.e.
training name that is yes/no, the time it takes, and the cost)

The employees also have charge out rate based on a code (for instance, T1 to
T7 and P1 to P7 and A1 to A3) each of these codes have a cost per hour i.e.
T1 is 90.00 and hours.

How could best create a query (that can then be put on a report) that:

looks up the total costs for each type of training (by checking which
employees has taken the training x the time it takes for the training x the
actual cost (which is can be 0 if internal or some other figure if external)
+ the charge out rate (I will call this the extended cost which means the
time it takes x the charge out rate)?

This is probably not the answer you want to hear but your database
design really needs some help. In my mind's eye it looks more like a
spreadsheet with employee details at the left and multiple column
groups to the right with information about multiple training types.
This is not a good way to put a relational database together, and it
will make the analysis you want to perform more difficult than it
needs to be. You might do better implementing this in a spreadsheet.

If you want to use Access or other database environment I urge you to
study a little on "database normalization". Several good pages will
come up in a Google search. Feel free to bring back any questions you
have (or better yet, post design questions to
microsoft.public.access.tablesdbdesign ).

 

[gbpg]

If I could start all over again I would do a lot of things differently.
Creating and maintaining a database is something I do after a do training and
supporting 300 people, like at this hour.

Maybe a simpler question would be this:
I have a report that draws data on a report with this format a text box
called TextBox58 its control source has in it =-DSum"[xxxx
Training]","[Training xxxx]") this does calculate a value
I want to multiply the value in this TextBox58 x 20.0 dollars
What should I do for the control source?