cumulative history of purchases by referrals

[corebe]

I am looking for a query for a report that will give me the number of each
specific product model which have been purchased by any and all referrals of
an affiliate.

I have among other fields, fields in my affiliates table for affiliate number,
affiliate name, referred by, referral number, referral name, purchased model,
purchase date.

Each affiliate can make multiple referrals and typically referrals will only
buy one product although they could buy more than one.

The idea is that the commission owed to an affiliate will increase depending
on which level they have reached in sales purchased of any particular product
made by their referrals. Thanks

 

[S.Clark]

If I were doing this, along with your Affiliates table, I would also have a
Customers table with the Affiliate ID. There would be an Order and Order
Details table, too.

So, my query would have the tables Affiliates, Customers, Order, & Order
Details... all linked by their proper matching IDs.

With that, you would need to count the number of ProductIDs (from the Order
Details) grouped by the AffiliateID in the Customers table.

 

[corebe via AccessMonster.com]

A friend of mine came up with this solution using just the fields that
already exit in this Affiliates table:

SELECT affiliates.[Referred By], affiliates.[Purchase Item], count(*)
FROM affiliates
WHERE affiliates.[Purchase Date] is not null
GROUP BY affiliates.[Referred By], affiliates.[Purchase Item]
ORDER BY affiliates.[Referred By] DESC , affiliates.[Purchase Item];

I wonder what the advantage might be in the long run using your type of
solution versus this?

Thanks

If I were doing this, along with your Affiliates table, I would also have a
Customers table with the Affiliate ID. There would be an Order and Order
Details table, too.

So, my query would have the tables Affiliates, Customers, Order, & Order
Details... all linked by their proper matching IDs.

With that, you would need to count the number of ProductIDs (from the Order
Details) grouped by the AffiliateID in the Customers table.

I am looking for a query for a report that will give me the number of each
specific product model which have been purchased by any and all referrals of

[quoted text clipped - 10 lines]

on which level they have reached in sales purchased of any particular product
made by their referrals. Thanks