Analyze customers that have purchased a specific product
Hi. I've posted a couple times here this week trying to work through a problem and have received great feedback. Let me try to explain a little more clearly what I'm trying to accomplish, and where I am having difficulty:
I have a fairly standard customers -> orders -> line items - > products - > prod titles - > titles
database where customers have orders of products that are made up of multiple titles
and I have been struggling with how to analyze "For customers that have 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 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).
Any ideas or help would be greatly appreciated.