Hi all,
I have 2 tables say t_OLD and t_NEW. The new has
corrections for audit purposes.
They are identical in all respects (i.e. new is a copy of
old and then changes are made to t_new)
I would like a quick way to cycle through all the the fields
in each table and compare the values to see if there are
differences.
I would like to AVOID having to hardcode a comparison of
each and every fieldname pair -- perhaps a public TABLE
compare function that would generate a list of all records
and the differences between the values in each pair.
Any thoughts on how to approach this are most welcome.
If anyone has done something like this I would appreciate
hearing about your strategy. 4 52850
Why not create a query with the IDs from both tables joined and then use
StrComp to compare strings that are presumed to be the same and subtractions
to compare numbers. Non Zeros in either case indicate a difference
Phil
"Maur" <pk***@earthlin k.net> wrote in message
news:aK******** *********@newsr ead2.news.atl.e arthlink.net... Hi all,
I have 2 tables say t_OLD and t_NEW. The new has corrections for audit purposes.
They are identical in all respects (i.e. new is a copy of old and then changes are made to t_new)
I would like a quick way to cycle through all the the fields in each table and compare the values to see if there are differences.
I would like to AVOID having to hardcode a comparison of each and every fieldname pair -- perhaps a public TABLE compare function that would generate a list of all records and the differences between the values in each pair.
Any thoughts on how to approach this are most welcome.
If anyone has done something like this I would appreciate hearing about your strategy.
I think I discussed this a while back with Andi Plotsky. Not sure
what the subject of the discussion was, but if you search, you should
find it. In short:
You would need to join the two tables on a non-autonumber
field/fields.
Steps (if memory serves):
given tblA and tblB
1. Run "Find Unmatched" wizard on A,B.
2. Repeat with B,A.
these are the ones you definitely need to look at.
To find the inconsistent fields in the remaining records, you'd need
to do something like
- create a query that inner joins the two tables.
- loop through the records in the query
- loop through the fields collection of the querydef (so you might
want to put tblA fields first, and then the tblB fields). To do that,
you'd determine the "offset" of the second table's records. Say the
query consists of 5 fields from each table, so you'd be looking at
qdf.Fields(0) to qdf.Fields(9), so you could compare them...
really simply:
1. loop through recordset
2. loop through fields in single record
3. log non-matching values, ie. rs.Fields(n).Va lue <>
rs.Fields(n+OFF SET).Value
HTH,
Pieter
This will give you any record that had any change to it BUT not list
what the change is.
Example:
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.d = Table1.d) AND (Table2.c =
Table1.c) AND (Table2.b = Table1.b) AND (Table2.a = Table1.a) AND
(Table2.ID = Table1.ID)
WHERE (((Table1.ID) Is Null));
You can just go through the "Find Unmatched Query Wizard". The only
problem with that is that you can only link on one field. Therefore,
go into design view and link on ALL of the other fields.
"Maur" <pk***@earthlin k.net> wrote in message news:<aK******* **********@news read2.news.atl. earthlink.net>. .. Hi all,
I have 2 tables say t_OLD and t_NEW. The new has corrections for audit purposes.
They are identical in all respects (i.e. new is a copy of old and then changes are made to t_new)
I would like a quick way to cycle through all the the fields in each table and compare the values to see if there are differences.
I would like to AVOID having to hardcode a comparison of each and every fieldname pair -- perhaps a public TABLE compare function that would generate a list of all records and the differences between the values in each pair.
Any thoughts on how to approach this are most welcome.
If anyone has done something like this I would appreciate hearing about your strategy.
If you use ADO you can open both tables and then cycle through the
field names as provided via ADO to perform the comparison - that way
you would only need to open tabelOld cycle through each record and
then each field against the conterpart in tableNew
"Maur" <pk***@earthlin k.net> wrote in message news:<aK******* **********@news read2.news.atl. earthlink.net>. .. Hi all,
I have 2 tables say t_OLD and t_NEW. The new has corrections for audit purposes.
They are identical in all respects (i.e. new is a copy of old and then changes are made to t_new)
I would like a quick way to cycle through all the the fields in each table and compare the values to see if there are differences.
I would like to AVOID having to hardcode a comparison of each and every fieldname pair -- perhaps a public TABLE compare function that would generate a list of all records and the differences between the values in each pair.
Any thoughts on how to approach this are most welcome.
If anyone has done something like this I would appreciate hearing about your strategy. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: jeff brubaker |
last post by:
Hello,
Currently we have a database, and it is our desire for it to be able
to store millions of records. The data in the table can be divided up
by client, and it stores nothing but about 7 integers.
| table |
| id | clientId | int1 | int2 | int 3 | ... |
Right now, our benchmarks indicate a...
|
by: gloria |
last post by:
I would like to compare data across two tables. I have part
information in a table. I get a new set of information periodically.
I would like to compare my new info to my old info. I recognize that
doing a compare of every attribute of every part will take FOREVER. Is
there some way I can do a "diff" based on the columns that I care...
|
by: Baseball |
last post by:
It seems like this would be easy, but I can't seem to figure this one
out. I need an easy way to make Access compare two similar tables and
kick out a report that shows information mismatches. Certain fields
might have different names.
Very Fast example:
Table 1
Office Louisville
|
by: Megan |
last post by:
Hi everybody-
I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the new database. For instance,
her old database has a table with Band Info in it. Her new database
also has a table with Band Info in it but slightly...
|
by: filbennett |
last post by:
Hi Everyone,
I'm generally unfamiliar with Access form design, but have programmed
Cold Fusion applications for a couple of years.
I'd like to build a data entry form in Access that allows the
following.
First, the data schema: Three tables are involved. The first is a
PERSONS table which has two fields, SSNUMBER (primary key), and...
| |
by: khushbubhalla |
last post by:
how to compare data between tables and views row by row , column by column in the test and production environment
|
by: tezza98 |
last post by:
HI i am using BULK INSERT to copy data from a text file into a table that already exists
BULK INSERT dbo.TRANStemp
FROM 'D:\MSSQL\Data\TRANS.csv'
WHERE dbo.TRANStemp.DateTime <>
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
|
by: malini |
last post by:
I have two tables
emp - ( has two field)
Name Sal
Joe 45
Jack 50
Kate 67
Dept
Name Dept Sal
Joe IS 46
|
by: frankj |
last post by:
I have a database in which survey data is entered twice (by coder A & B). Coder A's Form is linked to a table storing their data. Coder B's form is linked to a table storing their data. In every other way, the forms and data structures are identical.
What I need to do is set up a data validation routine. Once coder A has entered the data into...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |