Project Tracker and Prioritization Tool, Table Design and Relationships

[yddeyma]

I'm trying to build a project tracker database for work. Every project we do follows the same basic process with distinct phases: Identify Issue, Research, Design, Build, Test, Field.



We always have more work than we can feasibly accomplish with time/budget. So our biggest problem tended to be consistently prioritizing. So I made a little scoring rubric in Excel that asked some questions (size, cost, ROI, etc.) and made a standard score based on the answers and spit out the score and title of the project to a table so you could sort by highest priority.

It worked great, but the boss wants it connected to all the project details which are kept separately. Up to this point, the details have been kept haphazardly in one giant excel spreadsheet with a bazillion columns. The columns are currently a mashup of project details (title, POC, cost, etc.), milestones/status (received funding, awaiting contractor, awaiting test), and "to-do" list items (request funding, create drawings, write reports, build prototype).

I am having trouble figuring out how to setup the database. My first instinct was to setup a table for each phase. But if I do it that way, all the relationships would be one-to-one.....So wouldn't it be better to just make everything in one giant project table like we already have? If not, I'm not sure how to approach. I could have separate table for project details, milestones or "to-do" items, but not sure how I would show which phase each project is in. Phase is a big deal at work, because different folks are in charge at different phases and people are always pointing fingers about tasks not being complete prior to moving to the next phase.

I created a bare bones ProjectTable with primary key ProjectID. Then, I created the rubric scoring criteria using this tutorial: https://www.youtube.com/watch?v=cDYAi3SOQ4E It worked great, but now I can't figure out how to tie the score back to the project table (in the tutorial, he did it all in one table), or at least tie each ProjectID to a single Score. I tried to drag/drop the ProjectID from the rubric table back to the project table but it keeps giving me a one-to-many connection, and in this case each project has exactly one score, and each score is tied to exactly one project. I am so new at this I am not sure how the relationship building is supposed to work.


Thoughts? I think i got ahead of myself by starting on the rubric first without thinking how all the information I need should be organized. Plus I desperately need some relationship/table building tutorials.

[Micron]

You have already discovered that design will be key, and if done poorly, will have you constantly running up against barriers. What you want to do first is understand normalization. Then try your hand at it with pencil and paper (I use very large sheets, penciling in table and field names, deciding on field properties, drawing lines to reflect relationships, etc). Pencil is easily erased! IMHO, queries come before forms - if it needs to be editable and is not, a form based on such a query is useless. So first things first.

Back to normalization: best thing you can do is NOT organize as you would a spreadsheet (wide) because tables are row based (tall). When you think you have something penciled out you can run it by this forum if desired. Not sure how much you need but here's a whack of links to get you started...

Normalization is paramount.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

[Mickjav]

Just of the top of my head a few tables come to mind
Clients
Projects
Phases
Tasks
Invoice
Invoice Details

They are the very basic tables I have a project system I designed for myself and those are the very basic tables I think mine now has over 100 tables and has worked for more than 12 years without any problems.

mick

[yddeyma]

We only have one client, and each project will have just one of each phase or task.

What are your various tables that you are at 100? I read somewhere that if everything is a one-to-one relationship it should all be in one table.....

[Mickjav]

No Never use one table thats what we used to call flat files

Every database will have the number of tables it needs to do the job

Have a look at some of these models

https://www.access-diva.com/dm13.html

[yddeyma]

try your hand at it with pencil and paper

So I've tried that a couple of times, and keep coming back to the same mental roadblock: I am not trying to track lot of information about lots of types of things, I am just trying to track information about one specific thing (projects). So everytime I try to group information by type, I only get one-to-one relationships. And I read somewhere that if you have a one-to-one relationship it should really be in the same table.

I have a couple of different sheet with my chicken scratch on it, but all I seem to be doing is figuring out ways to slice the info about the projects. I don't see distinct separate types of data as with all the examples I've found (which are usually given as customers, products, orders, etc.). The way I see it, I always have the same customer, with the same product, with the same order.

[Mickjav]

You dont need to worry about clients

So are the projects large enough to need to be split down into phases??

mick

[Mickjav]

I just re-read the first post, Is this system for a building/contruction trade??

Can you post a copy of the excel file with most ofthe data removed that way we will be able to get a better grasph on it.

mick

[Micron]

I am just trying to track information about one specific thing (projects)

So every project can only have one Issue, one Research (activity or whatever that means), one Design (activity), one Build, one Test, and one Field (activity?). Then it may be a one table thing, unless you incorporate lookup tables as well. Those are often best left out of the relationships schema.

If not, then I/we are not understanding the problem. You say there are a brazillion spreadsheet columns in the source data, indicating a problem for good db design, plus you have a scoring matrix, which is probably a table right there. If you studied normalization and understand the basics, where does that leave you for this issue? A sample of your spreadsheet with pseudo data might be helpful.

[yddeyma]

I just re-read the first post, Is this system for a building/contruction trade??

mick

I don't want to get too into detail since its work related, but its for engineering projects. So I would say probably very similar in scope and dollar value and requirements to construction. Just a lot of moving pieces to getting one from start to finish, except that instead of having a client, we just do work for our company....so while we have to budget and stuff, we don't actually get paid or invoice.

I attached the current template. But like I said previously, it is a mashup of details about the projects, POCs, milestones, status, a few "to-do" items, deliverable requirements, etc. Lots of stuff missing or incomplete, and like types of info is not currently grouped together. And while there are lots of different types of data, they all only correlate to one project record. Each project can take years to complete, so the phases in terms of "to-do's" can get quite large, but I don't think we are going to track down to the nitty gritty level. Using your construction example, we would put something like "order materials" on a to-do list, and not "order grout, order tile, order 2x4s", etc.

I did go ahead and make the rubric/scoring template a separate table in the access database.

But I guess in my mind I was thinking everything else could be in one table and then I just make an entry form per phase for various fields, and display status and milestones with reports.

[yddeyma]

Have a look at some of these models

https://www.access-diva.com/dm13.html

Awesome, that link was helpful. It is kinda like the Tasks database, except no clients and tasks don't repeat. Assuming "tasks" are what I call projects. Maybe what I call score is their priority. So I do have a many-to-one relationship there. There are many scores, but only one per project.

[Mickjav]

I'll download the file but from what you said in post 10 then A relational model is what you will need, I'll help best I can with the time I have but you have a very very long road and a very high hill to climb.

But thats what makes life interesting

mick