Re: Help needed with query design

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
..

"retired fire" <retiredfire@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:661580E1-B3FF-4254-AC4D-4FC40BCD46DB@xxxxxxxxxxxxxxxx
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....

"John Spencer" wrote:

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
..

"retired fire" <retiredfire@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0828ABA0-EEA0-4813-8B13-19BA140A22A2@xxxxxxxxxxxxxxxx


"John Spencer" wrote:

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
'====================================================


retired fire wrote:
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....


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...





.

Relevant Pages

  • Re: Query to compare subtables
    ... To perform this, you could query for everything in Set A. Then, through ... Does not contain each element of the compare set. ... Suppose that another set had only members A1 and A3. ... > I have a master table and a details table. ...
    (microsoft.public.access.queries)
  • Re: About my Database
    ... Hi Robert! ... It looks to me like you might have a structural problem - I'm not sure that ... - make the Basics table the Master with key on SSN ...
    (microsoft.public.access.gettingstarted)
  • Re: Help needed with query design
    ... which will be fine to address changes in the master table and the update ... "John Spencer" wrote: ... -- Drag from SSN to SSN ... Each Members records can change ...
    (microsoft.public.access.queries)
  • Re: About my Database
    ... Forms -- Display data, add data, edit data, and sometimes ... The way around this right now is for me to plug in the SSN manually ... >> - make the Basics table the Master with key on SSN ...
    (microsoft.public.access.gettingstarted)
  • Re: Add and update data to multiple tables
    ... which will handle a list of clients of a small insurance company. ... ID which is the primary key and the field SSN which I ... MASTER, to add a new record or to modify existing ones. ... In the tables TEMPLATES and FORMS only the SSN will be saved unless ...
    (comp.databases.ms-access)