Hi everyone, here is one of the tougher problems ive had to deal with.

i have a number of fields, called teammember2, teammember3 etc..to teammember12. This is done because on the form, the user is able to select team members in addition to their original selection using a button.

Another field on the form is a "total days worked".

The problem now is adding the total days worked for each record based on the team member

for example:

if record 1 has : john smith and jane smith as team members
and record 2 has: john smith and tom smith as team members

i need the report (query) show the total days worked for john smith as record 1+ record 2. and for jane smith just record 1, tom smith would show total days for record 2.

im sorry for the weird explanation! i will be happy to clarify anything.

Here is your problem it seems:

You have one table with something like:

tblMyUnNormalizedTable
PKID
Job
teammember1
teammember2
teammember3
teammember4
teammember5
teammember6
teammember7
teammember8
teammember9
teammember10
teammember11
teammember12

This is not a good structure. You should have:

tblJob
JobID

tblEmployee
EmpID

tblJobs_Employee
PKID
JobID
EmpID
DaysWorked

Then this would be an easy issue.

That being said, what is the actual structure of the underlying table(s)? Can John Smith show up as TeamMember1 on one job and TeamMember2 on another and so on in an unnormalized table? Or is this properly normalized where it won't make a difference?

Lemme know.


mmcdonal

well you hit the nail on the head im afraid. that is the exact structure of my table. and yes john smith can come up where ever the user wishes, as the first, second, third etc team member on any given job.

the days worked are a seperate table, actually a few different tables, based on the products they are working on. for example if they input data for product1, on job1 and product1 on job2, then those values need to be added.

how should i structure my tables to make this possible. if i use ur idea, will the user still be able to select more than one teammember per job?

alright, i have normalized my database to help smooth things along. however i still cant figure out how to work my query.

i now have a seperate table for "jobs", "team members" , "workdays product1", "workdays product2" etc for all products.

the tables are structured so that for every job the user can input all the team members on the job, and indicate the number of days worked on each product.

how do i now add all the days worked on a product by team member as stated above?

ive tried using DSum, but without any success, as well as stored calculations within a query. any other ideas?

Thanks,
Jonas