Creating database for Data Entry, Reports - how to set up the tables?

[tiffjones001]

Hi,
I'm about to create a new database to perform simple project tracking and I would like your help on the design and "normalization" (?) of setting up the tables to create and use a Form for data entry and to create Reports.

I have a main table containing most of the fields I need, but I'm not sure how to create and link the "Yes/No", Checkboxes, List Boxes, Combo Boxes, Names of people list etc...

How do I design this and ensure that all of the project information (including the check marks from check boxes) are included in the Main table when there is data entry on the Form ?

Thank you for all of your sugesstions!

B.regards,
Jones

 

[dhookom]

Provide your current table/field names and data types. You should also tell us what project information you intend to store. "Check boxes" often raise red flags. I try to avoid all use of check boxes but await your intended use.

Duane
Hook'D on Access
MS Access MVP

 

[tiffjones001]

Here are the fields that I want to use (I may trim down). There are a few that I want to pull up a calendar to input the date (right now I have the date entry format set and it works fine)
The check boxes I wanted for choices such as the "cost savings" ... whether Recurring or Non-recurring.

Project Name
Owner
Type
Start Date
Implementation Due By
Basis for Project
Requested By
Sponsor
Total Spend_Annual
Cost Savings
Recurring
One-Time
Current Status_Phase
Initial Review_Analysis - Est Completion Date
Initial Review_Analysis -Actual Completion Date
Initial Review_Analysis -Comments
RFI Publish Date
RFI Est Completion Date
RFI Actual Completion Date
RFI Comments
Kickoff Meeting - Est Complettion Date
Kickoff Meeting - Actual Completion Date
Kickoff Meeting - Comments
Cust Require_Scope_Objectives - Est Completion Date
Cust Require_Scope_Objectives - Actual Completion Date
Cust Require_Scope_Objectives - Comments
Tollgate I Review - Est Completion Date
Tollgate I Review - Actual Completion Date
Tollgate I Review - Comments
KPIs - Est Completion Date
KPIs - Actual Completion Date
KPIs - Comments
Tollgate Approval Date
Tollgate Approved By
RFI - Publish Date
RFI - Est Completion Date
RFI - Actual Completion Date
RFI - Comments
RFP - Publish Date
RFP - Est Completion Date
RFP - Actual Completion Date
RFP - Comments
Bid Recommendation - Est Completion Date
Bid Recommendation - Actual Completion Date
Bid Recommendation - Comments
Bid Recommendation Approved By
Bid Award - Est Completion Date
Bid Award - Actual Completion Date
Bid Award - Supplier(s) Awarded
Bid Award - Comments
Contract Negotiation - Est Completion Date
Contract Negotiation - Actual Completion Date
Contract Negotiation - Comments
Contract Execution - Est Completion Date
Contract Execution - Actual Completion Date
Contract Execution - Comments
Implementation - Est Completion Date
Implementation - Actual Completion Date
Implementation - Comments
Tollgate 4
Finalize Score Card
Contract Adjustments


Sorry to overwhelm you with all of these fields! Thanks for all of your advice.

T

 

[dhookom]

A good table structure should never be overwhelming. Your structure is highly un-normalized. You have hard-coded lots of project "mile-stones" into field names. Do you even have a primary key field? I would first add an autonumber field [ProjID] and make it the primary key

I would get rid of almost every date field in the table and create tables like:
[tt]
tblMileStones
===================
MSID Autonumber primary key
MSTitle values like "Internal review", "RFI Publish",...
MSOrder numeric field that identifies the default order these milestones occur
MSActive Is this milestone active
MSOwner The default owner/employee of this milestone
[/tt]

[tt]
tblProjectMileStones
======================
ProjMSID Autonumber primary key
ProjID link to tblProjects.ProjID
MSID link to tblMileStones.MSID
ExpDate
ActDate
PMSResponsible person responsible
PMSComments
[/tt]

IMO, I would not go any further with your current structure. The structure I suggested allows you to add or remove milestones without change table structures, forms, reports,...



Duane
Hook'D on Access
MS Access MVP