Comparing datasheets

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

Guest

I have a main form with two subforms (both in datasheet view). Both have the
same columns. I need to compare the data from the two datasheets to catch any
differences between them. The problem is that these subforms can have
hundreds of rows and there are 14 columns in each subform.

To give a bit more detail. The left subform has data that will be imported,
the right subform has data that already was imported. If there is no
difference between them, then I don't import. So I need a way to find if they
are different. Is there a way to do this? Even programmatically.

Ray
 
Ray said:
I need to compare the data from the two datasheets to catch any
differences between them.
Click to expand...

Hon, you don't need to spend 200 bucks on a software tool. You can write
your own query and check the results of the query to determine what the
differences are. You need to use a full outer join on the primary key. Jet
doesn't support full outer joins, but you can create the equivalent by using
a left outer join and a right outer join, and then using a union of the two.
For this example, we can use the following table structures (although queries
can be used as the data sets, too):

CREATE TABLE tblEquipment
(SerialNum Text (8) NOT NULL,
Nomenclature Text (50) NOT NULL,
Location Text (255) NOT NULL,
CONSTRAINT PrimaryKey Primary Key (SerialNum));

CREATE TABLE tblNewEquip
(SerialNum Text (8) NOT NULL,
Nomenclature Text (50) NOT NULL,
Location Text (255) NOT NULL,
CONSTRAINT PrimaryKey Primary Key (SerialNum));

tblEquipment is the existing table and tblNewEquip is the data set to be
imported if there are any differences. Running the following query will
result in only the different records being returned:

SELECT Eq.SerialNum, NE.SerialNum,
Eq.Nomenclature, NE.Nomenclature,
Eq.Location, NE.Location
FROM tblEquipment AS Eq LEFT JOIN tblNewEquip AS NE
ON Eq.SerialNum = NE.SerialNum
WHERE (ISNULL(NE.SerialNum)) OR
(Eq.Nomenclature <> NE.Nomenclature) OR
(Eq.Location <> NE.Location)
UNION
SELECT Eq.SerialNum, NE.SerialNum,
Eq.Nomenclature, NE.Nomenclature,
Eq.Location, NE.Location
FROM tblEquipment AS Eq RIGHT JOIN tblNewEquip AS NE
ON Eq.SerialNum = NE.SerialNum
WHERE (ISNULL(Eq.SerialNum)) OR
(Eq.Nomenclature <> NE.Nomenclature) OR
(Eq.Location <> NE.Location);

Programmatically (or with a separate query) you can count the number of
records returned by this query. If no records are returned, then both tables
are identical, and you don't need to import the new data set. If records are
returned, then here's how to read the results:

1) Eq.SerialNum is NULL: a new record is to be imported from the import
table (tblNewEquip).
2) NE.SerialNum is NULL: the imported records don't contain this record
that's in the existing table (tblEquipment).
3) Both Eq.SerialNum and NE.SerialNum have values: the other matching
columns have to be checked to see which ones don't have matching values, eg.
Eq.Nomenclature <> NE.Nomenclature, etc.
 
Back
Top