1 Reply Latest reply on Aug 31, 2009 8:37 AM by philmodjunk

    mUltiple customers, each with multiple records



      mUltiple customers, each with multiple records


      Hi. I have an interior decorating business and I need instructions on how to set up a database with multiple tables. I have already set up 3 of those tables. 1 for general information about the customer, 2nd for tracking options and inventory that the customers have purchased or budgeted to purchase, 3rd for invoicing these customers. 

      Right now i am trying to figure out a setup that will allow me to go to one customer and enter in several items such as a sofa, table, lamp, etc that they have purchased (and the details about the item including price and pictures) with each item being a separate record under that customer. 

      Then I would like the database to allow me to sort that inventory into a table (in tab view) by room. For example, when i click the "living room" tab I show a table that contains each item (record) that was either purchased or not for that specific room. Then check another tab and see other items/records that were purchased for the "dining room". I also need for each item purchased to calculate or subtract from a given budget.

      How would I accomplish this?


        • 1. Re: mUltiple customers, each with multiple records

          I suggest you add at leaset two more tables. Add a line item table for listing each item purchased on a given invoice. You can link this table to you invoice table by an order or invoice number. The line items records can be displayed/edited/created/deleted in a portal placed on a layout based on your invoice layout. Fields in a typical line item table would include at least:  Item Id, qty purchased, unit price, Cost (Unit price * Qty)


          In your case, I'd add one more field, Room. This field will allow you to create a report based on your line items table that will allow you to pull up all line items for a given invoice and sort them by room to get the report you've described.


          The other table that I recommend is some kind of price list or catalog table. This table lists the items and their unit prices. When you create a new invoice, each time you add a line item record, the unit price would be copied (looked up) from the price list table. (This method allows you to change prices in the price list table without the change affecting the prices shown on existing invoices.)