Acces DB Setup Help.

[dbalilti]

Hi everyone.

So I am ready to attempt my second try on creating a DB in Access and I want to do it right this time

My Db will essentially hold the electrical and mechanical Contracts For various Jobs

Here are the fields that will be in the db. (Note each Job has max of 3 phases and SF = square footage)

1. Job Number - Key
2. Project - Unique to the Job (Wont change between Phases)


3. Developer - (can change between phases)
4. Electrical Contractor
5. Electrical Contract Date
6. Electrical Contract Amount
7. Electrical Cost Per Unit - Dependent of 6. & 15.
8. Electrical Cost Per SF - Dependent on 6. & 17.
9. Electrical Comments
10.Mechanical Contract amount
11. Mechanical Cost Per Unit - Dependent On 10. & 15.
12. Mechanical Cost Per SF - Dependent On 10. & 17
13. Mechanical Comments
14. Mechanical Contract Date

15. Number of Suites
16. Suite (SF)
17. GFA (SF)

Note that every Job Number has a Maximum of 3 Phases, each of which will have diffrent values for all the fields except for Developer & obv the Job Number.

So in other words, JobNUMBER 1111 Can have Phase 1,2 each of which can have diffrent contractors, number of units, etc.

I need this database to do a few things.

1. Sort By number of Units (SAY Show Jobs with units between 50-200 units)
2. Sort By Developers (Show ALL Jobs with a certain developer)
3. Sort By Contractors for electrical/Mechanical

I plan to break this up into the following Tables

1. Project - {With just project and Jobnumber}
2. Contracts - Phase 1 { This will have everything from 3 -17)
3. Contracts - Phase 2 {Same as above}
4. Contracts - Phase 3 {Same as above}

Obviously every Table will have the Job Number since its the Key

Does this look like a proper setup? Or am doing something wrong.

Thanks.

[June7]

Is it possible for the same business to be both mechanical and electrical contractor? Would you want to be to search on business and return all records - both mechanical and electrical?

You want a table for each phase? I would have one table with a field for the phase number.

[dbalilti]

Alright, let me explain it like this:

Lets say i have project 10000.
In reality project 10000 has 3 Condos.
The developer doesnt want to pay one giant lump sum so he breaks it into 3 Phases, where each phase takes care of one buildings ele/mech stuff.

So because of this each phase is a diffrent building and will obv have a diffrent ele/mech contract and diffrent building specs (# Of Units,SUITE SF, & GFA(SF))

To answer your question, yes every Job will have ele/mech contracts. Note sometimes it might have only one, but that is very rare.

and for your question about the phases, you can now see why i cannot treat it as a field because it is not a numerical number, it is a part of a project

I know it is a bit complicated, but thanks for baring with me, I appreciate the help.

[June7]

Nope, don't understand. If you can break the project phases into separate tables can just as easily have one table with a field for the phase designation.

I expect every job will have both elec/mech contracts. My question was: Can the same business be both mech and elec contractor on the same job/phase?

[dbalilti]

Same Business? Do you mean the Contractors?

The contractors are diffrent for the ele/mech as in one company will do the ele and one does the mech.

I Dont think there will ever be a case both contracts for mech/ele are done by the same company

The Job/Phases will have the same contractor for both ele/mech