2 Replies Latest reply on Aug 4, 2011 8:46 PM by JohnWoods3256

    Counting and Summarizing Related Child Records

      Title

      Counting and Summarizing Related Child Records

      Post

      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

      where:

      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:

      2011

      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.

      Jason