Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    One to Many Conundrum (2002)

    I have three tables: tblVendors, tblServices-Categories and tblServices.
    My client might have 1-10 Categories of services groups; each group having 1-80 records.
    Do I link the tables one-to-many: tblVendors, tblServices-Categories and tblServices.or tblVendors or tblVendors, tblServices and tblServices-Categories?
    Additionally, I have a table tblChartOfAccounts.
    There are 30 records that enumurate each account i.e Job Income receivable, plumber companies payable etc.
    I'm using this as a sort of template to give a picture of the status of each job.
    I have tblCustomers, linked one-to-many to tblJobs and then to tblChartOfAccounts. This is kind of dumb as I think I need a linking table from jobs to ChartOfAccounts to link each job to a new ChartOfAccounts but I want to keep the existing chart of accounts accounts records and just reuse them as a template.
    Well, it's saturday morning and this is what I have to solve. And it's such a beatiful day. Well your help is much needed and appreciated.
    Frank <img src=/S/basket.gif border=0 alt=basket width=58 height=52>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: One to Many Conundrum (2002)

    You wrote:
    >>I have three tables: tblVendors, tblServices-Categories and tblServices.
    My client might have 1-10 Categories of services groups; each group having 1-80 records.
    Do I link the tables one-to-many: tblVendors, tblServices-Categories and tblServices.or tblVendors or tblVendors, tblServices and tblServices-Categories?<<

    It is kind of hard to answer your question without knowing the major fields in these tables. That will give us a clue as to how they should be related. (Some examples would help, also).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to Many Conundrum (2002)

    Sorry, I wasn't clear enough.
    My client wants a pre-entered services form.
    This is just a list of records with pre-entered services i.e. 1 service call $100. Next record might be sqare feet of dehumidifing an area @ .50 foot. I have a quantity field where the entry operator just enters the quantity and a calculation is made and this record eventually is used to create a line Item on the Sales Invoice.
    I'm calling everyone a vendor even the company.
    When you go to the vendor form the first vendor is the company.
    A subform on a tab contol is services offered by the vendor and company.
    Services are filled out for a category i.e. water damage another category might be sewage damabe.
    Services are entered per category.
    When an invoice is to be created, the category is selected Quantities entered and a line Item is created.
    I'm not sure the one-to-many relationships.
    Frank
    I'll explain more if needed thanks

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: One to Many Conundrum (2002)

    It appears that you would have a one-to-many relationship between Categories and Services. However, I'm unclear how these relate to a vendor. It seems what you are creating is a pricing list, but is it vendor-specific? That is, is a service call $100 for every vendor, or do you want to create a pricing record for each Vendor specifying how much a service call will be?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to Many Conundrum (2002)

    You hit it on the head. Yes it is a price list or several prices lists that is vendor specific.
    lstVendors pulls up the first price list for the vendor and lstServices-Categories on this subform selects various price lists for that vendor only.
    Frank

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: One to Many Conundrum (2002)

    It would then seem like you need another table, one that specifies which services are available for each vendor. The records for this table would include fields for vendor#, service#, and price. This new table is on the "many" side of one-to-many relationships with both the Vendor and Service tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to Many Conundrum (2002)

    Thanks Mark.
    I actually used tblVendor->tblServices-Categories->tblServices (One-to-Many, One-to-many) this arrangement essenturaly creates a list of categories with the services for that category.
    Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •