 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008linked tables
I have a fixed database comprising circa 30,000 records - all unique service record numbers. I am importing 2 linked tables which have multiple records against the same serivice record number. My problem is that I only want them to link on a one to one basis. As there appears to be very little you can do to alter linked table parameters I'm stumped.... Help please - > I have a fixed database comprising circa 30,000 records - all unique service >record numbers. I am importing 2 linked tables which have multiple records >against the same serivice record number. My problem is that I only want them >to link on a one to one basis. As there appears to be very little you can do >to alter linked table parameters I'm stumped.... > Help please - I'm unclear on your problem... If there really are duplicate records in the two new tables that link to each service record number, then a one-to-one relationship isn't possible. What will you do with the duplicates then? Armen Stein Microsoft Access MVP www.JStreetTech.com Hi Armen, good question....I am used to working with Lotus Approach which only shows the first record of each link. If you want to show more it has a separate procedure. Effectively I want to "ignore" or "delete" the other records.Possible scenario Table 1 Table2 Table3 45000 45000 45000 45001 45000 45000 45002 45001 45000 I just want one record from each table.....If this is not possible then I will need to consider deleting the records when I update the linked table. thanks for your time Bill > > I have a fixed database comprising circa 30,000 records - all unique service > >record numbers. I am importing 2 linked tables which have multiple records [quoted text clipped - 12 lines] > www.JStreetTech.com > >Hi Armen, > good question....I am used to working with Lotus Approach [quoted text clipped - 9 lines] >will need to consider deleting the records when I update the linked table. >thanks for your time Are you sure you don't want one-to-many relationships? Are there any other fields in the duplicate records in tables 2 & 3 that you need? If you really want one-to-one and remove the duplicates: If this is a one-time thing, and you won't need to get the external tables again, you can create new tables for 2 & 3 and make their keys unique. Then run an append query to copy all the records in. The first record for each key will make it in, but all the duplicates will fail with error messages. If you need to keep these external tables up to date, the problem is more difficult. You could repeat the above routine periodically. But to have the data truly live from the other system, you will need to build totals queries on tables 2 & 3 that eliminate the duplicates, then join your table 1 with those queries. This resulting query will not be updatable (since it involves a aggregate/totals query) but you'll at least be able to see how the records match. Hope this helps, Armen Stein Microsoft Access MVP www.JStreetTech.com Hi Armen, thanks for the suggestions. As I am setting this up to handover to a non 'geek', I will have to reconsider my options on this. I can either have the data pre filtered at SQL Server level, or using Xcel and Microsoft Query create single record files, and then create linked tables in Access. Once again, thanks for your help Best Regards Bil > >Hi Armen, > > good question....I am used to working with Lotus Approach [quoted text clipped - 35 lines] > www.JStreetTech.com > |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.