Difficulties Counting Related records

[reichwaldl14]

I am having trouble counting related records between 2 databases.

I have an Acquisitions Database and a Products Database. Each acquisition produces multiple products. Each Acquisition can be acquired multiple times but at a different price with a different # of products coming from the acquisition.

Here's what I've done: I've related the product quantity table (related to the product table by a sku)in the Product Database with the Acquisition table in the acquisitions DB through an Acquisition ID and a date Acquired. The product quantity record records if the item is in stock and the cost to acquire for that item. In order to determine the cost to acquire, I need to determine how many products the acquisition created (this is where the counting comes into play). I've tried to create a relationship to count the number products created from each acquisition. In the Acquisition table, I created a field with count(Quantity::Sku). So far I can only get a count of the total number of records in the product quantity table or no result depending on the type of join I use (cartesian or =, I know I should learn the difference in how to use these concretely, but it always escapes me.) The result is not filtered by Acq ID and Date Purchased.

Help?

If I am not being clear, please ask for clarification. I am terrible at relaying the Database Schema in words. Thank you!

[comment]

I have lost you here:

Each Acquisition can be acquired multiple times

Can you elaborate on the meaning of acquisitions and products and how they are related in real life?

[reichwaldl14]

We buy a used car for parts. 2 different 1990 Toyota Camry's. It is the same used car (Year and Model), but can have a different number of usable pieces and each has its own acquistiion cost.

Make sense?

By the way, when I create a portal and display related records, it shows the records I need to count. Makes me think the relationships are correct.....

[comment]

when I create a portal and display related records, it shows the records I need to count.

OK, perhaps we could start here instead of going all the way back. So what is the relationship behind this portal?

[reichwaldl14]

I now suspect that this is an across two databases issue as I cannot see the records in the portal when I use a portal in the Acqusition Database, only in a portal in the Product Database.

The relationship is Quantity::ACQID = Acquisitions:: ACQID AND Quantity::Date of Acq = Acquisitions:: Date of Acq

[reichwaldl14]

Think I figured it out. Had an unstored calculation in one of the relationships. Thanks for looking into this. And I apologize if I wasted your time.