1 Reply Latest reply on Feb 12, 2012 10:32 PM by philmodjunk

    relationship question



      relationship question


      I would like to create a small DB where I would keep an inventory of personnal items that would contain a location and a category filed on which I would like to be able to do calculation (how many item / category, $$$ / category, how many items/location, $$$/category) and I would also like to be able to drill down from a location down to an item and the same for category.

      I already tried to build my DB with 3 tables; Item (containing the item details and a location and category field), one table containing a list of all categories and a table containing a list of all locations.

      I've builded a relationship between item::category and category::category and another one between item::location and location::location, I tried to build reports that would show me for category::category all the items related (same for location) but all I have is one item per category that prints.

      thanks for any advice

        • 1. Re: relationship question

          A summary report based on the Item table with sub summary parts can produce  report with totals for each category and either the same layout or a different layout can produce the same time of report grouped by location. This produces two separate reports, but other than that, it does exactly what you describe. The way to set up such a layout is with sub summary parts "when sorted by location" and "when sorted by category". Summary fields computing total number and total value can then be placed in the sub summary parts to produce sub totals.

          If you do not want the report to list individual records and just show the sub totals, remove the body part from the layout.

          Note that sub summary parts will not be visible in browse mode unless the records are sorted to include the "when sorted by" field specified for the sub summary part.