comparing tables

[Jenn]

I need some help with how to set up a comparison type query. I have a
database that has a table called Employees.

The Table has the following Fields:
Employee Name
Employee Number
Salary Band
Occupation Title

This information comes from our corporate system and a report is generated
in either a text or excel file. Every quarter we run this report and I import
it into Access.

What I would like to do is be able to import this file into Access as a
“new†Employee table (it can be brought in either as a txt file or excel
file) and I do not care what it is called. Then I would like to run a query
that shows me if there are any difference between the new employee table that
was just imported and the old employee table that exists.

Specifically I would like to know if there are any new employee records in
this new table, any records that do not exist in the new table that exist in
the old table, and finally if there were any changes to the current records
(i.e., an employee salary band has changed). I would also like to know what
these differences are. Is there a query that can do this?
TIA!

 

['69 Camaro]

Is there a query that can do this?

Both of these tables must include at least one column that is never changed.
Otherwise, there's no way to match the records in one table with the
corresponding records in the other table. Both the old and new values of
each field must be compared to each another. In the following example, the
Employee Number is assumed to be the primary key that never changes.

SELECT Employees.[Employee Number], NewEmps.[Employee Number],
Employees.[Employee Name], NewEmps.[Employee Name],
Employees.[Salary Band], NewEmps.[Salary Band],
Employees.[Occupation Title], NewEmps.[Occupation Title]
FROM NewEmps RIGHT JOIN Employees ON NewEmps.[Employee Number] =
Employees.[Employee Number]
WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
(((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
(((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
(ISNULL(NewEmps.[Employee Name]) AND ISNULL(NewEmps.[Salary Band]) AND
ISNULL(NewEmps.[Occupation Title]))
UNION
SELECT Employees.[Employee Number], NewEmps.[Employee Number],
Employees.[Employee Name], NewEmps.[Employee Name],
Employees.[Salary Band], NewEmps.[Salary Band],
Employees.[Occupation Title], NewEmps.[Occupation Title]
FROM NewEmps LEFT JOIN Employees ON NewEmps.[Employee Number] =
Employees.[Employee Number]
WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
(((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
(((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
(ISNULL(Employees.[Employee Name]) AND ISNULL(Employees.[Salary Band]) AND
ISNULL(Employees.[Occupation Title]));

How to read the comparisons:

If the Employees.[Employee Number] is NULL, then it's a new record in NewEmps.
If the NewEmps.[Employee Number] is NULL, then the record only exists in
Employees.
If Employees.[Employee Number] equals NewEmps.[Employee Number], then
something in that record has changed, so compare the columns, old with new to
find out what the change is.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Ofer]

So, you are the first one.
Good on you

Is there a query that can do this?

Both of these tables must include at least one column that is never changed.
Otherwise, there's no way to match the records in one table with the
corresponding records in the other table. Both the old and new values of
each field must be compared to each another. In the following example, the
Employee Number is assumed to be the primary key that never changes.

SELECT Employees.[Employee Number], NewEmps.[Employee Number],
Employees.[Employee Name], NewEmps.[Employee Name],
Employees.[Salary Band], NewEmps.[Salary Band],
Employees.[Occupation Title], NewEmps.[Occupation Title]
FROM NewEmps RIGHT JOIN Employees ON NewEmps.[Employee Number] =
Employees.[Employee Number]
WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
(((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
(((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
(ISNULL(NewEmps.[Employee Name]) AND ISNULL(NewEmps.[Salary Band]) AND
ISNULL(NewEmps.[Occupation Title]))
UNION
SELECT Employees.[Employee Number], NewEmps.[Employee Number],
Employees.[Employee Name], NewEmps.[Employee Name],
Employees.[Salary Band], NewEmps.[Salary Band],
Employees.[Occupation Title], NewEmps.[Occupation Title]
FROM NewEmps LEFT JOIN Employees ON NewEmps.[Employee Number] =
Employees.[Employee Number]
WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
(((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
(((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
(ISNULL(Employees.[Employee Name]) AND ISNULL(Employees.[Salary Band]) AND
ISNULL(Employees.[Occupation Title]));

How to read the comparisons:

If the Employees.[Employee Number] is NULL, then it's a new record in NewEmps.
If the NewEmps.[Employee Number] is NULL, then the record only exists in
Employees.
If Employees.[Employee Number] equals NewEmps.[Employee Number], then
something in that record has changed, so compare the columns, old with new to
find out what the change is.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I need some help with how to set up a comparison type query. I have a
database that has a table called Employees.

The Table has the following Fields:
Employee Name
Employee Number
Salary Band
Occupation Title

This information comes from our corporate system and a report is generated
in either a text or excel file. Every quarter we run this report and I import
it into Access.

What I would like to do is be able to import this file into Access as a
“new†Employee table (it can be brought in either as a txt file or excel
file) and I do not care what it is called. Then I would like to run a query
that shows me if there are any difference between the new employee table that
was just imported and the old employee table that exists.

Specifically I would like to know if there are any new employee records in
this new table, any records that do not exist in the new table that exist in
the old table, and finally if there were any changes to the current records
(i.e., an employee salary band has changed). I would also like to know what
these differences are. Is there a query that can do this?
TIA!

 

['69 Camaro]

So, you are the first one.

Nah. Eighth. Out of 900 newsgroups for the English language, these seven
people have already earned one silver badge each since the inception of the
badge program in June of '04:

David Bartosik (MVP), Publisher, 11/7/2004
garfield-n-odie (MVP), Word, 12/3/2004
Alejandro Mesa, SQL Server, 3/22/2005
Jim Thomlinson, Excel, 5/25/2005
Jocelyn Fiorello (MVP), Outlook, 7/22/2005
Duke Carey, Excel, 8/29/2005
CyberTaz, Word, 9/29/2005

However, in the next month, we should have four people who have earned at
least one silver badge in the Access newsgroups. And by the end of the
year, you will probably have earned four silver badges and three bronze
badges.

As you can see, it's quite unprecedented. So, good on you!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

So, you are the first one.
Good on you

Is there a query that can do this?

Both of these tables must include at least one column that is never
changed.
Otherwise, there's no way to match the records in one table with the
corresponding records in the other table. Both the old and new values of
each field must be compared to each another. In the following example,
the
Employee Number is assumed to be the primary key that never changes.

SELECT Employees.[Employee Number], NewEmps.[Employee Number],
Employees.[Employee Name], NewEmps.[Employee Name],
Employees.[Salary Band], NewEmps.[Salary Band],
Employees.[Occupation Title], NewEmps.[Occupation Title]
FROM NewEmps RIGHT JOIN Employees ON NewEmps.[Employee Number] =
Employees.[Employee Number]
WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
(((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
(((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
(ISNULL(NewEmps.[Employee Name]) AND ISNULL(NewEmps.[Salary Band]) AND
ISNULL(NewEmps.[Occupation Title]))
UNION
SELECT Employees.[Employee Number], NewEmps.[Employee Number],
Employees.[Employee Name], NewEmps.[Employee Name],
Employees.[Salary Band], NewEmps.[Salary Band],
Employees.[Occupation Title], NewEmps.[Occupation Title]
FROM NewEmps LEFT JOIN Employees ON NewEmps.[Employee Number] =
Employees.[Employee Number]
WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
(((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
(((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
(ISNULL(Employees.[Employee Name]) AND ISNULL(Employees.[Salary Band])
AND
ISNULL(Employees.[Occupation Title]));

How to read the comparisons:

If the Employees.[Employee Number] is NULL, then it's a new record in
NewEmps.
If the NewEmps.[Employee Number] is NULL, then the record only exists in
Employees.
If Employees.[Employee Number] equals NewEmps.[Employee Number], then
something in that record has changed, so compare the columns, old with
new to
find out what the change is.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.

I need some help with how to set up a comparison type query. I have a
database that has a table called Employees.

The Table has the following Fields:
Employee Name
Employee Number
Salary Band
Occupation Title

This information comes from our corporate system and a report is
generated
in either a text or excel file. Every quarter we run this report and I
import
it into Access.

What I would like to do is be able to import this file into Access as a
"new" Employee table (it can be brought in either as a txt file or
excel
file) and I do not care what it is called. Then I would like to run a
query
that shows me if there are any difference between the new employee
table that
was just imported and the old employee table that exists.

Specifically I would like to know if there are any new employee
records in
this new table, any records that do not exist in the new table that
exist in
the old table, and finally if there were any changes to the current
records
(i.e., an employee salary band has changed). I would also like to
know what
these differences are. Is there a query that can do this?
TIA!