merging tables

[bone head]

Ok I think I have gone brain dead today. I know this is so simple but it eludes my grey cells today.

I am creating a small database to identify tooling and usage.

Have one table detailing all the tooling info, and another table will identify usage repairs etc.

Am trying on the second table to enter the tooling number and it will pull up the info from the other table with all the info on it.

I know I must have done similar before, but can not find anything. also have searched forum but as I am not sure what I am looking for have not had any luck.

Anyone who can point this Knumbskull in the right direction

Many thanks in advance

 

[The_Doc_Man]

If the tooling numbes are the same in both tables, you can define some sort of relationship from the relationships pane.

1. If a strict one-to-one or many-to-one relationship exists. In that case you can define a relationship between the two tables. An inner join in an SQL query will work, or you can define a permanent relationship. Referential integrity is at least possible.

2. Some values exist in either table that do not exist in the other. (And this can be bidirectionally mismatched.) In this case, you can STILL define a relationship between the two tables, which in this case must ALWAYS be many-to-one. (With the understanding that in this case, "many" can mean "none" sometimes.) An inner join or a permanent relationship still work OK but this time, BE WARNED that you will have some unmatched records either way that require explicit (separate) management. Referential integrity is not possible.

3. If the table structure is such that the tooling number isn't the prime key for either table then you have a potential many-to-many relationship. In this case you need a bridging table that lists records from each table that are related to each other through their common tooling numbers. The bridging table might be a bit complex in this case. It would have to contain the prime key of each tooling info entry and the prime keys of each usage entry with matching tooling numbers. In this case, you will need a formal join query to link the information together. In fact, it might have to be a multi-layer query in order to get the joins right.

 

[bone head]

Many thnaks Doc_Man

I guessed it was something i was doing wrong have cracked it now, problem was how i had things set it was only letting me have an Intermediate relationship, which was causing the problems. a few minor changes and now have a many to one relationship which works fine

Cheers again for clarifying