Results 1 to 5 of 5

Thread: How to insert multiple records against single order id/ primary key

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Posts
    154

    How to insert multiple records against single order id/ primary key

    Hi. When I purchase medicine or any grocery from the shopping mart. So the final receipt which i receive has an
    Order ID
    against which there are several items which I have purchased. Similarly Against a patient MR No. He/ she gets multiple tests or medicines which has the same idea of having several records against 1 Primary Key.

    How it happens?

    I'm learning web development and this is absolutely for my practice to working on different tables and web pages to improve my practice.

    In my case: I have created a cart table where the sales man keeps adding the items which is purchased/ ordered by the customer. Its basically a kind of shop where the walking customers come, purchase, pay and take away their orders or dined in. On btnAdd the items are added in cart table where Cart_ID is the PK. What I want that Once the customer finishes his purchasing, so the sales man clicks the Sale Button and the data goes from CART table to the Orders Table, leaving the Cart table always empty.

    Question?
    1. what I want that just like the above example of MR No. or Order ID I want that all these items of the customer are stored in the Order Table against single Primary Key/ Order_ID.

    My Cart Table:
    cart_id (PK)
    item_id (FK)
    item_unit_price
    order_qty
    order_price
    order_of
    order_instructions
    order_date
    token_id (FK)
    Each customer is given a token in the shop which he submits back when he takes away the order or he is dined in.

    My order Table
    order_id (PK)
    item_id (FK)
    item_unit_price
    order_qty
    order_price
    order_of
    order_instructions
    order_date
    token_id (FK)
    Here is my Sale Table:
    sale_id (PK)
    order_id (FK)
    total_payable
    total_paid
    sale_discount
    sale_date
    user_id (FK)
    If my question needs explanation so I'm here to give more info.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,066

    Re: How to insert multiple records against single order id/ primary key

    your design is wrong.
    You should have an Order table that holds a single order record:
    Code:
    orderID - pk
    customerId - fk
    dateOfOrder
    orderTotal
    ... more fields as you need ...
    Then you should have another table that then holds the details:
    Code:
    tblOrderDetails
    id - pk
    order_id - fk
    item_id - fk
    qty
    price
    instructions
    ... more fields as needed ...
    Now when you want to save it, you insert into order table, get back the ID, then insert into order details using the order id you got in step 1.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Posts
    154

    Re: How to insert multiple records against single order id/ primary key

    @tg
    Do you mean that make 2 order tables:
    1. Order Master
    2. Order Details? Right?

    Secondly, Do you mean that Sale table will be removed and sale info will be stored in both order tables? Right?

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,066

    Re: How to insert multiple records against single order id/ primary key

    Yes... two tables... Order and OrderDetails ... The ID then comes from the Order table... and inserted into the OrderDetails when you add items to it.

    As for Sales... maybe, maybe not... if all of the info from it is moved to one of other tables, then sure, you wouldn't need it. Or, you keep it, and it too would have a FKey to the Order table. I didn't address it because it wasn't the source of the problem, your current Order table was. You had OrderID as the PKey, but then were inserting detail lines into it, giving each one a new OrderID, which isn't what you want.

    To be honest... your Sales table should be the parent record... and the Order should be the child... if you take the Order_ID off of sales, and add Sales_ID to the Order table... that would probably solve it... Now everything is keyed off the Sales_ID - in short, you have it flipped.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Posts
    154

    Re: How to insert multiple records against single order id/ primary key

    [QUOTE
    To be honest... your Sales table should be the parent record... and the Order should be the child... if you take the Order_ID off of sales, and add Sales_ID to the Order table... that would probably solve it... Now everything is keyed off the Sales_ID - in short, you have it flipped.
    -tg[/QUOTE]

    I'm not native English speaker. Can you explain it in more plain english. Pleaaassee.
    Thank you

Posting Permissions

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



Click Here to Expand Forum to Full Width