Setting Up a New Database for Projects & Contracts

[Weekleyba]

I've been challenged to set up a database for another department at work.
I don't consider myself an expert but I've been working with Access on and off for about 5 years now.
I'm looking for some guidance on the initial set up.
The database will involve at it's core, Service Units, Projects, and Contracts.
Every project and contract will belong to a Service Unit.
There will be multiple items to track under each project and contract.
The problem that is stumping me is, Projects can have multiple Contracts and Contracts can have multiple Projects.
I'm thinking this would be a good place for a many to many relationship but, after fooling around with some ideas, I'm stumped.
I envision a main form for the Service Units and then subforms for Projects and Contracts.
Can any one give me some guidance here?
Thanks in advance.

 

[Lightwave]

Yes you are looking at a many to many relationship

All databases implement this in the same way as two one to many relationships with something called a junction table in between.

Carefully study this video should be better at describing what you need to do than anything I can write. Once you have it you will quickly admire the elegance.

https://support.office.com/en-us/article/Video-Create-many-to-many-relationships-e65bcc53-8e1c-444a-b4fb-1c0b8c1f5653

 

[jleach]

Tends to look like this:

Contracts
..ID (PK)
..ContractDate
..etc

Projects
..ID
..ProjectCode
..etc

ProjectContracts
..ID (PK)
..ContractID (FK)
..ProjectID (FK)
..other details

Typically there's a unique index put on the two FK fields of the junction table (or, for the natural/composite key camp: get rid of the ID autonumber PK and make the two FKs the PK). All the details related to the project/contract pair go in the ProjectContracts table (or other related tables further down the line as required).

 

[plog]

Can you better communicate what your organization does? Provide 2 paragraphs:

Paragraph #1-- Just tell us in big picture terms what it is you guys do. Use absolutely no database jargon. Pretend its career day at an elementary school and you are explaining to the kids what it is you exactly do. Be sure to include what Service Units, Projects and Contracts are. Again, no database jargon.

Paragraph #2--Tell us what kind of data you guys need to track and what problems you are having with any current system. You are allowed to use a little database jargon, but don't explain your thoughts on how this is to work or go into detail about anything specific.

 

[Pat Hartman]

Continuing plog's thought

Try to focus on the problem you have to solve rather than what you think the solution should be.

Inputs and outputs are important because you will have to get data from somewhere and present it in some coherent fashion to the users but do not let input/output constrain you with spreadsheet logic because relational database schemas do not look anything like spreadsheets.

 

[Weekleyba]

Thanks all for your responses.
Here's the skinny on what this department does.
They provide engineered solutions for different Indian reservations.
Projects range from new water treatment facilities to septic systems for homes.
We help each reservation (or service unit) contract out the construction once they complete the design.
A project could have multiple phases (e.g. a 10 mile water line, split up in two 5 mile increments over two years.) and thus have two contracts bid out for the two phases.
There are multiple documents that need to be completed during the design phase and multiple docs that need to be completed during the contract phase.
Also, one contract may be bid out for multiple projects.
The art of setting this one up is baffling me.
I understand the many-to-many query with the junction table but, utilizing it where, I do not know.
My thought was to have a main form, with a combo for the reservations and use that to search for projects & contracts for each reservation.
I was playing around with a subform that had the many-to-many query and inputting a project and contract (when awarded) in a datasheet view.
I was hoping to somehow select a record from the datasheet, that is sorted by reservation, and for both a project subform and a contract subform to either popup or be populated below the datasheet subform.
The subforms for project & contract would be in single form view.
That's my thought but I'm not able to figure out a way to make it work...yet.
Does this help with what my intent is?
Thoughts?
Thanks again all!

 

[Mark_]

Suggestion, main form holds site (reservation) and it has a tab control on it.

One tab runs off of contracts. Click on a contract to bring up the contract details with a subform on projects.

One tab runs off of projects. Click on a project to bring up the project details with a subform on contracts.

This would allow you to enter either a contract or a project on its own subform, then establish links as needed.

 

