Comparing Tables

[Guest]

I need to be able to compare/combine two tables. The first table is imported
data with a field containing a job code of the form (AAAA-AAAAA). The other
table is inputed and has the same job code field but also has another
associated task field. What I need to do is compare the first table to the
second and combine the tables such that for every record that has a matching
job code field the task field is copied over. I also need a list generated
for every job code that does not have a task.

 

[Guest]

I have Table1 & Table2, both containing your "index" of AAAA-AAAAA.

I created an additional field in Table2 called (Data_From_Tbl1) wherein the
results from matching rows in Table 1 will go during the UPDATE query (see
below).

UPDATE Table2 INNER JOIN Table1 ON Table2.Tbl2_JobCode = Table1.Tbl1_JobCode
SET Table2.Data_From_Tbl1 = [Tbl1_Other];

This query is created using both tables, and dragging over the Tbl1_JobCode
to the Tbl2_JobCode - this will show only the results where the two codes
match.

 

[Guest]

Thank you. That works for my first problem but I also need a list of those
job codes that that don't have an associated task.

I have Table1 & Table2, both containing your "index" of AAAA-AAAAA.

I created an additional field in Table2 called (Data_From_Tbl1) wherein the
results from matching rows in Table 1 will go during the UPDATE query (see
below).

UPDATE Table2 INNER JOIN Table1 ON Table2.Tbl2_JobCode = Table1.Tbl1_JobCode
SET Table2.Data_From_Tbl1 = [Tbl1_Other];

This query is created using both tables, and dragging over the Tbl1_JobCode
to the Tbl2_JobCode - this will show only the results where the two codes
match.

I need to be able to compare/combine two tables. The first table is imported
data with a field containing a job code of the form (AAAA-AAAAA). The other
table is inputed and has the same job code field but also has another
associated task field. What I need to do is compare the first table to the
second and combine the tables such that for every record that has a matching
job code field the task field is copied over. I also need a list generated
for every job code that does not have a task.

 

[Guest]

By opening that same query in Design View, and choosing View Join Properties,
you can see 3 choices for joins.

Instead of Inner Join (where you looked for equal values between tables)
You'll now be creating LEFT and RIGHT JOINs, in order to find out the values
that do NOT match up.

Thank you. That works for my first problem but I also need a list of those
job codes that that don't have an associated task.

I have Table1 & Table2, both containing your "index" of AAAA-AAAAA.

I created an additional field in Table2 called (Data_From_Tbl1) wherein the
results from matching rows in Table 1 will go during the UPDATE query (see
below).

UPDATE Table2 INNER JOIN Table1 ON Table2.Tbl2_JobCode = Table1.Tbl1_JobCode
SET Table2.Data_From_Tbl1 = [Tbl1_Other];

This query is created using both tables, and dragging over the Tbl1_JobCode
to the Tbl2_JobCode - this will show only the results where the two codes
match.

I need to be able to compare/combine two tables. The first table is imported
data with a field containing a job code of the form (AAAA-AAAAA). The other
table is inputed and has the same job code field but also has another
associated task field. What I need to do is compare the first table to the
second and combine the tables such that for every record that has a matching
job code field the task field is copied over. I also need a list generated
for every job code that does not have a task.