Help needed with query design

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

Guest

I have a master table with 25 fields and each month I receive a table with
updated data that needs to be checked against the original table to identify
any changes in any of the records 25 fields (such as added records or any
changes to any field in any existing records)....I have found no way to set
up the query to do this...What am I missing...

I need to have the query to return only changes made in the updated table
that are not in the master table....

Any help would be greatly appreciated....
 
Well, how do you match master table records to update table records? Is
there a field or fields that uniquely identify the records that match (a
primary key)between the two tables?


Generic SQL to show NEW records would look something like
SELECT Update.*
FROM Update LEFT JOIN Master
ON UPdate.MatchingField = Master.MatchingField
WHERE Master.MatchingField is Null

Generic SQL to records that have changed would be something like
SELECT Update.*
FROM Update as U INNER JOIN Master as M
ON U.MatchingField = M.MatchingField
WHERE U.Field1 <> M.Field1
OR U.Field2 <> M.Field2
OR ...

That gets more complicated if you have to deal with nulls. Then you
have to use the NZ function to force a value in the comparison or use
some thing like

WHERE (U.Field1 <> M.Field1 Or U.Field1 is Null and M.Field1 is not Null
or U.Field1 is not null and M.Field1 is Null)
OR (U.Field2 <> M.Field2 Or U.Field2 is Null and M.Field2 is not Null or
U.Field2 is not null and M.Field2 is Null)

Or alternate which is easier to enter, but may not be as fast to search
WHERE Nz(U.Field1,"") <> Nz(M.Field1,"")



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
John Spencer said:
Well, how do you match master table records to update table records? Is
there a field or fields that uniquely identify the records that match (a
primary key)between the two tables?


Generic SQL to show NEW records would look something like
SELECT Update.*
FROM Update LEFT JOIN Master
ON UPdate.MatchingField = Master.MatchingField
WHERE Master.MatchingField is Null

Generic SQL to records that have changed would be something like
SELECT Update.*
FROM Update as U INNER JOIN Master as M
ON U.MatchingField = M.MatchingField
WHERE U.Field1 <> M.Field1
OR U.Field2 <> M.Field2
OR ...

That gets more complicated if you have to deal with nulls. Then you
have to use the NZ function to force a value in the comparison or use
some thing like

WHERE (U.Field1 <> M.Field1 Or U.Field1 is Null and M.Field1 is not Null
or U.Field1 is not null and M.Field1 is Null)
OR (U.Field2 <> M.Field2 Or U.Field2 is Null and M.Field2 is not Null or
U.Field2 is not null and M.Field2 is Null)

Or alternate which is easier to enter, but may not be as fast to search
WHERE Nz(U.Field1,"") <> Nz(M.Field1,"")



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Click to expand...

Thanks for the reply...

Let me provide further detail...

I manage the data for our organization of 960 members (960 records with 25
fields each)... Each Members records can change (address,city,state.etc..)
monthly...
New members can be added monthly, and members can be deleted monthly..

Since I receive this data monthly from an outside source, I must check the
monthly data for any changes to any current record, for any new records, and
for any current records that have been deleted....thus manually checking 960
records and 25 fields in each record...very time consuming...

Thus I hope there is a way to set it up so MS Access can check the master
table against the monthly update, and only return those current records that
have changed, those current records that have been deleted, and any new
records added...

Hopefully this better explains what is needed....

Thanks in advance for any solutions...
 
I understand what you want to do. What I still don't know is how you match
the records.

For instance, do you match a master record to an update record by a member
number that is unique and not reused? By member name ? Names are not a
good for completely dependable matching since names often duplicate and
names change for women (and men) through marriage, divorce, adoption, court
order, misspelling, etc.

You are doing this now. How do you decide that the first record you see in
the update table matches one of the records in the master table? How do you
decide that the second record you see in the update table is not in the
master table? How do you decide that the third record in the master table is
not in update table?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks for the further help... I currently have to manually check each
record, sorted by last name, first name, middle initial.... and the unique
field is social security nuimber....
 
Ok so SSN should uniquely identify the records.

Use the Unmatched query wizard to identify records that have been added and
deleted. Find unmatched records in the Master table to identify those which
have been dropped and unmatched records in the update table to identify
those which have been added.

The SQL to show Master Records that have been dropped would be
SELECT M.*
FROM MasterTable as M LEFT JOIN UpdateTable as U
ON M.SSN = U.SSN
WHERE U.SSN is Null

In the query grid
-- Add both tables
-- Drag from SSN to SSN
-- Double click on the join line and select ALL from Master and only
matching from Update
-- Add all the fields from master and just the SSN field from Update
-- Set criteria under UpdateTable.SSN to Is Null

Do similar thing to find the records in the update table that are not in the
master table.

For changes to FirstName and Last Name and ZipCode
-- Add both tables
-- Drag from SSN to SSN
-- Add all the fields from master
-- Add a calculate fiedl
Field: Master.Field1 & Master.Field2 & Master.Field3 & ... &
Master.Field10
Criteira: <> UpdateTable.Field1 & UpdateTable.Field2 & UpdateTable.Field3
& ... & UpdateTable.Field10

You can probably do 10 or so fields at a time with this technique
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks for the great info, I will set it up and test it, looks like it
should work....from what I read, I will end up with 2 unmatched queries,
which will be fine to address changes in the master table and the update
table....

Thanks again, I really appreciate your efforts.....

Bob
 
Back
Top