Compare multiple fields on 2 similar tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm creating a process to compare like data from one month to another by
building a table for each month to compare one to the other. There are
multiple fields that can change from month-to-month. How can I return only
rows where one or more of these fields has changed from one month to the next?

thanks
 
Sure - here you go.

These fields are listed on both tables:

Employee ID
Employee Name
Security Group
Supervisor
Dept. Description
Job Title

Aside from Employee Name and ID, any of these can change from one month to
the next.
 
Thanks, Petz. Well, I am not sure that making a separate table for each
month is the wisest approach - but we'll leave that question aside.

Make a query that includes both tables, and join them on the Employee ID
field.

Make a calculated field in the query, like this...
Comparison: [Table1].[Security Group]=[Table1].[Security Group] And
[Table1].[Supervisor]=[Table2].[Supervisor] And [Table1].[Dept.
Description]=[Table2].[Dept. Description] And [Table1].[Job
Title]=[Table2].[Job Title]

In the criteria of this column put..
False

This should return all records where the values of any of these fields
are different for a given employee.

Did Access really allow you to put a . in the field name of Dept.
Description? I didn't think it was possible, and I would advise to
change this.
 
Thanks Steve - appreciate your help on this. I'll try your expression out. I
don't have the . in Access. Guess I just added that to my list out of habit.
Curiosity prompts me to ask what you see as the best approach other than
making the two tables. My primary purpose for that is to have a snapshot of
all data for the date I run the query. Internal Audit is the driving
force...what can I say??

petz

Steve Schapel said:
Thanks, Petz. Well, I am not sure that making a separate table for each
month is the wisest approach - but we'll leave that question aside.

Make a query that includes both tables, and join them on the Employee ID
field.

Make a calculated field in the query, like this...
Comparison: [Table1].[Security Group]=[Table1].[Security Group] And
[Table1].[Supervisor]=[Table2].[Supervisor] And [Table1].[Dept.
Description]=[Table2].[Dept. Description] And [Table1].[Job
Title]=[Table2].[Job Title]

In the criteria of this column put..
False

This should return all records where the values of any of these fields
are different for a given employee.

Did Access really allow you to put a . in the field name of Dept.
Description? I didn't think it was possible, and I would advise to
change this.

--
Steve Schapel, Microsoft Access MVP
Sure - here you go.

These fields are listed on both tables:

Employee ID
Employee Name
Security Group
Supervisor
Dept. Description
Job Title

Aside from Employee Name and ID, any of these can change from one month to
the next.
Click to expand...
Click to expand...
 
Petz,

This is sometimes known as the "tables as data trap". It is a symptom
of an un-normalised design. I don't know enough about your data and
your project at this stage to comment explicitly. But a more standard
design would be to store all the data in one table, with an additional
field to identify the month, and then use queries to extract your
selected month's data when needed. In practice, in this case, the end
result is likely to be very similar to what we have discussed, i.e. a
query based on 2 queries instead of a query based on 2 tables. But even
there, you could then use form-based criteria in these queries, which
would make it a lot easier to quickly do comparisons between any two
months you like, just by changing the criteria.
 
Back
Top