|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
Hi Ray,
You might want to consider using Total Access Detective, by FMS:
http://www.fmsinc.com/products/detective/index.html
It can be used to find differences in the data in tables. I don't know if
this product allows programmatic control; that would be a question for the
vendor, FMS.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
"Ray Cacciatore" wrote:
[Quoted Text] > 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 Cacciatore wrote:
[Quoted Text] > I need to compare the data from the two datasheets to catch any
> differences between them.
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.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200609/1
|
|
|