Couting through relationships

[x14607]

I wasn't sure if this belongs in calculations or here, but being new to the relational database concept, I figured it's more of a relationship thing...

The question involves 3 tables:

Products, Resources and a Join table. Products and Resources each have an ID field of their own, both of which appear in the join table to link them.

I have a portal on the Products table that shows which resources are required to product that product (The portal is linked to the Join table, so that when productID matches, the Resource will appear in the portal)...

I've come up with a script to enter resources into the portal. Since it would be pointless to use a resource twice (there's a quantity field), I need to somehow COUNT how many times this resource exists via this product already. If that count is greater than 0, I know it would be a repeat.

What do I count? And where?

I've been able to count various things within the databse already, but this one I can't seem to get. In fact, the whole concept of context and calculating via relationships seems to have missed me somewhow.

I understand the concept in my head, but applying it seems hard for me to grasp. I find myself not knowing what should calculate from where, and how many tables it should go through, and if it has to 'stop' anywhere along the way...

If anyone knows of a thorough and easy to grasp explanation of the whole concept somewhere, I'd be interested. I wouldn't have so many of these questions then

Thanks for any help on this.

[Fenton]

Yes, this is more of a relationship thing. It sounds like you got the structure right, which always helps :-) What you want to count is whether a Resource is appearing twice for a particular Product. So the count would occur in the Product-Resources join table.

First you need a self-relationship on the ProductID within that table. Then count a reliable field of that relationship (say the ProductID field); it should be 1. However, it will not be 1 UNTIL you exit the field and the relationship evaluates.

So, if you want to Validate data entry by this relationship, in a calculation, it passes only if the relationship count is 0 (not 1), because Validation happens before the relationship is evaluated. I think; used to anyway. Try it.