Combining tables

[Guest]

This may or may not be an easy problem but I'm kind of learing Access as I go
here. I have data from two different operators for the same parts and I want
to combine it into one table as shown below. Can you help me on this? Thank
you.

Original Table Entered by Operator 1
Part Defect 1 Defect 2 Defect 3
A 1 0 1
B 1 0 0
C 0 1 0
D 0 0 0

Information Entered by Operator 2
Part Defect 1 Defect 2 Defect 3
A 1 1 0
B 0 0 0
C 1 0 0
D 1 1 0

Combined Finished Product
Part Defect 1 Defect 2 Defect 3
A 2 1 1
B 1 0 0
C 1 1 0
D 1 1 0

 

[Guest]

Josh,

If this data was in a normalized table, this would be relatively straight
forward (I am assuming that you have a lot more than 3 types of defects).
When I say normalized, what I mean is that rather than having a separate
column (field) in your table for each defect, what you should have is a table
that is structured somewhat like:

OperatorID Part_Num DefectType DateTimeIdentified

With this type of structure, you can create reports for a particular,
similiar to the data you have displayed below, or you can do an aggregate
across multiple operators; you could even filter the data for a specific date
or range of dates.

If you are stuck with the structure displayed, you could start by creating a
union query. It would look something like:

SELECT Part, [Defect 1], [Defect 2], [Defect 3]
FROM tableOperator1
UNION ALL
SELECT Part, [Defect 1], [Defect 2], [Defect 3]
FROM tableOperator2

Save that query as qry_UnionDefects

Now create an aggregate query that looks something like:

SELECT PART, SUM([DEFECT 1]) as Defect_1
, SUM([DEFECT 2]) as Defect_2
, SUM([DEFECT 3]) as Defect_3
FROM qry_UnionDefects
GROUP BY Part

HTH
Dale