Hi everyone, I am new to this forum and need in some advice to help me design an access database for work. I work for a maintenance company and need to design a database relating to all the properties they maintain. They want to run a report once a month to see which properties are due for maintenance, and also track maintenance history. They also have contracts with different properties and want to run another report telling them which contracts are up for renewal.

I dont have a terribly large amount of experience with access and have spent the last few days reading up and practicing to get my head around it.

So far I have the following tables in mind for the database:

Property table:
Property ID
Location
Address
Contact Details

Maintenance Completed table:
Date Maintenance Completed
Details of Maintenance
Invoice Amount
Paid

Maintenance Due table:
Date Maintenace Due
Details of Due Maintenance

Contract table:
Contract Start Date
Contract Renewal Date
Details

OK, so I know I will need to link the property table to all the others, in order to run a report detailing the maintenance history of a property, the maintenance due on a property and when a property is up for a contract renewal. Do any of the other tables need to be linked for any reason? Do they all need a primary key, or only if they are going to be linked to another table?

I will also be needing to create forms to enter details. I am wondering if it is best to just create one form with everything on it or to split it up into different sections eg. one form for entering maintenance details, another for contract details. Any advice?

Also, have I missed out on any other vital details? Sorry if I'm all over the place or if I have left anything out but I am very new to this and any advice will be much appreciated. Thanks

Here are my suggestions for the proposed tables:

Property table:
Location is part of address, thus should be included in the address. However, instead of having the address as a textfield, split it up so you have something like:

Property
PropertyID
unit
streetNumber
streetName
suburb
postCode
country (if needed)
ownerID

This is a very simple breakup of address and you might want to include street type (eg. road, street, crescent, end) and other stuff.

the ownerID would then be linked to an owner table containing all the contact information:

PropertyOwner
OwnerID
firstname
lastname
homephone
fax (idk if people still use fax...back in my day...
email

possibly the owner has a different address than the property address and you might want to add the owner's address for sending invoices and stuff.

rather than having a maintenance due and completed, just have maintenance.
Within maintenance you have a completed and nextDue date. If it is such that the next due date occurs periodically then you don't store the next due date, rather just calculate it on the fly from whenever maintenance was last carried out.

Anyway, on to your questions:

Every table requires a primary key to uniquely identify records
in my opinion it would be best to create a separate form for each type of thing you want to enter.