Hi. I have a fairly standard customers -> orders -> line items - > products - > prod titles - > titles
database where customers have orders of products (buy products) that are made up of multiple titles.
The things we sell (our products) are DVDs, and sometimes we sell individual DVDs, and sometimes we sell a bundle of them, like a four or 10 pack. So a product is made up of titles.
and I have been struggling with how to analyze "For customers that own title a, what other titles do they own?"
Currently I can run a report based on my titles table, sorted by title name, and displays a summary field in my customers table that counts customers. This gives me a count of customers that own a title.
(It seems a little counter-intuitive to me that this report should be based on the titles table since I really want to analyze customer information, but when I try to run a similar report on customers sorted by titles, it only counts each customer once (according to the first title they purchased).
Now, I have a customers layout that has portals to other tables all the way down to titles where I can perform a find on a particular title name and get a found set of all customers that own a particular title.
It seems if I could run my title report against just that found customer set, it would produce the results I want, but I can't figure out how to do it.
- I've tried "go to related records", but matching a found set of related title records to customer records isn't what I need.
- I've used a List function in a calculated field in my customer table to drill down and collect all titles the customer owns, so that data is there in a carriage separated list by title name.
- I've spent a lot of time researching table occurrences trying to build in some parameters to "hard code" the found set query, if you will.
Once I figure out the basic technology or methodology to accomplish this, I want to generate a report that looks at each title and shows how many customers own it, then below that loops through all the titles and shows OF THOSE CUSTOMERS the ownership distribution (if any, besides the 100% for said title).
I've attached a relationship graph and a mock up of the report.
Any ideas or help would be greatly appreciated.