Append new records only

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Timboo - 25 Jan 2005 11:15 GMT

Hi I have a table (TblPatients) that I wish to append new records to. The
source is a linked (TXT) table called (PASImport) that I have already
created.

So far I have created an append query that successfully appends all records
in the PasImport table to theTblPatients. This however imports records that
already exist, or have already been imported if the query has already been
run. I have tried inserting Criteria in the append query
"[TblPatients]![PatientNo]=Null" (so that if the patient Number exists in the
TblPatients, then the Append query would not append that record - well thats
what I thought would happen, but access does not like my logic. What am I
doing wrong?

Thank you, Tim.

Reply to this Message

Jeff Boyce - 25 Jan 2005 12:53 GMT

Timboo

If you add a "unique index" on your tblPatients on the fields that come from
PASImport and that should not be duplicated, your append will "fail" for
those that would otherwise be duplicates. For all others, they'll append...

Signature

Good luck

Jeff Boyce
<Access MVP>

> Hi I have a table (TblPatients) that I wish to append new records to. The
> source is a linked (TXT) table called (PASImport) that I have already
[quoted text clipped - 10 lines]
>
> Thank you, Tim.

Reply to this Message

Timboo - 25 Jan 2005 13:29 GMT

Thanks Jeff, that works well, since the query will probably be run via a code
or macro, is there a way I can suppress the error/ confirmation dialogue
boxes, so the user presses the button and as far as they need to know it all
updated.

Thank you, Tim

> Timboo
>
[quoted text clipped - 20 lines]
> >
> > Thank you, Tim.

Reply to this Message

Timboo - 25 Jan 2005 13:47 GMT

Also... Am I right in assuming that since the data to be imported may also
contain changes that would effect existing data in the main table, then I
also need to run an update query to update these records. I notice that the
update query can easily add say 20% to an existing field value, but can it
look to see if the imported value is different, and if so, update the old
value, to the value in the imported table - or should I be looking for a
different way of doing this?

Thanks Tim

> Thanks Jeff, that works well, since the query will probably be run via a code
> or macro, is there a way I can suppress the error/ confirmation dialogue
[quoted text clipped - 27 lines]
> > >
> > > Thank you, Tim.

Reply to this Message

Jeff Boyce - 25 Jan 2005 23:09 GMT

Tim

So, you want to append if the record isn't already in there, but update if
the record is...

Or are you saying that there might be times when what you are importing
should NOT overwrite what's already in your permanent table? If so, you'd
need to figure a way to exclude those records from update.

Are you quite confident that the imported data will always be "more correct"
than the data in your permanent table(s)?

Signature

Good luck

Jeff Boyce
<Access MVP>

> Also... Am I right in assuming that since the data to be imported may also
> contain changes that would effect existing data in the main table, then I
[quoted text clipped - 37 lines]
> > > >
> > > > Thank you, Tim.

Reply to this Message

AlCamp - 25 Jan 2005 15:35 GMT

Timboo,
To prevent messages during the Import process, use the DoCmd.SetWarnings
= True/False function.
hth
Al Camp

> Thanks Jeff, that works well, since the query will probably be run via a
> code
[quoted text clipped - 35 lines]
>> >
>> > Thank you, Tim.

Reply to this Message

Ed Robichaud - 25 Jan 2005 13:58 GMT

If you think about it, you don't want to append records where the PatientNo
is null (in either table), but where it is unduplicated in the Patients
table. Use the query wizard to create a "Find Unmatched.." query, and use
the result of that to append the records you want.
-Ed

> Hi I have a table (TblPatients) that I wish to append new records to. The
> source is a linked (TXT) table called (PASImport) that I have already
[quoted text clipped - 14 lines]
>
> Thank you, Tim.

Reply to this Message

Timboo - 25 Jan 2005 14:59 GMT

Thanks Ed, Tried that out as well, as an alternative -all aids my
understanding, thanks Tim.

> If you think about it, you don't want to append records where the PatientNo
> is null (in either table), but where it is unduplicated in the Patients
[quoted text clipped - 20 lines]
> >
> > Thank you, Tim.

Reply to this Message