[Uncle Gizmo]

"Many to Many" is a good description of the actual table that stores the information. A basic many-to-many table would consist of two fields, a left field and right field. You could describe this table as many items on the left linked directly to many items on the right. However although that is a correct description, it's also a misleading description, as it focuses attention on the actual many to many table. Whereas when you extract information from the many-to-many table all you ever do is extract "One to Many!" in my blog here:-


Many to Many Relationship

I show examples of both sides of the "Many to Many" "One to Many" extraction. There's also a video that shows you what could well be wrong if the synchronisation between the main form of the subform is not working properly.

 

[Weekleyba]

I'm still at a lose on the set up.
Can anyone else help?

Their current system for tracking the progress of projects and contracts involves spreadsheets. The way I see it, is that it's begging for a database.

I've been missing around for a couple days now I can't figure it out.

I like the idea of a main form for the Reservations, then a Tab control with Project and Contract as subforms on each tab.

I would like to select a Reservation and all Projects and Contracts to be filtered on the selection. (I can make that happen)
Then in the Project tab, I like to see the Project (and any info I would later include) and all the associated Contracts. I would then like to select any one of those Contracts and for it to take me either to the other tab for Contracts or to popup form with the Contract info.
It also needs to be editable, so as a Project is bid out and awarded, the Contract Number can be inputted. (maybe multiple contract numbers)

Any samples, or help would be appreciated.

 

[jdraw]

You seem to be dealing with the following "entities" (things).

Project
Phase
Contract
Documents
Progress/Status
(Customer/Reservation)

As Pat said, try to focus on the problem --that is, what exactly are you being asked to do?
How do these things fit together in the business? It might be easier if you could describe a typical project/contract in simple terms (5-6 lines). The business rules/facts are key to getting your tables and relationships designed.

Also, as has been suggested, looking at/reviewing some existing inputs and outputs will clarify the data required and will help in confirming you have addressed those data in your evolving design.

Progress ( to me) tends to suggest a plan with estimate and actual values --could be a 20 day project and you're at day 18; or some related expense numbers. But a sample of progress would be helpful.

I would not be considering forms and tabs etc until I had a draft data model (pencil and paper) that I could test with some test data.

However, you know your situation better than we do. You did ask for any help.

Here's a link I found via google that may help put some concepts together.
Good luck.

 

[Weekleyba]

Thanks Jdraw.
The department would like to have a database that multiple employees can access in order to track and input new projects and contacts for the reservations they serve.
Here are some of the entities they want to track for:
Projects
1. Project Request - this is a document that is drafted, then approved.
2. Project Summary - another document drafted and eventually approved and can have amendments.
3. Environmental Doc - This can involve many docs all of which would go through the draft and approval process.
4. Memo of Understanding - Between Tribal gov't and the dept.
5. Then the Design phases all the way to 100%.
Contracts
1. Advertising
2. Bid Date
3. Pre-construction conf
4. Submittals
5. Contract Completion
6. Pay Requests
7. Modifications
8. Final Inspection

Again a Project may have more than one Contract and vice versa.

For the documents involved above - they currently have employees draft them in Word and they either email it to their supervisor or place in on the server in the project folder and the supervisor has to go to it and review it or approves.

The same holds true for their construction designs. Employees draft them and the supervisor reviews or approves it. Phases: 10%, 35%, 65%, 95% and 100% as well as written updates on the progress of the design.

Is that the kind of information you are asking for?

Thanks for the link to the PM Model. I'll need to chew on that for awhile.

 

[Mark_]

Just as a warning... you will not want to store documents inside your database. Leave them as files on the server. You may want a child file that identifies each attachment, has some information about it (or how it relates to a program) and the full path to the file, but you won't want the file itself.

You will also want to check with the department to find out what reports and charts they use. That will help tell you other pieces of information you will need.

 

[Weekleyba]

Understood, Mark. My thought there was to set up a form to generate a report. Once supervisors have completed there edits on the form, they could print the pdf and store in the appropriate folder, outside the database.

 

