Comparing Records in a Table

[DW]

I am attempting to compare records in a table based upon a particular field
(employee field) to see if another field is equal (hours). There is another
field that will always be different (hours code). In other words, I want to
compare records where the employee is the same, with different hours codes,
and see if the hours match. Suggestions are appreciated. Thanks.

 

[John Spencer]

Need more details on what is in the table.

Many records per employee on many different dates?
Do you want to match a specific date or date range?

In other words, can you explain how you intend to match up two records to
compare them if you were doing this by hand.

For example,
The employee fields must match
The date field (if any) must match
The Hours codes must be different

You need to add the table TWICE to a query. Access will name the instances
YourTableName and YourTableName_1.

Join the two instances of the table on the employee field (and perhaps the
date field if needed).

Add the Hours Code field from YourTableName to the query grid and in the
criteria for the field enter
<>[YourTableName_1].[Hours Code]
Add the other fields you want to view.

You will get multiple lines with the same data. For instance if you have two
records with this data
Spencer:12/1/2008:7:xyz
Spencer:12/1/2008:6:xxx

You would get back
Spencer:12/1/2008:7:xyz Spencer:12/1/2008:6:xxx AND
Spencer:12/1/2008:6:xxx Spencer:12/1/2008:7:xyz

There are ways to remove that duplication, but it involves being able to
construct a query in the SQL window instead of in design view.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[a a r o n _ k e m p f]

there are lots of commercial software applications that allow you to
do this with SQL Server

or, I frequently use CHECKSUM in order to get a 'hash' of a database
record.
This makes it easy to compare 12 columns to see if something is
different


Select *
From Table1, Table2
Where Table1.RowNumber = Table2.RowNumber and Checksum(Table1.*) <>
Checksum(Table2.*)


Hope that helps-- I just dont' think that Access fits your needs

 

[Ken Sheridan]

Another way would be to return the rows where the employee and hours values
match, but the hours code values differ, as separate rows in the query's
result table. You can do this by using a subquery to identify the matches.
The matching rows can be grouped together in the result table by ordering it
on employee and hours. So the query would be along these lines:

SELECT *
FROM [YourTable] AS T1
WHERE EXISTS
(SELECT *
FROM [YourTable] AS T2
WHERE T2.[Employee] = T1.[Employee]
AND T2.[Hours] = T1.[Hours]
AND T2.[Hours Code] <> T1.[Hours Code])
ORDER BY [Employee], [Hours];

The two instances of the table are differentiated by the aliases T1 and T2,
which enables the subquery to be correlated with the outer query.
Consequently the outer query will return a row where at least one other row
exists with the same Employee and Hours values, but a different Hours Code
value. The ORDER BY clause will sort the results first by employee and then
by hours within each employee. Rows with matching hours per employee will
therefore be grouped together.

Ken Sheridan
Stafford, England

 

[DW]

For a given day, a given employee may have three records but there would be
at least two. I need to be able to add two and compare to the third. Hours
field from HoursCode1field + Hours field from HoursCode2 field = Hours field
for HoursCode3 field.

 

[Ken Sheridan]

Are HoursCode1field, HoursCode2field and HoursCode3field the names of three
columns in the table, or are they values of the same column? As you refer to
three 'records' I'm assuming it’s the latter (the former would be a badly
flawed design as it encodes data as column headings). Also, if there are
only two rows per employee per day, I'm assuming you'd be looking for equal
hours values in each, so if the values in the HoursCode column are in
ascending order per day, e.g. 1, 2 and 3 a query along these lines might do
it:

SELECT *
FROM [YourTable] AS T1
WHERE
(SELECT SUM([Hours])
FROM [YourTable] AS T2
WHERE T2.[Employee] = T1.[Employee]
AND T2.[WorkDate] = T1.[WorkDate]
AND T2.[HoursCode] <
(SELECT MAX([HoursCode])
FROM [YourTable] AS T3
WHERE T3.[Employee] = T1.[Employee]
AND T3.[WorkDate] = T1.[WorkDate]))
=(SELECT [Hours]
FROM [YourTable] AS T4
WHERE T4.[Employee] = T1.[Employee]
AND T4.[WorkDate] = T1.[WorkDate]
AND T4.[HoursCode] =
(SELECT MAX([HoursCode])
FROM [YourTable] AS T5
WHERE T5.[Employee] = T1.[Employee]
AND T5.[WorkDate] = T1.[WorkDate]))
ORDER BY [Employee], [WorkDate], [Hours];

However, the assumptions behind this might not be correct, in which case
we'd need more details of the table structure and the HoursCode values which
determine which are the rows to be added to which.

Ken Sheridan
Stafford, England

 

[James A. Fortune]

or, I frequently use CHECKSUM in order to get a 'hash' of a database
record.
This makes it easy to compare 12 columns to see if something is
different


Select *
From Table1, Table2
Where Table1.RowNumber = Table2.RowNumber and Checksum(Table1.*) <>
Checksum(Table2.*)

It's possible to have a set of changes result in the same Checksum as
the original. With your method you could get cases where changes have
been made, yet the method doesn't detect them.

Hope that helps-- I just dont' think that Access fits your needs

Hope that helps-- I just dont' think that SQL Server fits your needs

James A. Fortune
(e-mail address removed)