If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
CrossTab Query?
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. |
#2
|
|||
|
|||
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). "Jim Shady" wrote: 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. |
#3
|
|||
|
|||
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. "Jim Shady" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form input in query, Union and crosstab query gives error msg | Christian | Running & Setting Up Queries | 2 | November 17th, 2004 05:00 PM |
crosstab query problem | Michael S. Montoya | Running & Setting Up Queries | 1 | November 4th, 2004 09:48 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
crosstab query | chris morrison | Running & Setting Up Queries | 2 | July 12th, 2004 08:57 PM |