[jdraw]

You know more about the requirement than you first posted. Mark's points are right on and are points to incorporate in your design. Your contract outline reminds me of series of stages. A project starts at stage 1, through stage2 ....stage x. There are some conditions that must be met to "complete stage1 and start stage2 etc.", at any stage if conditions are not met, there will be some alternate flow(penalty) in order to meet the stage end condition.

Here is a link to a lot of info. You might want to watch a few of the videos to get a refresher on concepts, but I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary. You'll learn a process that you can then use with your data/description --flesh it out as needed. Get it working on paper with test data and scenarios before getting too deep into Access.

Good luck with your project.

 

[Mark_]

Odd question that may be very relevant as you are talking about this being for different tribes (and as such you may step into a steaming pile of appropriations), will you need to track funding? This gets messy if one contract steps into more than one project as you would then need to determine how much of the cost is coming from each project / for each milestone. If you do need to deal with appropriations you'll want to be very careful on tracking what is coming from which appropriation, when, and what the purpose / type along with the dates/durations for the appropriations.

NOTE: You may have to dig a little, but see if ANY of the money is coming from the Federal government. If yes, you are dealing with appropriated money.

 

[Weekleyba]

Ok, I've done some homework and made some models on paper.
I've started with the what I consider the main items of the database, and that being Reservations, Projects, & Contracts.
Everything else that they want to track will fall under Projects and Contracts.
So, this leads me to the problem I thought I would run into, that is, how I set up the database knowing that you can have multiple Projects per Contract and vise versa.
Attached is my start but it's not good.
It appears to work ok, but additional records are generated in the ProjectT when I enter a contract in the subform under projects.
I still need to create a Contract form and show the projects that belong to it, similar to what I have for the Project form.
Can you give me some help on this?
Thanks in advance.

 

[plog]

That is incorrect. First, I am only lookin at your Relationship Tool at this time. There's no need to worry about forms until you have your tables (and then your reports) set up properly. So, let's only focus on your tables.

In your Relationship Tool you have essentially created a spider web relationship by including ReservationID in each of the other 3 tables. There should only be one way to trace a path between tables, not multiple ways (add ReservationT to the tool and you'll see what I mean). You need to figure out how a reservation fits into this and only keep ReservationID in the appropriate one table. If a Reservation is attached to both a Contract and a Project, then that means it belongs in JUNCT_ProjectContractT and not each of the other individual tables.

 

[Weekleyba]

Plog,
Thanks for your help on this.
I'm a self taught Access user so it doesn't surprise me that I'm heading in the wrong direction.
For me, the only way I can really tell if the relationships are set up correctly, is when I set up the forms and see how it works. I find myself doing a lot of trial and error however. I still have difficulty understanding complicated relationships.
Anyway, so I deleted my couple of forms and I'm looking at the tables only.
I have a lot of tables to enter, but they will be related to either the ProjectT or the ContractT with FKs, so I didn't feel I needed to add them until I solve this first problem of setting up Reservation, Project and Contract relationship. Should add all the tables first?
Yes, the Reservation will be attached to both a Project and/or Contract.
A Contract will never be created without a Project being created first. A Project can have multiple Contracts and a Contract can have multiple Projects. That is why I used a junction table. Also, a Project and/or a Contract will only have one Reservation.
I want to see this thing through but, as you can tell, I'm going to need some assistance.
Send me some more advise, I really do appreciated it.
Thanks!

 

[plog]

No, you should add your tables 1 by one, making sure each one fits properly. The 3 tables you had in there looked fine just by themselves. Its just that they each had a ReservationID, so it was essentially in there, just not technically.

So, it sounds like a Reservation is attached to a Contract/Project. That means the ReservationID should only be in the junction table. Of course, that is only if the Reservation is attached to just one Contract/Project. Can a reservation be for multiple Contract/Projects?

 

[Weekleyba]

Let me clarify, a Reservation is an Indian Reservation, so each Reservation can have multiple Contracts and/or Projects.