Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44

    Unanswered: Inserting data into an associative table

    This is the first time I have used an associative table. Basically my question is, when I create the form, how do I make the data go both into the table and the associative table? The idea is that a quote can be made from many shippers and shippers can have many quotes.

    The table structure looks something like this:

    tblQuote
    QuoteID (PK)
    QuoteDate
    DelvAddress

    tblShipper
    ShipperID (PK)
    ShipperName
    ShipperAddress

    atblQuoteShipper (Associative Table)
    QuoteID (PK)
    ShipperID (PK)
    Rate
    Miles

    I am using access 2007. I have attached my actual ERD from Visio
    Thanks in advance for what I am sure is a simple solution.

    Dan
    Attached Thumbnails Attached Thumbnails ERD_19-Aug-10.jpg  
    "Things are only impossible until they are not." ~Jean Luc Picard

  2. #2
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    Maybe a subform? Any thoughts?
    "Things are only impossible until they are not." ~Jean Luc Picard

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Form/Subform is ideal for any One-To-Many relataionship. The subform would be for the many side of the relationship and the form would be for the one side. For example, an Order has an Order Date, Order Number, etc. that would appear in the form. The subform would be all of the items (OrderDetails) that the person has ordered (the many side).

    When you add the subform to the form, Access will try to determine how the two forms are related. If it is obvious it will create a link between the two tables and manage the data as it is being entered. It will put the data in the many side and update or add anything entered in the form on the one side.

    In your case, I can see a Shipper form with a Quote subform. What I would do is to show Shipper information above and add a Continuous subform at the bottom of the form for all the quotes related to the shipper. I will show Record Selectors on the subform that allows the user to select a quote and either double click it to get a Quote Editor or have a Details button that brings up the Quote Editor for the given quote.

    The only thing I am not sure of is whether you have a true Many-To-Many relationship (three tables are required). Can a quote have Many Shippers?

  4. #4
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    Thank you DCKunkle,

    A shipper can have many quotes, and a single quote can be for many shippers, this is why I thought it necessary to bring in the 3rd table.

    Normally how the process works it that a customer will ask for a quote (single) from 1 to 8 different locations (shippers) all to a single delivery address.

    I think your suggestion is great. I think it may be easier if the Quote form is the main form with a continuous form for the shippers, thus allowing to the user to enter the delivery information one time on the quote form, then add the rate and other details to the subform for that shipper.

    Most of the delivery information is included on the actual quote as it is the same for each shipping location, the only thing that changes for each shipper is the rate, and the miles from shipper to destination.

    I will give it a try. Thanks for your help. I really appreciate it. I will post by solution back for others to read.
    "Things are only impossible until they are not." ~Jean Luc Picard

Posting Permissions

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