Access Help...again! (Having trouble)

Styles · 23 Feb 2006 at 14:41

MS Access Help...again! (Having trouble with database)

Hi,
I've been reading through a few online tutorials about database design but I can't seem to find anything about relashionships within the database.

Basically I'm trying to make a database for my mum's workplace. They want to make a database with the ability to track and analyse jobs and stock issues.

I've been given a list of staff members and the departments they work in.

I've also been given a list of charges for copying and printing papers and photocopying etc, and a list of charges for stock (A4 paper, A3 paper etc).

So from this i've worked out so far that i need tables for the following:

Staff - Primary Key = Staff ID - Foreign Key = Department ID
Department - Primary Key = Department ID
Transaction - Primary Key = Transaction ID - Foreign Key = Staff ID
Stock
Paper (Charges)

I think what i've got above is correct? Now in the tutorials it says I have to link the tables to create a 'relashional' database. How would I do this? I'm really stuck and have no clue where to go from here? I hope i've explained it enough, it's quite difficult putting it down in words.

Any help would be excellent

Thanks

lordedmond · 23 Feb 2006 at 15:25

just click on the relationship tab on the top menu bar and select the tables, then drag the fields to form the relationships

its the one with the three tables on with a line between

but you will need a common field in each table to link with ie record number

btw do not call any fiield with the name "name" causes no end of trouble

also do not use spaces the table or form names

they should be Tblxxxxx and Frmxxxxx or Qryxxxx

that makes maint a lot easier spaces cause problems with the VB and sutch like

the dummies guide is a good book on the subject

Styles · 23 Feb 2006 at 16:14

Thanks for the reply. I know how to create the relashionships in access itself, I just don't know what relashionships I need to make?

I know I need to make a one to many relashionship else it won't work but I don't know how to link my tables?

How would the staff table link to the department table to the transaction table? This is where i'm confused? Any help would be excellent. Thanks

lordedmond · 23 Feb 2006 at 17:07

each linked table has to have a common field ie empolyname or EmpRefNo in the staff table has to be linked to the employname or EmpRefNo in the job table

marc_howarth · 23 Feb 2006 at 19:39

S7yl3s said:
Thanks for the reply. I know how to create the relashionships in access itself, I just don't know what relashionships I need to make?

I know I need to make a one to many relashionship else it won't work but I don't know how to link my tables?

How would the staff table link to the department table to the transaction table? This is where i'm confused? Any help would be excellent. Thanks

This is sounding very much like the IT coursework that i had to do!! :rolleyes:

Anyway, to create your relationship you must open the relationship window, as stated above. The relationship should take place with the Staff, Department and Transaction table. Arrange the tables in the relationship window so that the transaction table is in the middle. Click and drag from the side tables inwards with the matching feild names (can't remember wether it's inwards to outwards or vice versa, the "1" should be on the Transactions table). A box will appear and make sure that you tick the "Enforce Referential Integrity".

The relationships you should make are, Department ID from the department table --> Department ID from the Transaction table and Staff ID from the Staff table --> Staff ID from the Transaction table.

Now that the relationship is set up a query that links all the table together. When creating this theory firstly start inserting fields into the query from the transaction table. However if the feild from the transaction table is related to a feild on another table then you must insert the feild e.g. Department ID from the department table. Once you have started inserting fields from another table you must insert all the other feilds from the table before moving onto the next field from the Transaction table.

After completing all the fields from the transaction table you can then insert any fields you may want from another table such as your Stock and Charges table.

Hope this helps (again)