Relating Tables

[Dr.L]

Well I am now getting cross-eyed trying to figure out what I missing...

Simply I have a table [table2] with four fields [code_ID, code_Name, Code_description, Code_pricing] where I want to get info for another table [table1]. I can get informationf from table2 by relating the code_ID from the first table. The relationship does bring up the correct relating information. However when I add new fields by duplicating the exisiting fields and changing the names [i.e. code_Name2, code_Name3, etc.] to table1, I can only populate the relating fields to one collection of relating information tghroughout all relating fields.

My relation is code_ID = code_ID which seems to be the answer, yet is not. I have attemped to add code_Name to the relationship and even added another table. But... Help please...

Much thanks...

Dr.L

[comment]

Well I am now getting cross-eyed trying to figure out what I missing...

And I am in danger of getting cross-eyed trying to figure out what you mean... Please, never use abstract names like "table1".

However when I add new fields by duplicating the exisiting fields and changing the names [i.e. code_Name2, code_Name3, etc.] to table1,

Whenever you find yourself appending numbers to your field names, that's a sign of bad data structure. You are most likely missing a third table between Codes and [whatever that is].

[Dr.L]

My tables are DataFile and DX_Codes. I am trying to populate a form with DX codes from the DX_Code table into the DataFile.

In order to keep the information separate I've created fields in the DataFile where I need the information from the DX_Codes to come in from. That's why I've used Code_ID = Code_ID...

Does that help some?

Dr.L

[comment]

Please, never use abstract names

My tables are DataFile and DX_Codes.

Do you see the humor here?

Anyway, if your relationship is =

Codes::CodeID = Objects::CodeID

then the relationship is a one-to-many - meaning that each object has only one code. If you need more that one code per object, then you have a many-to-many relationship, and you need to add a third table to join between the two.

[Dr.L]

So, if I understand you then my first table to the second table would be:

Codes::Code_ID=Code_ID::Code_ID

and then from the second table to the third Code_ID::Code_ID=Code_ID::Code_Definition?

That should provide me with a one to one relationship?

Is that correct??

Dr.L

[comment]

Is that correct??

No, I'm afraid not. Can you explain what are you trying to do?

[Dr.L]

Much thanks for your patience...

I am attempting to enter 4 unique codes into a form. Along with that I'd need descriptions and prices for each which I'd like to generate by simply entering the codes. I'd like to house the codes, description and pricing in one table and just pull the appropriate information into the other. Any other information you'd need?

Dr.L

[comment]

I suggest you have look at this:

http://fmforums.com/forum/showpost.php?post/309136/