Counting and Summarizing Related Child Records
I am losing my hair over this!
I posted earlier, but I don't think I articulated my problem very well.
We sell DVDs. You can buy a single dvd titie, or a bundle which contains multiple dvd titles (like a four pack).
I need to see how many of each DVD title has sold for a specified time period, which is a combination of individual sales and bundles.
Here are the tables I've set up:
ORDERS ----- ORDER LINE ITEMS ---- PRODUCTS ---- PRODUCT TITLES ---- TITLES
An ORDER will have one or many PRODUCTS, which contain one or many TITLES.
So two PRODUCT examples would be:
Product Name Titles Included
Gone with the Wind Gone with the Wind
Classic Movies 4-Pack Gone with the Wind, Casablanca, Citizen Kane, Star Wars
So if I sold one of each in 2011, the report would look like:
Gone with the Wind 2 copies sold
Casablance 1 copies sold
Citizen Kane 1 copies sold
Star Wars 1 copies sold
What approach do you think I should use to accomplish this?
Thanks so much.