Hi everyone, I am new to this forum and need in some advice to help me design an access database for my new job. I have started working for a maintenance company and need to design a database relating to all the properties they maintain. What they want is to be able to run a report lets say once a month, and be able 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 every so often 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. Will I be able to achieve what I want out of the ideas I have detailed. 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 a few thoughts...
- I think Invoice should have it's own table. It's a completely distinct type of thing.
- How does a Contract object differ from a Maintenance object? Could there be multiple maintenance items in a single contract?
- I think Due and Complete are attributes of an item, so you would never put due and complete items in different tables. Rather, you might give a Maintenance record a DateDue field and a DateCompleted field. But all same-typed things should be in a single table, and if they differ by some single dimension you add a field to that table.
- And your Contract object, once it is started and then renewed, what happens? If it's re-renewed do you change the StartDate? Maybe a Contract just has a StartDate and a Duration. What it gets close to ending maybe you negotiate a new Contract with a new StartDate and Duration?
HTH