I’m trying to find the count of Transaction ID’s associated with Client ID’s enrolled in certain purchasing agreements for products. I have two tables – one has the raw transaction data (Transaction ID, Product Type, Client ID), the other has the enrollment data for those purchasing agreements (Product Type, Client ID).

Both of these tables have duplicate values in Program Type and Client ID, and I’m trying to find the count of transactions associated with each client/program combination. The issue is that clients have purchasing agreements for multiple products, and transactions for products that they do not have agreements for as well, so I need to filter those out.

What I’m ultimately trying to get is a PivotTable with Product Type, Client ID, and then the count of associated transactions.

| Product/Client | Transaction Count |
| Product A | 20 |
| – Enrolled Client 1 | 14 |
| – Enrolled Client 2 | 6 |
| Product B | 34 |
| -Enrolled Client 1 | 17 |

I’ve tried creating relationships between the Product Type and Client ID columns by creating extra worksheets with only those columns as tables. I then created relationships from [Transactions] -> [Product Type] <- [Purchasing Agreements], and I’ve done the same with the Client IDs.

I can create a Pivot Table with all data sources available because of this, but when I try to get a count of Transaction ID’s in the Transactions table against the Client ID’s it just gives me a count of all Transaction ID’s on every row with a popup saying, “Relationships between tables may be needed.” There are no other columns with shared values I can create relationships between.

I do have access to SSMS if this would be easier to do in there, but I have a very limited grasp of SQL. Any assistance would be greatly appreciated.