By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,218 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Update and append data in one table from data in another table

P: 62
Hello -

I have a local table in my database called t_CompanyData that holds a subset of the fields in another table called dbo_INT_AUX_LISTING.

dbo_INT_AUX_LISTING is a linked table via an ODBC connection. This is a table in our CRM system and the data in that table is updated constantly.

I need to run a query to:
1 - update the data in t_CompanyData with any updated data for related records in dbo_INT_AUX_LISTING

2 - append any NEW records in dbo_INT_AUX_LISTING to t_CompanyData
I wrote an query that updates data and adds any new records using the strategy at
http://support.microsoft.com/kb/127977

New records are being added to t_CompanyData. However, the value in dbo_INT_AUX_LISTING.LISTING_ID field is not being added to t_CompanyData.IA_CompanyLISTING_ID.

I have verified that the LISTING_ID field in the dbo_INT_AUX_LISTING table is a NUMBER datatype (not autonumber). It is a primary key.

Expand|Select|Wrap|Line Numbers
  1. UPDATE (dbo_INT_AUX_LISTING LEFT JOIN t_CompanyData 
  2. ON dbo_INT_AUX_LISTING.LISTING_ID = t_CompanyData.IA_CompanyLISTING_ID) 
  3. LEFT JOIN dbo_INT_AUX_DIRECTORY 
  4. ON dbo_INT_AUX_LISTING.OWN_DIR_ID = dbo_INT_AUX_DIRECTORY.DIRECTORY_ID 
  5.  
  6. SET t_CompanyData.IA_CompanyLISTING_ID = [dbo_INT_AUX_LISTING].[Listing_ID], 
  7. t_CompanyData.COMPANY_NM = [dbo_INT_AUX_LISTING].[Company_NM],
  8. t_CompanyData.DIRECTORY_NM = [dbo_INT_AUX_Directory].[directory_NM];
  9.  
Any suggestions greatly appreciated.
Sandra
Jan 19 '12 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,271
The settings of t_CompanyData.IA_CompanyLISTING_ID are of far more interest Sandra ;-) If this is an AutoNumber field for instance, it would fail.

PS Congrats on a well prepared question. Such quality from newbie posters is as rare as the proverbial R-H S.
Jan 20 '12 #2

P: 62
Thanks, NP ;)

t_CompanyData.IA_CompanyLISTING_ID is a number - long integer.

Any other clues I can send along?
Jan 20 '12 #3

NeoPa
Expert Mod 15k+
P: 31,271
I'm grasping at straws here Sandra, but is it involved as a foreign index to another table maybe? Does it feature in the Relationships diagram?

Frankly I'm surprised at the described behaviour.
Jan 20 '12 #4

P: 62
dbo_INT_AUX_LISTING is not part of the Relationships diagram.

Here are a few images - hope this helps.





Jan 20 '12 #5

Rabbit
Expert Mod 10K+
P: 12,324
Why store the company name and directory name if they're already stored in the other tables? When you need them, you can just join the tables together.
Jan 20 '12 #6

P: 62
When I first set up this database it was taking a very long time to load the data from the ODBC tables. They have done some upgrades to our servers and now things are nice and fast.

I think it may be best for me to re-configure the back end to get data directly from the ODBC tables instead of going through this update/append process. Hopefully this wont be too much of a hassle :-)

Thanks all!
Jan 20 '12 #7

NeoPa
Expert Mod 15k+
P: 31,271
I see the relationship, but I cannot think of anything that would explain the bahaviour. Even if all the restrictions are set up the value, by definition, must be a valid one as it's the same as the one it's linked to (having been copied from it). I don't know the problem I'm afraid.
Jan 20 '12 #8

P: 62
Thanks NP - maybe some issues with the install. I have been having other strange problems so I might re-install Access.
Jan 20 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.