Creating multiple records from one form entry... Help?

[Guest]

I need to create multiple records from a single form entry. I want to pull
info from 2 tables and calculate (and store) multiple records in a third
table. For example, Betty from the Accounting department does some work for
the Shipping department. Accounting has to recover Betty's costs from
Shipping.

TblEmployees stores Betty's name, charge rate and the coding for the
Accounting department. TblChargeto stores all the coding for the different
departments an employee could do work for (including the Shipping department
for the above example).

Unfortunately, each charge has to be categorized as 2 different kinds of
cost - salary and benefits (benefits are always 15% of salary) attached to
Betty's name and date, but I want to avoid having to enter Betty's name more
than once.

I know storing calculated values is discouraged, but these have to be stored
because pay rates aren't static. If Betty gets a raise today, I can't have
her previous work re-calculated at higher rates.

So, how do I buid a form that enters Betty's name and the department she did
work for in the header, and then create 4 records below that pulls in the
info necessary, kind of like this:

Main Section of Form:
Name: <select Betty>
Dept: <select Shipping>
Hours: <enter value>
Rate: <pulled from tblEmployee - based on selecting Betty above)

Generate record 1 (salary charge):
Name: Betty (pulled from above)
Charge to: Shipping (pulled from above)
Cost type: Salary
Cost amount: calculate (=Hours * Rate)

Generate record 2 (benefit charge):
Name: Betty (pulled from above)
Charge to: Shipping (pulled from above)
Cost type: Benefits
Cost amount: calculate (=Hours * Rate * 0.15)

Generate record 3 (salary recovery):
Name: Betty (pulled from above)
Charge to: Accounting (based on Betty's info in Employee table)
Cost type: Salary
Cost amount: calculate (=Hours * Rate * -1)

Generate record 4 (benefits recovery)
Name: Betty (pulled from above)
Charge to: Accounting (based on Betty's info in Employee table)
Cost type: Benefits
Cost amount: calculate (=Hours * Rate * 0.15 * -1)

I need to have this information stored in 4 separate records because of the
different ways we need to be able to report on the data later. I won't bore
you with it here, but just trust me on this one - it has to be 4 separate
records.

Help?

 

[tina]

i started to give you a detailed answer, klasher, but realized that i'd
either need to make a lot of assumptions and include an explanation of same
in my post, or go into an extended Q&A with you first. i don't have time to
do either, i'm afraid. instead, i can build a "quick and dirty" demo
database showing you one solution, which you can use as a model, and load it
to my website where you can download it and review it. if you would like to
do that, post back to let me know, and i'll set it up.

hth

 

[Guest]

Thanks, Tina. I did some more reading, and ended up building some queries
that manipulate the data for reporting and attached those to macros to
simplify the process. But I very much apprecaiate your response. =)

 

[tina]

good job! you often learn a lot more figuring out things on your own -
though it can be a painful process.