Need through relationship

[pctechtv]

While creating a database that has Products, Suppliers and Lines I have come across the need to calculate a count of the suppliers from the context of a Products record. My relationship is like so:

My question if I want to pull a lot of information about Supplier is should my portal in the Products record be from Suppliers or from Lines? If the best answer is Lines, could I still have a count of related Suppliers by using the primary key from that table in the portal? Is it a bad idea to use fields from a related tables portal other than its own? Thanks

[jasonwood]

If you have a field in Products which is a count of suppliers, this will use your relationship and is completely unrelated to the decision on how to setup your portal.

If you want to know how many unique suppliers a product has, then I would use a calculation in Products that is a count on PK_Suppliers using your relationship. Depending on your setup, it may be sufficient to count FK_Suppliers from the Lines, but this would double-count suppliers that appear on more than one line (if this is possible in your system). It would also count Lines that have an invalid value in FK_Suppliers (a value that does not correspond to a Supplier).

As for the portal, if you only want to display information from Suppliers, then the portal should be to Suppliers (although it doesn't really matter in most cases), but if there is ANY information that you want to display from Lines, then the portal should be from lines. It is no problem to "extend" the relationship with a field from Suppliers in the portal to Lines, but you will have problems if you set up the portal to suppliers and then decide you want to stop half way to get some fields from lines - you'll end up with a line for each supplier but the additional data pulled from Lines will just be a repeat of the first matching Line record on every line of the portal.