Need help regarding lookup column

[malaydash]

Hi

I have a database in which I have one table for master_order_no & another table for sub_order_no & other details. Both the tables are linked by one-to-many relationship from master_order table to sub_order table through the master_order_no.

One master_order_no can have nos of different sub_order_no & any particular sub_order_no can appear in more than one master order. But the combination of master_order_no & sub_order_no makes the record unique.

Now I want to make a table for shipment details of every order (i.e. combination of master & sub order). I would like to key in the sub_order_no by myself & want to have a lookup column for master_order_no in which I want it to show only the list of all the master_order_no which relates to that particular sub_order_no I have keyed in earlier. I would also like to have the first value automatically appear in the field so that I can carry on with it without writing anything if it suits the particular record or else select a different one from the drop down list.

I have tried the lookup wizard & set the look up value to master_order_table/master_order_no but it is showing the complete list of master_order_no.

Any help please....

Thanks

 

[MarkK]

If one Master has many Subs and one Sub has many Masters then you have a Many-To-Many relationship. This is a three (3) table solution. You'll need 1) a Master table and 2) a Sub table and 3) a Link table between them to manage the links.
Do you have these three tables?

 

[malaydash]

Dear Lagbolt

Thanks for your prompt reply.

I understand these two field have many-to-many relationship & I have also defined them properly. My database is working pretty well for other issues. I have problem with the lookup column only in which I am doing some mistake somewhere in the procedure. Please elaborate the procedure for setting up a lookup column as per my requirement.

Regards

 

[gemma-the-husky]

to repeat what lagbolt said

if you have two tables exhibiting a many to many relationship, than a RDBS CANNOT manage them. you need to deconstruct these into two 1 to many links by adding a third junction table.

so not

masterorder n ...... n suborder

but

masterorder n ..... 1 masterorder/suborder 1 ........ n suborder


the new table in the middle stores the combined pairs to which you are referring as the unique indicator

 

[malaydash]

to repeat what lagbolt said

if you have two tables exhibiting a many to many relationship, than a RDBS CANNOT manage them. you need to deconstruct these into two 1 to many links by adding a third junction table.

so not

masterorder n ...... n suborder

but

masterorder n ..... 1 masterorder/suborder 1 ........ n suborder


the new table in the middle stores the combined pairs to which you are referring as the unique indicator

I have a separate table for master in which I have a list of all the master order, let's say 20. This table contains information regarding the clients who have placed that particular master order. Now lets assume I have 5 suborder for each master. Then it adds up to 100 orders which I have saved as another table called suborder details in which each record has mention of master_order_no & Sub_order_no along with other details pertaining to that suborder.

I have joined both of these tables by one to many relationship by joining the master_order_ no field which is a primary key field in the master table & a foreign key field in the suborder details table.

This arrangement is working pretty well with all the queries & reports I want to generate. But with lookup column it is showing all the list of master_order_no.

I tried the way it is suggested by Dave by creating an intermediate table but it is still showing the same result.

Actually I want to know "if two field x & Y have one to many relationship & if I key in x-value in one column, then, how would I get Y-values corresponding to that particular x in a lookup column.

Hope I succeed clarifying my point.

Thanks
Regards

 

[Brianwarnock]

I've never designed a many to many data base, if I did i might have done it like you, if then when I asked for help on an issue and 2 experienced guys said that I should have a third linking table, I would believe them. Why don't you?

Brian

 

[malaydash]

I've never designed a many to many data base, if I did i might have done it like you, if then when I asked for help on an issue and 2 experienced guys said that I should have a third linking table, I would believe them. Why don't you?

Brian

Dear Brianwarnock

I'm really sorry if it seems from my post that I am not listening to the seniors regarding creating a third table. However I have created the third table as suggested by Dave & lagbolt. I defined one-to many relationship from masterorders & suborders to the order details table. But I am still getting the same result in the lookup column in shipment table.

I think, what I am doing wrong is the setting of lookup column & not the relationship. Because in both the case I am getting the same result.

Anyway, what is the correct procedure to set a lookup column either in a one-to-many relationship or in many-to-many relationship?

Please explain the steps so that I can actually do it by my own

And sorry if I dishonored any of the senior members of the forum & I would like to thank all of them for their selfless services & helping hand they offer to us.

Waiting eagerly for the reply.

 

[Brianwarnock]

My apologies Malaydash I guess I missed that sentence.

I think that your problem is that you are populating your Combo from the table, it needs to be from a query filtered on the entry for the sub_order.
You will probably need to ReQuery the combo in the change or update event of the sub_order control.

Brian

 

[gemma-the-husky]

The thing is, it probably isnt enough just to create a third table

it also means that some of your data needs moving from one (maybe both) of the existing tables into the new table - and you cant construct a useful look up between the original two tables - the lookup needs to be between one of the original tables, andthe new third table.

 

[MarkK]

Consider the following structure.

[B]tMaster[/B]
MasterID (PK)
Data

[B]tMasterSubLink[/B]
LinkID (PK)
MasterID (FK)
SubID (FK)
DateCreated

[B]tSub[/B]
SubID (PK)
Data

Notice how powerful the tMasterSubLink table is. It is THE definition of all relationships between Masters and Subs. If a Master is related to a Sub there is a record to this effect in tMasterSubLink. If there is no such record, there is no relationship.

If you want every combination of every Master/Sub, then you use every record in tMasterSubLink. If all you want are the Subs of a specified Master you query that table with SQL like ...

SELECT SubID FROM tMasterSubLink WHERE MasterID = [SomeSpecifiedMasterID]

If all you want are the Masters of a specified Sub you'd use ...

SELECT MasterID FROM tMasterSubLink WHERE SubID = [SomeSpecifiedSubID]

This is sort of the industry standard for handling data having the structure you describe.

In theoretical terms you are dealing with two concrete objects and one abstract object. The concrete objects are your Master and your Sub and the abstract object is the relationship between the two. The abstract object doesn't have a tangible expression, but it is a real and discrete object in respect to your data model just like the others.

And it's not uncommon for the link table in a many-to-many to be the most dynamic. Consider the three table tClass-tClassStudent-tStudent system. When a student registers for a class we create a tClassStudent record and that will be where his final grade should go. All the data about that student in that class needs to be leveraged off that intermediate link!!!