Compare New Records fields (50+) to old/existing records data fiel

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

Guest

I receive 2 monthly files on members that contain 50+ fields of data.
Each record already indicates either New, Change or Term.

On a Change record, what I need to do is when I receive a record on a member
that already exists in my table, I want access to automatically check the
fields of data to determine what the Change is.
I need to know which fields of data on the change record have different
information than the previous record.

I am looking for someone to suggest where I start with this. I know it can
be done. I am a fairly experienced user; just looking for some help getting
started. I am assuming this will require some programming, which is great if
anyone has any suggestions.

The database is '97.
THANKS!!
 
This might get you started. I opened Northwind and copied and pasted the
Product table as Product2. I then used the following code to create a big
union query that returns values field by field based on the primary key. You
can take the SQL that is created and paste it into a blank query SQL view.
Then query this query where Products <> Products2. I called the function
with a call in the immediate window like:
? finddifferences("products","Products2","ProductID")

'watch out for line wrapping. I tried to shorten all lines where possible.
The generated SQL is listed below.

Function FindDifferences(pstrTable1 As String, _
pstrTable2 As String, _
pstrPrimaryKey As String) As String
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
Dim strSelect As String
Dim strWhere As String
Dim strFrom As String
Set db = CurrentDb
Set td = db.TableDefs(pstrTable1)
strFrom = " FROM [" & pstrTable1 & "] t1 INNER JOIN [" & _
pstrTable2 & "] t2 ON t1.[" & pstrPrimaryKey & _
"] = t2.[" & pstrPrimaryKey & "] "
strWhere = "WHERE "
strSelect = "SELECT '' as [" & pstrPrimaryKey & _
"] , '' as FldName, '' as [" & pstrTable1 & _
"], '' As [" & pstrTable2 & "] FROM [" & pstrTable1 & "] "
For Each fd In td.Fields
strSelect = strSelect & vbCrLf & "UNION " & _
vbCrLf & "SELECT t1.[" & pstrPrimaryKey & _
"], '" & fd.Name & "', t1.[" & fd.Name & _
"], t2.[" & fd.Name & "] " & vbCrLf & _
strFrom
Next
Debug.Print strSelect
End Function

SELECT '' as [ProductID] , '' as FldName, '' as [products], '' As
[Products2] FROM [products]
UNION
SELECT t1.[ProductID], 'ProductID', t1.[ProductID], t2.[ProductID]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'ProductName', t1.[ProductName], t2.[ProductName]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'SupplierID', t1.[SupplierID], t2.[SupplierID]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'CategoryID', t1.[CategoryID], t2.[CategoryID]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'QuantityPerUnit', t1.[QuantityPerUnit],
t2.[QuantityPerUnit]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'UnitPrice', t1.[UnitPrice], t2.[UnitPrice]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'UnitsInStock', t1.[UnitsInStock], t2.[UnitsInStock]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'UnitsOnOrder', t1.[UnitsOnOrder], t2.[UnitsOnOrder]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'ReorderLevel', t1.[ReorderLevel], t2.[ReorderLevel]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID]
UNION
SELECT t1.[ProductID], 'Discontinued', t1.[Discontinued], t2.[Discontinued]
FROM [products] t1 INNER JOIN [Products2] t2 ON t1.[ProductID] =
t2.[ProductID];
 
Back
Top