Better approach to finding a sum from reverse reference table using reference value

Ask for Help
,
1

Hi,

I am having issues returning a sum from a column (which has calculations) that is from a different table (this table happens to be a reverse reference table) by filtering by said reference values in the reference’s value column from said table. I also need to be able to find a sum from that same column in that other table but this time using a particular value as the reference value. To help map out what it is that I am doing, here are some of my tables, columns, and how they are currently connected to each other.

I have a couple of tables:

  • Product Connection
  • Book Name List
  • Product Name List
  • Website Name List
  • Decade Name List

In the Product Connection, I have columns that include:

  • Product Name
    • (referencing table “Product Name List”)
  • Book Name
    • (referencing table “Book Name List”)
  • Website Name
    • (referencing table “Website Name List”)

In the Book Name List Table, I have columns that include:

  • Book Name
  • Date Published
  • Decade Name List
    • (referencing table “Decade Name List”)
  • Number of Products
    • (count based on Book Name value from the Book Name column from the Product Connection Table)

*I have one value in the Book Name column as “Website”

In the Product Name List Table, I have columns that include:

  • Product Name
  • Number of Total Sources
    • (count based on Product Name value from the Product Name column from the Product Connection Table)
  • Number of Website Sources
    • (count based on Product Name from the Product Name column from the Product Connection Table AND the value in Book Name column from the Product Connection Table equals “Website”)
      • *********this calculation is one of the ones that I am having an issue with
  • Number of Book Sources
    • (Number of Total Sources minus Number of Website Sources)

In the Website List Table, I have columns that include:

  • Website Name
  • Number of Products
    • (count based on Website Name value from the Website Name column from the Product Connection Table)

In the Decade Name List Table, I have columns that include:

  • Decade Name List
  • Number of Books
    • (count based on Decade Name List value from the Decade Name List column from the Book Name Table)
  • Number of Products
    • (SUM based on Decade Name List from the Decade Name List column from the Book Name Table AND the value in Book Name column from the Book Name Table equals “Website”)
      • *********this calculation is one of the ones that I am having an issue with

I hope that makes sense as far as which calculations I am having issues with