I am working with an Access 2000 database. I import a text
file into a table, add some calculated fields to the
records, then append the records to a different table.
After the records are imported, I delete all records from
the original table.

My problem is I want to delete the records from the
original table that already exist in the destination table
before I append them.

The fields that I need to compare are:
Type (1-4)
ChkNum (number)
OnPad (date & time)
OffPad (date & time)
Start (date & time)
End (date & time)
Total (imported as text, converted to Currency during
append)
StrID (number)

The Type, ChkNum, Total, and StrID are the only fields
that are consistently populated.

If I create a query that joins all of the fields listed
above, I only get the duplicate records that have all
fields populated.

Any suggestions?

Thank you,
Jean

In the query tab of the Database window, click the New button and choose the
Find Duplicates option. This should get you started. You will probably have
to massage it a bit because it isn't a dupe if not all the fields match.

Next, you said that you wanted to delete records before appending. If you
are going to do this, you will probably want to call the queries from code
and wrap them in a Transaction so that if the append fails, you can undo the
delete. Otherwise, do the append first then delete the dupes. The 2nd way
may be what you're doing, but the way you worded it I wasn't sure (how do
they already exist if you haven't appended them yet?).

--
Wayne Morgan
Microsoft Access MVP


"Jean" <(E-Mail Removed)> wrote in message
news:163a01c3a89a$c4cd3a80$(E-Mail Removed)...
> I am working with an Access 2000 database. I import a text
> file into a table, add some calculated fields to the
> records, then append the records to a different table.
> After the records are imported, I delete all records from
> the original table.
>
> My problem is I want to delete the records from the
> original table that already exist in the destination table
> before I append them.
>
> The fields that I need to compare are:
> Type (1-4)
> ChkNum (number)
> OnPad (date & time)
> OffPad (date & time)
> Start (date & time)
> End (date & time)
> Total (imported as text, converted to Currency during
> append)
> StrID (number)
>
> The Type, ChkNum, Total, and StrID are the only fields
> that are consistently populated.
>
> If I create a query that joins all of the fields listed
> above, I only get the duplicate records that have all
> fields populated.
>
> Any suggestions?
>
> Thank you,
> Jean

Thank you for the response.

The 'Find Duplicates' wizard only allows you to find dups
in one table as far as I can tell. I want to find the
records in one table that are already in another table
before I append them to that table.

The destination table contains all records that have
previously been imported. Therefore I want to catch
records that have already been imported before I append
the newest imported records to the destination table.

I am performing other processes on the imported data
before I append it to the destination table so I need to
do the deletion at a specific step in the process before
the append.

Jean

>-----Original Message-----
>In the query tab of the Database window, click the New
button and choose the
>Find Duplicates option. This should get you started. You
will probably have
>to massage it a bit because it isn't a dupe if not all
the fields match.
>
>Next, you said that you wanted to delete records before
appending. If you
>are going to do this, you will probably want to call the
queries from code
>and wrap them in a Transaction so that if the append
fails, you can undo the
>delete. Otherwise, do the append first then delete the
dupes. The 2nd way
>may be what you're doing, but the way you worded it I
wasn't sure (how do
>they already exist if you haven't appended them yet?).
>
>--
>Wayne Morgan
>Microsoft Access MVP
>
>
>"Jean" <(E-Mail Removed)> wrote in message
>news:163a01c3a89a$c4cd3a80$(E-Mail Removed)...
>> I am working with an Access 2000 database. I import a
text
>> file into a table, add some calculated fields to the
>> records, then append the records to a different table.
>> After the records are imported, I delete all records
from
>> the original table.
>>
>> My problem is I want to delete the records from the
>> original table that already exist in the destination
table
>> before I append them.
>>
>> The fields that I need to compare are:
>> Type (1-4)
>> ChkNum (number)
>> OnPad (date & time)
>> OffPad (date & time)
>> Start (date & time)
>> End (date & time)
>> Total (imported as text, converted to Currency during
>> append)
>> StrID (number)
>>
>> The Type, ChkNum, Total, and StrID are the only fields
>> that are consistently populated.
>>
>> If I create a query that joins all of the fields listed
>> above, I only get the duplicate records that have all
>> fields populated.
>>
>> Any suggestions?
>>
>> Thank you,
>> Jean
>
>
>.
>

For duplicates in 2 different tables, if there is a unique ID field in both of the tables
that is also duplicated you could just link the 2 tables on that field. The only value
that will be returned are those where both tables match.

--
Wayne Morgan
Microsoft Access MVP


"Jean" <(E-Mail Removed)> wrote in message news:04e401c3a920$7d75ddf0$(E-Mail Removed)...
> Thank you for the response.
>
> The 'Find Duplicates' wizard only allows you to find dups
> in one table as far as I can tell. I want to find the
> records in one table that are already in another table
> before I append them to that table.
>
> The destination table contains all records that have
> previously been imported. Therefore I want to catch
> records that have already been imported before I append
> the newest imported records to the destination table.
>
> I am performing other processes on the imported data
> before I append it to the destination table so I need to
> do the deletion at a specific step in the process before
> the append.
>
> Jean
>
> >-----Original Message-----
> >In the query tab of the Database window, click the New
> button and choose the
> >Find Duplicates option. This should get you started. You
> will probably have
> >to massage it a bit because it isn't a dupe if not all
> the fields match.
> >
> >Next, you said that you wanted to delete records before
> appending. If you
> >are going to do this, you will probably want to call the
> queries from code
> >and wrap them in a Transaction so that if the append
> fails, you can undo the
> >delete. Otherwise, do the append first then delete the
> dupes. The 2nd way
> >may be what you're doing, but the way you worded it I
> wasn't sure (how do
> >they already exist if you haven't appended them yet?).
> >
> >--
> >Wayne Morgan
> >Microsoft Access MVP
> >
> >
> >"Jean" <(E-Mail Removed)> wrote in message
> >news:163a01c3a89a$c4cd3a80$(E-Mail Removed)...
> >> I am working with an Access 2000 database. I import a
> text
> >> file into a table, add some calculated fields to the
> >> records, then append the records to a different table.
> >> After the records are imported, I delete all records
> from
> >> the original table.
> >>
> >> My problem is I want to delete the records from the
> >> original table that already exist in the destination
> table
> >> before I append them.
> >>
> >> The fields that I need to compare are:
> >> Type (1-4)
> >> ChkNum (number)
> >> OnPad (date & time)
> >> OffPad (date & time)
> >> Start (date & time)
> >> End (date & time)
> >> Total (imported as text, converted to Currency during
> >> append)
> >> StrID (number)
> >>
> >> The Type, ChkNum, Total, and StrID are the only fields
> >> that are consistently populated.
> >>
> >> If I create a query that joins all of the fields listed
> >> above, I only get the duplicate records that have all
> >> fields populated.
> >>
> >> Any suggestions?
> >>
> >> Thank you,
> >> Jean
> >
> >
> >.
> >

There is no unique ID field that is duplicated in both
tables. The fields that I previously listed are the only
fields that will be duplicates and none of them are unique
ID fields. Although each table does have a unique ID
field, they are not duplicates of each other.

Jean
>-----Original Message-----
>For duplicates in 2 different tables, if there is a
unique ID field in both of the tables
>that is also duplicated you could just link the 2 tables
on that field. The only value
>that will be returned are those where both tables match.
>
>--
>Wayne Morgan
>Microsoft Access MVP
>
>
>"Jean" <(E-Mail Removed)> wrote in message
news:04e401c3a920$7d75ddf0$(E-Mail Removed)...
>> Thank you for the response.
>>
>> The 'Find Duplicates' wizard only allows you to find
dups
>> in one table as far as I can tell. I want to find the
>> records in one table that are already in another table
>> before I append them to that table.
>>
>> The destination table contains all records that have
>> previously been imported. Therefore I want to catch
>> records that have already been imported before I append
>> the newest imported records to the destination table.
>>
>> I am performing other processes on the imported data
>> before I append it to the destination table so I need to
>> do the deletion at a specific step in the process before
>> the append.
>>
>> Jean
>>
>> >-----Original Message-----
>> >In the query tab of the Database window, click the New
>> button and choose the
>> >Find Duplicates option. This should get you started.
You
>> will probably have
>> >to massage it a bit because it isn't a dupe if not all
>> the fields match.
>> >
>> >Next, you said that you wanted to delete records before
>> appending. If you
>> >are going to do this, you will probably want to call
the
>> queries from code
>> >and wrap them in a Transaction so that if the append
>> fails, you can undo the
>> >delete. Otherwise, do the append first then delete the
>> dupes. The 2nd way
>> >may be what you're doing, but the way you worded it I
>> wasn't sure (how do
>> >they already exist if you haven't appended them yet?).
>> >
>> >--
>> >Wayne Morgan
>> >Microsoft Access MVP
>> >
>> >
>> >"Jean" <(E-Mail Removed)> wrote in message
>> >news:163a01c3a89a$c4cd3a80$(E-Mail Removed)...
>> >> I am working with an Access 2000 database. I import a
>> text
>> >> file into a table, add some calculated fields to the
>> >> records, then append the records to a different
table.
>> >> After the records are imported, I delete all records
>> from
>> >> the original table.
>> >>
>> >> My problem is I want to delete the records from the
>> >> original table that already exist in the destination
>> table
>> >> before I append them.
>> >>
>> >> The fields that I need to compare are:
>> >> Type (1-4)
>> >> ChkNum (number)
>> >> OnPad (date & time)
>> >> OffPad (date & time)
>> >> Start (date & time)
>> >> End (date & time)
>> >> Total (imported as text, converted to Currency during
>> >> append)
>> >> StrID (number)
>> >>
>> >> The Type, ChkNum, Total, and StrID are the only
fields
>> >> that are consistently populated.
>> >>
>> >> If I create a query that joins all of the fields
listed
>> >> above, I only get the duplicate records that have all
>> >> fields populated.
>> >>
>> >> Any suggestions?
>> >>
>> >> Thank you,
>> >> Jean
>> >
>> >
>> >.
>> >
>
>
>.
>

Then the records aren't "true duplicates" because they aren't the same all the way across.
You will need some sort of unique identifier to know whether or not they are duplicated or
are supposed to be another copy of one that is already there.

If this doesn't matter, then you can still use the query and draw links between the tables
on each field you are trying to compare. I just tried it and it works, except Null
apparently doesn't equal Null, so if you have a Null value in any of the fields that
record will be skipped.

--
Wayne Morgan
Microsoft Access MVP


"Jean" <(E-Mail Removed)> wrote in message news:112801c3a927$5b8cf910$(E-Mail Removed)...
> There is no unique ID field that is duplicated in both
> tables. The fields that I previously listed are the only
> fields that will be duplicates and none of them are unique
> ID fields. Although each table does have a unique ID
> field, they are not duplicates of each other.

As I stated in my original posting, I know the query works
if all fields are populated but I need to get all
duplicate records which includes records with Null fields
in both records (the import table and the destination
table).

Jean

>-----Original Message-----
>Then the records aren't "true duplicates" because they
aren't the same all the way across.
>You will need some sort of unique identifier to know
whether or not they are duplicated or
>are supposed to be another copy of one that is already
there.
>
>If this doesn't matter, then you can still use the query
and draw links between the tables
>on each field you are trying to compare. I just tried it
and it works, except Null
>apparently doesn't equal Null, so if you have a Null
value in any of the fields that
>record will be skipped.
>
>--
>Wayne Morgan
>Microsoft Access MVP
>
>
>"Jean" <(E-Mail Removed)> wrote in message
news:112801c3a927$5b8cf910$(E-Mail Removed)...
>> There is no unique ID field that is duplicated in both
>> tables. The fields that I previously listed are the only
>> fields that will be duplicates and none of them are
unique
>> ID fields. Although each table does have a unique ID
>> field, they are not duplicates of each other.
>
>
>.
>

You could run an update query on both tables to put some otherwise unused data in the Null
fields, just to populate them then run the query to check for dupes. When you are done,
you could run another update query to remove the bogus data and change it back to Null.
Just pick some value that will match the data type for the field and doesn't appear
anywhere in any of the records. This should work as long as none of the fields have only 2
values possible, such as a Yes/No field.

--
Wayne Morgan
Microsoft Access MVP


"Jean" <(E-Mail Removed)> wrote in message news:284001c3a968$301c9ba0$(E-Mail Removed)...
> As I stated in my original posting, I know the query works
> if all fields are populated but I need to get all
> duplicate records which includes records with Null fields
> in both records (the import table and the destination
> table).
>
> Jean
>
> >-----Original Message-----
> >Then the records aren't "true duplicates" because they
> aren't the same all the way across.
> >You will need some sort of unique identifier to know
> whether or not they are duplicated or
> >are supposed to be another copy of one that is already
> there.
> >
> >If this doesn't matter, then you can still use the query
> and draw links between the tables
> >on each field you are trying to compare. I just tried it
> and it works, except Null
> >apparently doesn't equal Null, so if you have a Null
> value in any of the fields that
> >record will be skipped.
> >
> >--
> >Wayne Morgan
> >Microsoft Access MVP
> >
> >
> >"Jean" <(E-Mail Removed)> wrote in message
> news:112801c3a927$5b8cf910$(E-Mail Removed)...
> >> There is no unique ID field that is duplicated in both
> >> tables. The fields that I previously listed are the only
> >> fields that will be duplicates and none of them are
> unique
> >> ID fields. Although each table does have a unique ID
> >> field, they are not duplicates of each other.
> >
> >
> >.
> >

Thanks. I hadn't thought of that. It sounds like it might
work. I'll give it a try and let you know.

Jean

>-----Original Message-----
>You could run an update query on both tables to put some
otherwise unused data in the Null
>fields, just to populate them then run the query to check
for dupes. When you are done,
>you could run another update query to remove the bogus
data and change it back to Null.
>Just pick some value that will match the data type for
the field and doesn't appear
>anywhere in any of the records. This should work as long
as none of the fields have only 2
>values possible, such as a Yes/No field.
>
>--
>Wayne Morgan
>Microsoft Access MVP
>
>
>"Jean" <(E-Mail Removed)> wrote in message
news:284001c3a968$301c9ba0$(E-Mail Removed)...
>> As I stated in my original posting, I know the query
works
>> if all fields are populated but I need to get all
>> duplicate records which includes records with Null
fields
>> in both records (the import table and the destination
>> table).
>>
>> Jean
>>
>> >-----Original Message-----
>> >Then the records aren't "true duplicates" because they
>> aren't the same all the way across.
>> >You will need some sort of unique identifier to know
>> whether or not they are duplicated or
>> >are supposed to be another copy of one that is already
>> there.
>> >
>> >If this doesn't matter, then you can still use the
query
>> and draw links between the tables
>> >on each field you are trying to compare. I just tried
it
>> and it works, except Null
>> >apparently doesn't equal Null, so if you have a Null
>> value in any of the fields that
>> >record will be skipped.
>> >
>> >--
>> >Wayne Morgan
>> >Microsoft Access MVP
>> >
>> >
>> >"Jean" <(E-Mail Removed)> wrote in message
>> news:112801c3a927$5b8cf910$(E-Mail Removed)...
>> >> There is no unique ID field that is duplicated in
both
>> >> tables. The fields that I previously listed are the
only
>> >> fields that will be duplicates and none of them are
>> unique
>> >> ID fields. Although each table does have a unique ID
>> >> field, they are not duplicates of each other.
>> >
>> >
>> >.
>> >
>
>
>.
>

That worked.

Thanks,
Jean

>-----Original Message-----
>Thanks. I hadn't thought of that. It sounds like it might
>work. I'll give it a try and let you know.
>
>Jean
>
>>-----Original Message-----
>>You could run an update query on both tables to put some
>otherwise unused data in the Null
>>fields, just to populate them then run the query to
check
>for dupes. When you are done,
>>you could run another update query to remove the bogus
>data and change it back to Null.
>>Just pick some value that will match the data type for
>the field and doesn't appear
>>anywhere in any of the records. This should work as long
>as none of the fields have only 2
>>values possible, such as a Yes/No field.
>>
>>--
>>Wayne Morgan
>>Microsoft Access MVP
>>
>>
>>"Jean" <(E-Mail Removed)> wrote in message
>news:284001c3a968$301c9ba0$(E-Mail Removed)...
>>> As I stated in my original posting, I know the query
>works
>>> if all fields are populated but I need to get all
>>> duplicate records which includes records with Null
>fields
>>> in both records (the import table and the destination
>>> table).
>>>
>>> Jean
>>>
>>> >-----Original Message-----
>>> >Then the records aren't "true duplicates" because they
>>> aren't the same all the way across.
>>> >You will need some sort of unique identifier to know
>>> whether or not they are duplicated or
>>> >are supposed to be another copy of one that is already
>>> there.
>>> >
>>> >If this doesn't matter, then you can still use the
>query
>>> and draw links between the tables
>>> >on each field you are trying to compare. I just tried
>it
>>> and it works, except Null
>>> >apparently doesn't equal Null, so if you have a Null
>>> value in any of the fields that
>>> >record will be skipped.
>>> >
>>> >--
>>> >Wayne Morgan
>>> >Microsoft Access MVP
>>> >
>>> >
>>> >"Jean" <(E-Mail Removed)> wrote in message
>>> news:112801c3a927$5b8cf910$(E-Mail Removed)...
>>> >> There is no unique ID field that is duplicated in
>both
>>> >> tables. The fields that I previously listed are the
>only
>>> >> fields that will be duplicates and none of them are
>>> unique
>>> >> ID fields. Although each table does have a unique ID
>>> >> field, they are not duplicates of each other.
>>> >
>>> >
>>> >.
>>> >
>>
>>
>>.
>>
>.
>