Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Designing a table (Access 2000)

    I have the task to build a separate table for the payment of the orders containing only the data whether the payment is done or not.Therefore i want to build a table that contains the Yes/No field for the payment of the orders.I have built this table and named it TblPaid.How can i relate it with the table orders so that i could enter the data "paid" in a query containg the orderid from the table orders and the yes/no payment from the table tblPaid?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Designing a table (Access 2000)

    In general, it is not a good idea to design a table that will have a one-to-one relationship with another table. It's much better to add the Paid field to the Orders table.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Designing a table (Access 2000)

    it is true,i know that.My case is rather special since the table orders is processed from different customers while a separate table will be kept with only one instance.I just want to take your advice and then to proceed.So my question is, can i build a table with another iautonumber d number, for example paidid, the second field to be orders and the third field to be the Yes/No field.In that case i might have a one to many relationsip and it remains to tie up the order id with the relevant Paid field form the table TblPaid.Of course if you tell me that it is impossible i will give up the idea but i strongly hope you might find some way.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Designing a table (Access 2000)

    You can create a table tblPaid with two fields:

    OrderID (number, long integer)
    Paid (Yes/No)

    OrderID must be the primary key of the table, and its Default Value property must be empty (blank, null) instead of 0.
    This table does not need an AutoNumber field.

    Before adding any records to this table, select Tools | Relationships...
    Add the Orders table and tblPaid to the window.
    Draw a line from OrderID in the Orders table to OrderID in tblPaid to create a relationship.
    Tick the first check box in the dialog that appears, to enforce referential integrity. This ensures that you cannot enter a record in tblPaid for an OrderID that does not exist in Orders.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Designing a table (Access 2000)

    Dear Hans

    Thank you for your reply.I wonder why i cannot build the relationship one to many.Access shows me only one to one relationsip. The orderid is with primary key as you have told me
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Designing a table (Access 2000)

    Delete the 0 record that is in the tblPaid table.

    As Hans noted you must set the default value to null instead of zero for the orderid in the tblPaid table.

    You can then ensure referential integrity.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Designing a table (Access 2000)

    Pat already pointed out what you should do. You're going to end up with a one-to-one relationship - there should only be one record in tblPaid for each OrderID.

  8. #8
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Designing a table (Access 2000)

    Thank for your reply. I have created the one to one relationship. But how can i call the order in the query,i want to have a query that relates the tables orders and tblPaid and write Yes or No in the field paid of the tblPaid
    Attached Files Attached Files

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Designing a table (Access 2000)

    I don't understand what you are trying to do, would you please be more explicit.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Designing a table (Access 2000)

    You'll have to add some more fields to the Orders table - a table with only an AutoNumber field is useless.
    In the query, double click the line joining the tables.
    Select the option to return *all* records from Orders.
    You will now see all orders, even if there is no corresponding record in tblPaid. As soon as you click the check box, a record will be created in tblPaid.

  11. #11
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Designing a table (Access 2000)

    Yes,thank you. Now i see all the records from the orders and the query is OK

    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
  •