Schedule Conflict

[Jim P]

Hi, I'm new to Access and this forum, so forgive me if I am a bit ignorant on both fronts.

I'm trying to create a data-base to help schedule people to jobs at different job sites.

I want to do it by Job type, Location (job site), Date, Crew & Trade (note that individuals (Trade) often are on two different crews. this is where the conflict comes in) Each Tradesman also has a preference rating of 1,2,3 or 4.

I have over a 1,000 different Job types (I assume Job Types will be a primary key) that need to be performed at 7 different locations. Normally 1 job per location per day but some times as many as 3.

If any of this makes sense and anyone can offer any advice, I would greatly appreciate the help.

Note that I only made it as far as importing each Job type with the Crew and Trades.

Again, I would appreciate any help

Jim P

 

[mission2java_78]

Definately looking at a main / sub form.

Id store your most relevant data that is global across all (sub) jobs in your main form, and the actual jobs in your sub form. Your header info may have crew names, info, etc...and your subform can have the particular jobs (many side). Your ID is not necessarily your job type. Job type should be captured in its own table and referenced in your main / sub table.


Your structure would look like this

+ Header : Info
-Job 1 (points back to its parent header 1)
-Job 2
-Job 3

+ Header 2 : Info
-Job 1 (Points back to its parent header 2)
....

+ Header N: Info
-Job 1 (Points back to parent N)
-Job 2

Hope this makes some sense...

BTW You may not need a database for all of this. Have you ever used MS Project? This is a good tool used to schedule tasks / activities on projects. It comes complete with durations, names, responsibilities, parent-child relationships, flowcharts, grids, etc.
The problem only is time, resources, and maybe reporting.

 

[rockman]

If you are looking for database structure suggestions, this is how I would set it up:

tblJobs
fldJobID (autonumber)
fldDate
fldLocationID

tblLocations
fldLocationID (autonumber)
fldDescription

tblWorkers
fldWorkerID (autonumber)
fldWorkerName
fldTradeID
fldPreference

tblCrews
fldCrewID (autonumber)
fldCrewName

tblTrades
fldTradeID (autonumber)
fldTradeType

and now for the "joining" tables

tblJobCrew
fldDummyID (autonumber)
fldJobID
fldCrewID

tblWorkerTrade
fldDummyID (autonumber)
fldWorkerID
fldTradeID

tblCrewWorker
fldDummyID (autonumber)
fldCrewID
fldWorkerID

Then you have to create locations, workers, crews, and trades.
Assign trades to workers, workers to crews, and ultimately crews and locations to jobs...

Then again maybe MSProject is the program you are looking for?

Hope this helps,
Jeff