linked tables

[POLARBILLY]

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 -

 

[Armen Stein]

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

 

[POLARBILLY]

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

 

[Armen Stein]

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

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

 

[Polarbilly]

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