Sum row value across multiple columns

[Alaska]

I have a table that has, in part, a Team ID, a pass/fail quantity for each
team, and date. The “Team†table consists of the Team ID and 10 employees,
ie Team ID, EMP1, EMP2, EMP3 etc. The team data with unique EMP ID numbers
can be entered in any order from 1 to 10.

A query make from the tables linking the Team ID fields would look similar to:


Team ID, PASS QTY, FAIL QTY, EMP1, EMP2, EMP3 EMP4 DATE

111 10 2 E1 E2 E3 E4 Date 1
112 30 0 E2 E4 E1 E5 Date 2
113 20 5 E6 E1 E3 E2 Date 3

I would like to sum the PASS QTY and the FAIL QTY by each EMP to be similar
to the following within the date range Date 1 to Date 3:

EMP PASS QTY FAIL QTY

E1 60 7
E2 60 7
E3 30 7
E4 40 2
E5 30 0
E6 20 5

Can anyone point me in the right direction? My experience is limited.

 

[Guest]

For this, you would be better off to use Excel.
In a relational database, you would not have a table defined as you
described it. It would be impossilbe to maintain. What happens if an
Employee leaves or changes teams?
Access can do what you are wanting, but it is much easier in Excel.

 

[Alaska via AccessMonster.com]

Thanks for the quick response. I agree that excel would be more efficient.
Still, if at all possible, I would like to attempt to have access perform
this task. The query I described can be optained from the data base now.
The problem is extracting the data from this query into the format desired.
I can, as you state, export the information to excel and get the results
desired. In regard to the other questions, the employee table provides for
active/inactive employees and restricts inactive employees from the drop-down
lists (10) used to populate the unique Employee team table. The employee can
be on several unique teams.

Thanks

For this, you would be better off to use Excel.
In a relational database, you would not have a table defined as you
described it. It would be impossilbe to maintain. What happens if an
Employee leaves or changes teams?
Access can do what you are wanting, but it is much easier in Excel.

I have a table that has, in part, a Team ID, a pass/fail quantity for each
team, and date. The “Team†table consists of the Team ID and 10 employees,

[quoted text clipped - 22 lines]

Can anyone point me in the right direction? My experience is limited.