Append new records only

[Guest]

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.

 

[Jeff Boyce]

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

 

[Guest]

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

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

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

 

[Guest]

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
boxes, so the user presses the button and as far as they need to know it all
updated.

Thank you, Tim

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

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

 

[Ed Robichaud]

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

 

[Guest]

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

 

[AlCamp]

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

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

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

 

[Jeff Boyce]

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)?

--
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
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
boxes, so the user presses the button and as far as they need to know it all
updated.

Thank you, Tim

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

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