Relationships

[hesketh1984]

Help Me Please

Hello There.

I'm having to make an access database for an estate agents at University. I'm struggling with the relationships. I've no idea what I'm doing, and its got to be done for this afternoon. I've got 10 tables- and dont understand what should be linking to what. Any help would be much appreciated.

Here are the Tables:

BRANCH
Branch Number
Street
Area
City
Postcode
Telephone Number
Fax Number

STAFF
Staff Number
First Name
Last Name
Address 1
Address 2
Address 3
Telephone Number
Sex
Date of Birth
Position
Salary
Date Joined
National Insurance Number

PROPERTY FOR RENT
Property Number
Street
Area
City
Postcode
Type
Number of Rooms
Rent
Owner Number
Managed by Staff Number
Branch Number

LEASE AGREEMENT
Lease Number
Property Number
Renter Number
Rent
Payment Method
Deposit Amount
Deposit Paid
Rent Start Date
Rent Finish Date

PRIVATE OWNER
Owner Number
First Name
Last Name
Address 1
Address 2
Address 3
Telephone Number

RENTER
Renter Number
First Name
Last Name
Address 1
Address 2
Address 3
Telephone Number
Preferred Type
Maximum Rent
Branch Number

ADVERT
Advert Number
Property Number
Cost
Date Advertised
Newspaper Name

NEWSPAPER
Newspaper Name
Address 1
Address 2
Address 3
Contact Name
Telephone Number
Fax Number

VIEWING
Property Number
Renter Number
Date Viewed
Comments

INSPECTION
Property Number
Staff Number
Date Inspected
Comments


So what should link to what? Cheers, Rick

 

[robthedog]

I think you may be struggling to get this project done and dusted for this afternoon!! However; what I suggest you do, is to look at your tables, and determine which field is the primary key in each table – if you look at the table in design view, the primary key will have a small ‘key’ icon to the left of the field name. The primary keys will probably be ‘Branch Number’ in the ‘Branch’ table, ‘Staff Number’ in the ‘Staff’ table etc. If the tables don’t have primary keys you may need to add them.

You then need to look for these fields as foreign keys in the other tables: for example the ‘Advert’ table has a field named ‘Property Number’, which is the primary key in the ‘Property for Rent’ table, thus the ‘Property Number’ is the foreign key in the ‘Advert’ table.

If you open the relationships window, you should then be able to add the tables which you want to create relationships with, and drag the primary key fields from your tables to the respective foreign keys to create the join.

Good luck

Rob

Hello There.

I'm having to make an access database for an estate agents at University. I'm struggling with the relationships. I've no idea what I'm doing, and its got to be done for this afternoon. I've got 10 tables- and dont understand what should be linking to what. Any help would be much appreciated.

Here are the Tables:

BRANCH
Branch Number
Street
Area
City
Postcode
Telephone Number
Fax Number

STAFF
Staff Number
First Name
Last Name
Address 1
Address 2
Address 3
Telephone Number
Sex
Date of Birth
Position
Salary
Date Joined
National Insurance Number

PROPERTY FOR RENT
Property Number
Street
Area
City
Postcode
Type
Number of Rooms
Rent
Owner Number
Managed by Staff Number
Branch Number

LEASE AGREEMENT
Lease Number
Property Number
Renter Number
Rent
Payment Method
Deposit Amount
Deposit Paid
Rent Start Date
Rent Finish Date

PRIVATE OWNER
Owner Number
First Name
Last Name
Address 1
Address 2
Address 3
Telephone Number

RENTER
Renter Number
First Name
Last Name
Address 1
Address 2
Address 3
Telephone Number
Preferred Type
Maximum Rent
Branch Number

ADVERT
Advert Number
Property Number
Cost
Date Advertised
Newspaper Name

NEWSPAPER
Newspaper Name
Address 1
Address 2
Address 3
Contact Name
Telephone Number
Fax Number

VIEWING
Property Number
Renter Number
Date Viewed
Comments

INSPECTION
Property Number
Staff Number
Date Inspected
Comments


So what should link to what? Cheers, Rick

 

[hesketh1984]

Cheers, I'll have a go.

 

[ColinEssex]

You'll need branch number in the staff table as well. Then you can link people to branches.

Can I say I think its unreasonable of your bosses to expect this to be done by this afternoon I would suggest you ask for a time extension on this.

Col

 

[Mile-O]

Branch Number -> Branch Number
Property Number -> Property Number
Renter Number -> Renter Number

etc. etc.

So, why'd you leave it to the last day?

 

[Mile-O]

Can I say I think its unreasonable of your bosses to expect this to be done by this afternoon I would suggest you ask for a time extension on this.

Replace boss with lecturer.

 

[ColinEssex]

Replace boss with lecturer.

yes - my mistake, I misread it

so has this project been waiting in the wings until the last minute then?

Col

 

[hesketh1984]

I leave everything to the last minute. Thanks for the help anyway- its making a little more sense now

 

[Rich]

You've no table for rent collections

 

[Mile-O]

I take it that's it's just the tables and relationships that you have to get correct and not a full database application with queries, forms, and reports?

 

[hesketh1984]

Ive got to do the forms aswell- but ive managed those. Ive got till 2pm. Loads of time. .

 

[ColinEssex]

won't the "renter" table need a property number??

 

[hesketh1984]

I'll put one in, thanks colin

 

[robthedog]

Please do let us know what grade you get for this monster application!!

 

[hesketh1984]

I will let you know. When im linking branch number to branch number- is it one-to-many, one-to-one, many-to-many?

 

[Mile-O]

Looks like a one to many as one branch can have many people renting from it.

Shuldn't there be a Branch Number in the Staff table so that you know in which branch people work?