update=merge 2 tables+replace sole adhoc fields & prevent duplicat

[Guest]

Update = merge 2 tables + replace sole ad hoc fields & prevent records
duplication

Hello !

I am seeking a solution to the hereunder problem.

Basic data :

- in Access, an original table with 6000 records having 50 fields, a.o. a
"contact n°" field ( with a primary key & auto n° ), and a "last update"
field ( date format )
- a new table ( extracted from an other Access programme ) with 13 fields,
a.o. "contact n°" field ( I can cancel its primary key & auto n° functions
), and a "last update" field ( date format )
- normally, the new table contains the same records as the original table,
but it is not trustable ( f.i. I noticed that records where cancelled without
reason, so that the sole records list of the original table is trustable =>
the original table HAS to be updated )
- after preliminary transformations of the new table,
- both tables have the same field names for the 13 common fields
- after having sorted the table on the "last update" field, I insulated the
720 records of the new table that were modified/updated.

Question:

How can I update the 13 common fields in my original table ? In other
words, how can I
update = merge 2 tables + replace sole ad hoc fields & prevent records
duplication ?

It seems I have to work through an export of the new table in an Excel
sheet, what is not a problem in itself.
But a simple import the that Excel sheet in Access is refused, unless I add
new records, what is obviously a no-sense, because I'd have duplicated
recrods.

Thank you for your help.

Pierre-André.

 

[John Nurick]

Hi Pierre-André,

First, I recommend that you rename the fields so their names do not
contain spaces or special characters (e.g. "ContactNumber" instead of
"contact n°". This makes life simpler.

Are the ContactNumbers shared between the two tables? For example, does
the record with ContactNumber 123 in the original table refer to the
same contact as the record with ContactNumber 123 in the new table?

If so, then the task is quite simple. For safety, work with a copy of
your database. The procedure is something like this. I'll call the
tables tblOrig and tblNew.

1) Create a query that joins the two tables on ContactNumber and returns
the 13 fields from tblOrig that need to be updated. The SQL view will be
something like this:
SELECT tblOrig.Field1, tblOrig.Field2, ... tblOrig.Field13
FROM tblOrig INNER JOIN tblNew
ON tblOrig.ContactNumber=tblNew.ContactNumber;

2) Add a criterion to this query so it returns only the records that
need to be updated. I presume these are records where tlbNew.LastUpdate
is more recent than tboOrig.LastUpdate. The SQL view will be something
like:
SELECT tblOrig.Field1, tblOrig.Field2, ... tblOrig.Field13
FROM tblOrig INNER JOIN tblNew
ON tblOrig.ContactNumber=tblNew.ContactNumber
WHERE (tblNew.LastUpdate > tblOrig.LastUpdate);

3) Using the Query menu, convert the query to an update query. In each
cell in the "Update To" row of the query design grid, enter the name of
the relevant field from tblNew. So you'd update tblOrig.Field1 to
tblNew.Field1 and so on.

4) When everything's right, run the update query.


NB 1: if the ContactNumber values do not identify corresponding records
in the two tables, things are more difficult: you have to find another
field or combinatoin of fields that can be used to join the two tables.

NB 2: if the 13 fields in tblNew are not always populated, you need to
update a field in a record in tblOrig if and only if there's a value in
the corresponding field in tblNew. You can do this by using expressions
like this in the "Update To" cells:

IIf(IsNull(tblNew.Field1), tblOrig.Field1, tblNew.Field1)

 

[Guest]

Hello John,


It works. Vous êtes un chef !!

I had some difficulties to implant the "where" condition, because I did not
knew anything from SQL. I consider myself just as a good "bricoleur" in
Access, not a professional programer.

Sorry for having been somewhat long to give you a feed back: I did not want
to send too many mails.
Before the end of this month ( when this work will be over ), I'll send an
addendum with print-screen of the queries, because I also found a way to
program the query with Access ( then, without SQL => easier for everybody ).

Thanks once more.
So long.


Pierre-André.

 

[Guest]

Hello John,



As promised in my message dd 11/16, please find hereunder another way to
program the required function, easier for people who - like me - are not
professional programmers.
John Nurick' solution is the scientific solution. But since I don't know
SQL language, I had some problems to write the 'where' line correctly.
Maybe, you can write the SQL resquest given by John Nurick, re-open the query
in 'modify' mode, and observe the screen.

----------

First, copy and paste the new table from the new Access pgm to the original
pgm.
Secondly - if you whish get a possibility to view/control the updated
records - create a new filed named 'UpdatedField' in the original table and
at least 10 characters.

Then create a new query, preferably in 'update' mode ( in my Access'97, the
selection is made through the button under 'tools' in the toolbar ), add the
'tblOrig and the 'tblNew', join them through their common field ( eventually
do not forget to select the appropriate 'join property' )

After that, you glide the required fields from the tables to the appropriate
boxes of the lower part of the window and use them as you would do for a
common 'selection request' ( a.o. with the same logic symbols ).
Then, write your update message and update instructions in the 'update' line
of the lower part of the screen.

Unfortunately, it is not possible to put an image in the present message,
because it is really simple:
You could see
+ 'tblOrig' with 'ContactNr', 'Name', 'Address', 'UpdatedField', ...
fields
+ 'tblNew' with 'ContactNr', 'Name', 'Address', 'UpdatedField', ... fields
in the lower part of the screen, one information by column
+ line 'fields': 'UpdatedField', 'Address', ...
+ line 'table': 'tblOrig', 'tblOrig', ...
+ line 'update'': 'updated', ' [tblNew].[Address] ', ...
+ lines 'conditions': column 1 empty, ' <>[tblNew].[Address] ', ...

After having run the update request, you'll have the possibility to check
the updated records since they are marked in the 'UpdatedField'.

That way of programing, though less scientific, is quicker and easier
+ to deal with complex fields names and tables
+ to deal with more complex selections and/or with more than two tables.

Thanks to John Nurick for having shown me the path.

Pierre-André

_____________________________________________________________________