I want to Sync two tables (kinda like syncing a palm)

[Guest]

Hi,

I was able to occupy myself for a couple weeks without posting a question
(yay!). But I am back again with a doozie. Hope someone can give me some
direction.

I have table1 and table2, identical structure. Each week I get a new Table2
sent to me via email (don't ask why, I can't change this fact).

table2 will have mostly the same data as table1(from the previous week), but
some fields may have changed for existing data. There also may be new
records added, or records TAKEN AWAY.

There is no primary key, as all fields may have duplicates. However, I
could make a multiple-field primary key based on 6 fields combined (out 10
fields).

I need to compare the two tables, overwrite data in table1 from data in
table2 where the multi-field primary key is a match. I also need new records
from table2 to be added to table1, BUT.... I don't want records in table1
deleted if they have been deleted in table2. In fact, I need the
targetdelivDate text field in table1 to be marked "Finished" if the
corresponding record has been deleted in in table2.

I have been trolling the Access groups for a comprehensive solution that I
can set up once and will be easy to run each week, as turnkey as possible. I
have come up with partial answers like running a "find unmatched" query or
"group by's" or "NOT IN" or "make-table" queries, but somehow I can't find
the right way to put them together. And, when I try using WHERE or NOT IN
with multiple AND's for field names, I end up with WAY more records in my
query than I began with.

I also don't quite understand how to refer to a multi-field primary key in a
query for comparing between two tables, if it is even possible. If I could
refer to a multi-field primary key almost as if it were one field, that would
simplify things immensely For instance, if I could run queries to: select
records from table2 where there is no match for multifieldprimary in table1,
select records from table1 where there is no match for multifield primary in
table2. Then select records from table2 where multifieldprimary matches
table1. and then push those results into a new table.


my fields are
style, orderNo, OrderDate, color, buyer, targetDelivDate, ProjDelivDate,
Office, units, and Factory

The multi-field primary key would need to include all fields except the
last four (ProjDelivDate, Office, units, and Factory)


Any Ideas for me?

Thanks
m-

 

[John Spencer]

WARNING: UNTESTED SQL code follows. Backup your data before you run these
queries

You want to
(1) update existing records (Target) that have no match in the source table
(2) update existing records that have a match in the source table
(3) add new records from the source table

Assumption: The six fields (style, orderNo, OrderDate, color, buyer,
targetDelivDate) are never null
If so, then you will need to rewrite this SQL to account for the fields that
could be null

(1)
UPDATE Target as T LEFT JOIN Source as S
ON T.style = S.Style AND
T.orderNo = S.OrderNo AND
T.OrderDate=S.OrderDate AND
T.Color=S.Color AND
T.Buyer = S.Buyer AND
T.TargetDelivDate = S.TargetDelivDate
SET T.TargetDelivDate = "Finished"
WHERE S.OrderNo is Null

(2)
UPDATE Target as T INNER JOIN Source as S
ON T.style = S.Style AND
T.orderNo = S.OrderNo AND
T.OrderDate=S.OrderDate AND
T.Color=S.Color AND
T.Buyer = S.Buyer AND
T.TargetDelivDate = S.TargetDelivDate
SET T.TargetDelivDate = .[TargetDelivDate]
, T.Office = .[Office]
, T.Units = .[Units]
, T.Factory = .Factory

(3)
INSERT INTO Target
(style, orderNo, OrderDate, color, buyer
, targetDelivDate, ProjDelivDate,
Office, units, Factory)
SELECT S.style, S.orderNo, S.OrderDate, S.color, S.buyer
, S.targetDelivDate, SProjDelivDate
, S.Office, S.units, S.Factory
FROM Target as T RIGHT JOIN Source as S
ON T.style = S.Style AND
T.orderNo = S.OrderNo AND
T.OrderDate=S.OrderDate AND
T.Color=S.Color AND
T.Buyer = S.Buyer AND
T.TargetDelivDate = S.TargetDelivDate
WHERE T.OrderNo Is Null


The multi-field primary key would need to include all fields except the
last four (ProjDelivDate, Office, units, and Factory)

 

[Guest]

Hey, John,

You are awesome! everything seems to work just great.(!!!)

I am curious as to why you chose to use the "order No" field in both queries
"where OrderNo is Null" instead of using the Style field, which seemed to
make more sense to me. I guess, since neither of them can be null it would
make sense to me that it wouldn't matter, but I was just wondering if there's
a specific reason you chose that field.

Thanks so much for ALL your time and effort!!

m-

my butt saved again...

 

[John Spencer]

No special reason. I could have played it safe and used all six fields, but
that should not have been needed.