How to make relationships with various entities in MS Access

[Ferdinand Alosa]

Hello friends I have been looking to create a database for which I would require to know some of the facts that are required to make a relationships with various fields of the tables that I have created. Also, I would want to know whether should I keep the data type of the ID field as Number or should I keep it as text. I have developed 3 tables at a initial stage and can assure you that this is just the beginning and with many more to come. These are my table lists
1 Clients table

• ClientID, Client Name, Add1, Add2, City, Postcode

2 Jobs Table

• JobID, ClientID, Works Description, Date Received

3 Invoices Table

• InvoiceID, ClientID, JobID, Subtotal, Vat, Total

So how should I carry out the joins that are eventually needed in a relationship with all the fields of the table.

[GeforceUser]

Hey I would suggest you to keep the Id fields of the tables that you have created in Ms Access as auto number because I think that it will help you in sorting the IDs field in proper order, when ever some ID at a random number is deleted or when ever we start to add another data row after deleting some one from the middle then automatically the number will be generated so as to add the row to the end of all the data that is available on the list. So you should make sure that you have kept the data fields of the ID as Auto number so that it will make your task more simpler.

[$tatic]

Hey I think that the Invoice table that you have created has the client Id and the Job Id that has been generated from the other two tables that you have created. So I guess that these two should be the foreign key of the same field from the original Jobs as well as the Client table, You can do this by clicking on the Client ID field on the Client table and then stretching to the Invoice table that will create a line to the Invoice table client id field, in the same way you can create the relationship to the Jobs Id field , remember that we must have a one to many relationships with this fields.

[void]

If you are really looking to make use of these tables in some kind of a project where you would want to enter the data from a front end software and that has to get entered into the database table into the Access sheet, then I would suggest you to make the ID field as Text because over the front end of your project you will have a text field through which you will enter the Id and that is why it would be better if you have the Access field also as text so as to avoid confusion.