CrossTab Query?

[Jim Shady]

Hi.

I've got a information source that provides the info to me each day as an
excel spreadhseet. I have imported this into Access and made a form based on
the table. I let Access add on a primary key. I have also added a column to
the table called Notes. So lets say the table has ID, NAME, ADDRESS, and
NOTES.

The next day my sources provides me with a new excel sheet. I now want to
import this into the table as the customer might have changed their address
for example.

I can add the info to the table, but it dublicates everything. I need some
way for Access to look at the second table, and pull into the 1st table
anything that has changed.

Is this possible? I guess using some sort of query?

Thanks - Jim Shady.

 

[BAC]

What you need here is some sort of unique key (Customer Number?) in the data
you receive from your source that you can use to tie the new records to the
old records in your Access table.

You can then run an update query by linking the two tables on this unique
ID. Then you run an Append query to add any new customers tthat came down in
the new source file.

This key has to be something that is not going to change, or would only
change VERY, VERY rarely (e.g. it would not do to build a key based on some
portion of the name concatenated with some portion of the address if name
and/or address changes are a common occurence in your world).

 

[David Lloyd]

Jim:

One approach would be to import the subsequent Excel worksheets in as a
second table. You can then create an outer join between the original table
and the second table, looking for rows where the records do not match. This
would include new records as well as records that have changed. You can
then create additional queries to insert and/or append data based on the
results of the first query.


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi.

I've got a information source that provides the info to me each day as an
excel spreadhseet. I have imported this into Access and made a form based on
the table. I let Access add on a primary key. I have also added a column to
the table called Notes. So lets say the table has ID, NAME, ADDRESS, and
NOTES.

The next day my sources provides me with a new excel sheet. I now want to
import this into the table as the customer might have changed their address
for example.

I can add the info to the table, but it dublicates everything. I need some
way for Access to look at the second table, and pull into the 1st table
anything that has changed.

Is this possible? I guess using some sort of query?

Thanks - Jim Shady.