tables linked with field that can accept duplicates!!!!

[weezles]

Hi

I'm trying to salvage data from a database where the tables are linked on a field that can contain duplicates. Can anyone tell me which record is likely to link to the correct corresponding record in sub table? ie will it be the last one entered?

Any help on this would be grately appreciated.

Louise

 

[SarahG]

Unfortunately this is not an Access problem, it's a database design problem so there is no quick answer.
I would re-check with your colleagues/hand-over documentation to see if the tables are linked by more than one column. Sometimes two or more columns combine to uniquely identify a record. By talking more to the users, you may figure out how to extract the data.

Failing that, you have a data-cleansing exercise on your hands. If the data is a manageable size, provide an exception report of all those in the main table which are duplicated with the associated data and ask a user to link the data manually. And most importantly add on a unique field of your own to the data which will link the data.
Sorry I can't be more help.

 

[KenReay]

Hi

You say "ie will it be the last one entered?" in a table there is no concept of ORDER. Order is only imposed via a query with an ORDER BY clause. Your question implies that the rows of the table may have a unique key, an Autonumber perhaps, which provided it a next sequential number autonumber and not a random one, may help you detrmine the sequence of insertion, but that does not necessarilly help with your question. It would be necessary to understand the application and what it is doing before an educated guess as to the "correct" record could be made

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[willir]

Louise

tables are linked on a field that can contain duplicates

Well, first it is hard to provide a specific answer since the details provided are vague.

This could be by desing, as in a one-to-many relationship design. For example, one house hold can have many family members, and perhaps more than one phone number. Likewise, an invoice can have many detail items.

Or it could be problem because of poor design.

Look at the design of the two tables - do not change any thing. You want to see a primary key on each table. The primary key is the unique identifier for each record. It can be as simple as autonumber, or complex to include several fields. The primary key is recognized by the "key" icon next to the field or fields.

If there are primary keys for each table, then things are looking good - each record can be retrieved.

If there is no primary key for one of the tables (i.e. the "sub table" as you describe.), then Access can not retrieve a specific record or records, and data integrity is at risk. For example, you can have two siblings called MaryJane both with the same date of birth in the same household -- not likely.

Richard