Designing a table (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Thank you !

Reply | Quote

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.

Reply | Quote