append query

[dede]

I have two table

table1 (excel link) table2
field1 field2 field1 field2
09-006 2020 09-006 2020
09-006 2021 09-006 2021
09-006 2022
09-007 2020

How could I find only the two last value from the excel table to be append
them in the table2 ?

 

[Jeff Boyce]

If you already have the four records in your example in your linked Excel
table, why do you need to have exact duplicate records in your table2?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[dede]

Hi Jeff
I need to duplicate this Excel sheet because this one is updated by an other
program which erases all info from the past year!!
Thank's for your help

 

[Jeff Boyce]

Is there anything unique about the records? From your example, it looks
like your [field1] is potentially a unique record/row identifier. If it is,
first set an index (no duplicates) on your Access table.

Then do an append query from your linked Excel table to your table2. When
Access encounters a "duplicate" key, it refuses to append it. When your
append query is done, only those that did not have matching keys would be
added.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[dede]

Hi Jeff
No, I do not have any unique records. In my access table. I have a
autoNumber as Primary key !
I'm able to retrieve those already imported, if I create a query with two
joins 1: Ony include rows where th joined fields ......
but not the other one !!
Cheers

Is there anything unique about the records? From your example, it looks
like your [field1] is potentially a unique record/row identifier. If it is,
first set an index (no duplicates) on your Access table.

Then do an append query from your linked Excel table to your table2. When
Access encounters a "duplicate" key, it refuses to append it. When your
append query is done, only those that did not have matching keys would be
added.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff
I need to duplicate this Excel sheet because this one is updated by an other
program which erases all info from the past year!!
Thank's for your help

 

[Jeff Boyce]

I was less concerned about the destination (table2) than the input (Excel,
linked table1). If you have no way to determine that a record is already in
table2, how do you propose to add only those that are not?

Whatever it is that gives you a unique record in table2 (whatever
combination of fields) create a unique (no duplicates) index on that
combination of fields. Then, when you try to append to it, if the record in
table1 already exists in table2, Access will refuse to (re-)add that one.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff
No, I do not have any unique records. In my access table. I have a
autoNumber as Primary key !
I'm able to retrieve those already imported, if I create a query with two
joins 1: Ony include rows where th joined fields ......
but not the other one !!
Cheers

Is there anything unique about the records? From your example, it looks
like your [field1] is potentially a unique record/row identifier. If it is,
first set an index (no duplicates) on your Access table.

Then do an append query from your linked Excel table to your table2. When
Access encounters a "duplicate" key, it refuses to append it. When your
append query is done, only those that did not have matching keys would be
added.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff
I need to duplicate this Excel sheet because this one is updated by an other
program which erases all info from the past year!!
Thank's for your help


:

If you already have the four records in your example in your linked Excel
table, why do you need to have exact duplicate records in your table2?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have two table

table1 (excel link) table2
field1 field2 field1 field2
09-006 2020 09-006 2020
09-006 2021 09-006 2021
09-006 2022
09-007 2020

How could I find only the two last value from the excel table to

be
append

them in the table2 ?

 

[Brett Stone]

If the 2 fields concatenated together create a unique record, then run an
"unmatched" query against the concatenated fields

 

[dede]

Many thank's Jeff & Brett
Yes it's work very well with the "unmatched" query
Best wishes & all the best for 2009