Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Unanswered: Multiple Shipping Address

    Here is my relative table structure.

    tCustomers
    tOrders
    tOrderDetails
    tShipToAddress

    My problem is that each customer will have multiple shipping address.

    First I had the PK of tShipToAddress linked to tCustomers. This is fine for relating the shipping address with the customer, but then I realized that that will not save the ShipToAddress in the OrderDetails table when a new order is placed. How can I have the ShipToAddressID saved in the Orders or OrderDetails table?

    Sorry but I am going crazy over this. I have thought about it way to long.

    Thanks for all of the help.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Obviously, you will need something in common between all your tables and I would say that you have the Customer Name field in each table. Set up a relationship and have all the shipping addresses display (perhaps in a subform) for the customer selected.


  3. #3
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Here are my current relations. I think the States links are wrong and I still haven't figured out how to link my ShipToAddress to Orders without duplicating to much data. Please help if I am going at it all wrong.

    Thanks
    Attached Thumbnails Attached Thumbnails test.jpg  

  4. #4
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Poor me

    Is it me or does the right half of the relations view look really messed up. What a mess I have gotten myself into.

    Maybe I should sleep a little. Never hurts.

  5. #5
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Re: Poor me

    Maybe is better to link addresshiporders with order, instead of with customers

    Another thing is that you have some cycles on the right of your referential scheme. This can yield some problems in the future.

  6. #6
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Re: Poor me

    Another thing is that you have some cycles on the right of your referential scheme. This can yield some problems in the future. [/SIZE][/QUOTE]

    Any suggestions on how to fix them. I know they don't look correct but Im not sure what to do about them.

    Thanks

  7. #7
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Re: Poor me

    I don't understand why you create 2 tables about state and country.
    Just to store the names? You can avoid these 2 tables and store just the name of the country and the state in the corresponding field.

  8. #8
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Re: Poor me

    Originally posted by pipeio
    I don't understand why you create 2 tables about state and country.
    Just to store the names? You can avoid these 2 tables and store just the name of the country and the state in the corresponding field.
    I just done that for a lookup field for the users. Is this a bad idea?

  9. #9
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    My main problem is--

    1 common customer will have many ShipToAddress. Lets say 5 for example. When a new order is placed I would like to have a form based on customers with a subform of ShiptoAddress so the user can select the customer and then select from the available ShipToAddress for that customer. How can I do this and save the ShiptoAddress in the orders table. I am really having problems with this.

    Thanks for all of the help.
    Last edited by hooks; 03-19-04 at 15:01.

  10. #10
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Re: Poor me

    Originally posted by hooks
    I just done that for a lookup field for the users. Is this a bad idea?
    You can do the lookup with a SQL statement against the field, instead of use the table.

  11. #11
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Re: Poor me

    Any Help with the actual table layout would be much appriciated

  12. #12
    Join Date
    Jan 2004
    Location
    Orlando Florida
    Posts
    5
    I will be trying to create the same setup next week. Here is a tutorial that I will use. I am new to this so may not be of much help but I'll let you know my results.

    http://www.fontstuff.com/access/acctut10.htm

Posting Permissions

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