Combining Data from numerous records into one field????????

[lucky33]

I posted this before but I am not sure that I explained very well what
I need Access to do.

I have created a database that keeps track of the loaders of the truck
and the damages they cause

The talble has the following fields:

Loader 1
Loader 2
# Cases Shipped
# Damaged Cases

On the input form I have two list boxes of the employees that could
load the truck. One for loader 1 and one for loader 2.

I am calculating the percentage on the report. The problem I am having
I need the total numbers for an employee no matter to which loader he
is

For Example:

Loader 1 Loader 2 # cases
shipped # Damaged Cases

Dan
Mike 3000
100
Joe
Dan 1500
50

If the above is the table in the field I would like for the report to
look like below

Loader # Cases
shipped # Damaged Cases

Dan
4500 150

Can this be done?

 

[tina]

suggest you start by re-examining your tables structure. storing data in
fieldnames (Loader1, Loader2) breaks normalization rules, makes it harder to
work with your data, and is not flexible - you can *never* enter more than
two loaders for a single truckload without essentially rewriting the entire
database.

if multiple persons load a single truck, then i'm assuming that each loader
gets equal credit for the number of cases loaded to a single truck, and each
one is held equally accountable for the number of damaged cases in a load.
in that scenario, the tables might look like the following, as

tblEmployees
EmpID (primary key)
FirstName
LastName
<this would be a list of the persons who work as truck loaders

tblLoads
LoadID (primary key)
Truck (if you need to identify the truck that received each load)
LoadDate
Quantity (number of cases loaded altogether)
Damaged (number of cases damaged)

tblLoadWorkers
LoadID (foreign key from tblLoads)
EmpID (foreign key from tblEmployees)
<using the two fields as a combination primary key, you can enter as many
loadworkers as worked on a single load; and you cannot accidentally list the
same employee twice for the same load.>

with the above table structure, it's easy enough to write a query that pulls
the number of cases and number damaged for each loadworker.

hth