Summing Different values

[uselessataccess]

Okay, this is probably really simple to do but as my login name suggests, im not very good with access. no formal training and all!

So i have set up two tables. In table A i have 4 identical fields where i can enter employee names (these are looked up from table B). Also in Table A amongst other things is a job time. Table B is simply the employees names and details.

So i want to be able to search for an emplyee by name and obtain his total working hours. The employee will not always been in the same field in table A. they could be in any of the four fields. Total hours would be the sum of all the individual "job time"s.

I have no idea where to start.

Any help would be much appreciated!!

Cheers,

C

 

[Kiwiman]

Howzit

Your setup for table A is wrong. You will need to structure it the following way or something like it

FYI - a <Foreign Key> <FK> is the Primary Key <PK> of another table

Table A setup
RecordKey <AutoNumber>
EmpID <FK>
JobID <FK>
JobDate <dateteime> Optional or other fields you require
Time

Determine your <PK> of this table - it could be a combination of EmpID,JobId and JobDate for example i.e what unique combinations do you require?

Set the <PK> for Table B as the EmpID.

You may also want to set up another table for the Job Details if you haven't done so. Make the <PK> the JobID

Create a 1:n relationship between table b (1) and table a , on the EmpID.

You can then build a query on the the one EmpID field in table A, and sum the time field