Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Unanswered: Joining 3 table with a same field...

    Hi,

    I have three tables:

    tblOrders ( orderID, customerID, employeeID, date.....)
    tlbOrderDetails ( orderID, productID, quantity, price, discount )
    tblPayments ( amtVisa, AmtMastercard,......)

    I need the third table to be able to receive more than one type of payment per order. I'm not sure how to relate all these tables properly. I want to enforce Referential Integrity, but Access wont allow it, and i can kinda understans why, but this doesnt help me!!

    For one record in the Orders table, there will only be one record in the Payments table, so i figure i need to setup a one-to-one relation here.

    For one record in the Orders table, there can be more than one in the OrderDetails table ( more than one item per order), so this must be one-to-many.

    I'm just unsure where to go next....

    thanks for the help!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Joining 3 table with a same field...

    Originally posted by MrCrud
    Hi,

    I have three tables:

    tblOrders ( orderID, customerID, employeeID, date.....)
    tlbOrderDetails ( orderID, productID, quantity, price, discount )
    tblPayments ( amtVisa, AmtMastercard,......)

    I need the third table to be able to receive more than one type of payment per order. I'm not sure how to relate all these tables properly. I want to enforce Referential Integrity, but Access wont allow it, and i can kinda understans why, but this doesnt help me!!

    For one record in the Orders table, there will only be one record in the Payments table, so i figure i need to setup a one-to-one relation here.

    For one record in the Orders table, there can be more than one in the OrderDetails table ( more than one item per order), so this must be one-to-many.

    I'm just unsure where to go next....

    thanks for the help!
    But what if someone does the installment plan??? You orders to payments table should be 1 to many. And put the OrderID in there also then you get your referential integrity ... As for the CC type make it a single column of say integer and then make a public enum with the different types of credit accepted.

  3. #3
    Join Date
    Nov 2003
    Posts
    150
    From what you explain, i would need to create a PaymentID field in the OrderPayments table as well as the OrderID and use the PaymentID as the PK?

    I've done all this, and i got RI to work.

    However, i'm not able to do the same for the OrderDetails table...

    I've included my DB if you want to take a look at it....
    Attached Files Attached Files
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by MrCrud
    From what you explain, i would need to create a PaymentID field in the OrderPayments table as well as the OrderID and use the PaymentID as the PK?

    I've done all this, and i got RI to work.

    However, i'm not able to do the same for the OrderDetails table...

    I've included my DB if you want to take a look at it....
    For the details table just add an internal index and make your PK the OrderID and Index # (this could be an autonumber ... hint! Hint!)

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    i've created an index field ( autonumber ) and made OrderID and Index the PK and it still wont work...

    i'm not sure what you mean by internal index. I'm sure by now you've noticed that i'm not too experienced!!!! ( getting there though!)

    thanks a lot!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by MrCrud
    i've created an index field ( autonumber ) and made OrderID and Index the PK and it still wont work...

    i'm not sure what you mean by internal index. I'm sure by now you've noticed that i'm not too experienced!!!! ( getting there though!)

    thanks a lot!
    You mean that it won't allow you to establish a relationship? Verify that the data will support a 1-n relationship.

  7. #7
    Join Date
    Nov 2003
    Posts
    150
    i can establish a one-to-many relation, but not RI. I think the fact there is a 3-way relation screws up RI.... not sure how i can get around this...


    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

Posting Permissions

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