Table Dilemma

[RowanG]

Hi All,

Im hoping you might be able to help, as I have a bit of a dilemma and I'm not sure of the best way forwards.

I have the following tables:

Tbl_PrimarySupplier - PK SupplierPrefix

Tbl_Secondary Supplier - PK SecondarySupplierID

Tbl_Packhouse - PK PackhouseID

Tbl_Farms - PK GrowerID

In an ideal world, every Primary Supplier would have Secondary Suppliers, meaning simple One-to-Many relationships. However, not every Primary Supplier does have a secondary supplier, which is where my dilemma comes in. I did try a junction table to tie SupplierPrefix, SecondarySupplierID & PackhouseID together in a PK but it didn't seem right, plus I could not get my head around how the Forms should work.

One solution would be to 'force' a PrimarySupplier to have at least one SecondarySupplier, but from an accuracy point of view this is not ideal.

Any thoughts would be greatly appreciated.

Thankyou.

 

[Simon_MT]

You could discern if the Primary Supplier and the Secondary Supplier are the same. To resolve my problem with multiple suppliers it was resolved in a non-normalised way by having a table with Supplier and, in my case, 3rd Party. In some circumstances there is Stock with a single Supplier whilst involve two Suppliers.

Simon

Simon

 

[wmphoto]

How exactly do packhouses, farms, primary and secondary suppliers relate in the real world. Depending on this, it may be that your table structure needs rethinking.

It sounds like your problem is that if a primary supplier doesn't have any secondary suppliers, you have a null in that field which means nothing is returned for that primary supplier at all whenever you're trying to create a query with an aggregate function for secondary suppliers. But I could be wrong.

 

[RowanG]

You could discern if the Primary Supplier and the Secondary Supplier are the same. To resolve my problem with multiple suppliers it was resolved in a non-normalised way by having a table with Supplier and, in my case, 3rd Party. In some circumstances there is Stock with a single Supplier whilst involve two Suppliers.

Simon

Simon

Hi Simon,

Thank you for that, but I think if I have understood what you are suggesting correctly, I would end up with an awful lot of duplicate data.

My response to wmphoto below might change things slightly.

Thank you
Rowan

 

[RowanG]

How exactly do packhouses, farms, primary and secondary suppliers relate in the real world. Depending on this, it may be that your table structure needs rethinking.

It sounds like your problem is that if a primary supplier doesn't have any secondary suppliers, you have a null in that field which means nothing is returned for that primary supplier at all whenever you're trying to create a query with an aggregate function for secondary suppliers. But I could be wrong.

Hi Wm.

It is basically a chain of contact. Me > Primary Supplier > Secondary Supplier > Packhouse > Grower.

In your second paragraph, you have hit the nail right smack bang on the head. Thus my logic of a Junction table. I suspect the only option might be to force all Primary Suppliers to have at least 1 secondary supplier but again am open to options.

Thank you
Rowan

 

[Simon_MT]

I see you are trying ito incorporate the entire Supply Chain. Then I would catagorise the Suppliers within a Junction Table:

1 Primary Supplier
2 Secondary Supplier
3 Packhouse
4 Grower

You would obviously have a table for Type of Supplier. It does not matter that a Secondary Supplier is present or not.

Simon

In fact ther is nothing to

 

[RowanG]

I see you are trying ito incorporate the entire Supply Chain. Then I would catagorise the Suppliers within a Junction Table:

1 Primary Supplier
2 Secondary Supplier
3 Packhouse
4 Grower

You would obviously have a table for Type of Supplier. It does not matter that a Secondary Supplier is present or not.

Simon

In fact ther is nothing to

Hi Simon,

Sorry I have been distracted by an Excel project this week.

Thank you for your answer. I do just have a couple of questions, given the 3 tables below as suggested, how would I illustrate the relationships between Primary Suppliers, Secondary Suppliers, Packhouses & Growers?

Tbl_Supplier
PK SupplierID

Tbl_SupplierType
PK TypeID

Tbl_Junction
CK SupplierID & TypeID

Sorry for being a pain, I'm struggling with something that seems it should be simple!

Thank you
Rowan

 

[Simon_MT]

You would input both the Supplier and Type using combi-boxes

Simon

 

[Simon_MT]

I been thinking about this:

How does the profile in the Supply chain apply to the produce. If assume that if produce is incoming then each delivery could have different sources or different produce?

Simon