Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    41

    Unanswered: What's the proper way to INSERT INTO a one-to-many relationship?

    For example, I have the situation where two tables, Company and Location, are in a one to many relationship. A company can have many locations.

    So if a sales guy is going to enter a NEW company, he has to enter some info on its location.

    How could this be achieved with SQL and VBA?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in access
    I'd create a topform which records everythign that is unique to the company
    eg name, website, phone, billing address etc
    and in a subform linked to the parnet form which records everything relevant to the location
    eg address, telephone and so on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Posts
    41
    Quote Originally Posted by healdem
    in access
    I'd create a topform which records everythign that is unique to the company
    eg name, website, phone, billing address etc
    and in a subform linked to the parnet form which records everything relevant to the location
    eg address, telephone and so on
    Thanks. That seems like a sweet and simple solution for that form.

    I have another form, however, with a similar problem... Sales Quote and Sales Price

    Each quote can have many prices (they can change while the quote is open)

    When a sales guy makes a quote, the form calculates the price, then when he clicks save, some info on the form is stored in Sales Quote while the prices are stored in Sales Price.

    Hopefully, that makes sense from a design point of view.

    For this one, I think I would need the SQL and VBA. Any thoughts?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    c\nt see why
    its the same basic idea
    a quote is made of many items
    so the items are a sub form to the parent quotation form

    being a developer I'd want to complicate things by using a tabel containign products, min/max price and so on.....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2009
    Posts
    41
    Thanks again, healdem...

    This one was a little different, since it's specific to the industry... the quote can only have a single item, but that item's price can fluctuate... so the sales guy might want to update it to reflect the new price.

    In this case, I just created a query with the two tables, and selected all the fields that were required.

    In VBA, I simply did an insert into the query, instead of trying to do two inserts into the two separate tables, and this worked fine.

Posting Permissions

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