Access Table Design

[jshjnh]

Good evening everyone. I am tackling a database and while I have a little experience working with access 2003 I am at a novice level. I have been tasked to create a database to keep track of all aspects of construction projects for reporting. I have been reading for some time, books, posts, and articles. I find it hard to get away from the spreadsheet frame of mind. Below are the tables I have created thus far with the field type beside and even some keys ( though they might not be correct) I have been reading about normalization and attempting to break my tables in that manner.

I am stumped. It seems like I should be able to break out more, I am not sure and should I be going that far? All this data will have to be input at certain times with no certain time interval. Meaning I will enter some info at one point and other info at points when I receive the documents/dates and so on.

Am I being to complex?
Will this even work with a form?
Am I on the right track?


Some info: The project name will only be used once, all other data has the potential to be possibly duplicated. Though 99% of the time there is only one work order per project.

Thanks for any help in advance!

Tblcontract
WorkOrder (PK) (text)
FundingType (text)
ResponsibleOffice (text)
PRCNumber (text)
ProjectAwarded (yes/no)
ProjectAwardAmount (currency)
AnticipatedAwardDate (date/time)
ContractAwardDate (date/time)
ContractNumber (text)
ContractType (text)
TaskOrderNumber (text)
LinkToContract (hyperlink)


Tblnegotiate
NegoID (PK) (autonum)
WorkOrder (FK) (text)
PriceObjMemoDate (date/time)
PriceObjMemoAmt (currency)
PriceNegoMemoDate (date/time)
PriceNegoMemoAmt (currency)
PnmLink (hyperlink)
PomLink (hyperlink)
ProjLeadNegotiator (text)
PrejNegotiated (yes/no)
ProjNegDuration (text)
ProjNegCost (currency)
ProjNegDate (date/time)
TechicalAnalysis (hyperlink)
TADate (date/time)



Tblfinancesktr - (KTR = Contractor)
KtrID (PK) (text)
ProjectName (FK)
KtrProposalAmt (currency)
KtrProposalDate (date/time)
KtrRevisedProposalAmt (currency)
KtrRevProposalDate (date/time)
KtrBaseBidAmt (currency)
KtrOptionBidAmt (currency)
KtrrevisedBaseBAmt (currency)
KtrRevOptBidAmt (currency)
KtrproposalLink (hyperlink)
KtrRevProposalLnk (hyperlink)
KtrLetterofAcceptanceDate (date/time)
LOAlink (hyperlink)
KtrProposalDue (date/time)


TblKtr
KtrID (PK) (text)
ProjManagerFirstName (text)
ProjManLastNam (text)
Cell (text) ( all cell and office phone formatted for phone # input)
Phone (text)
Email (hyperlink)

Tbllocation
BuildingID (PK) (text)
Street (text)
City (text)
State (text)
Zipcode (number)


Tblcustomer
CustomerID (PK) (text)
FirstName (text)
LastName (text)
Phone (text)
Email (hyperlink)

Tblnarrative
ProjectName (PK) (text)
ProjScopeNarr (memo)
PreAwardNarr (memo)
PostAwardNarr (memo)
CustomerViewNarr (memo)

Tblmipr
WorkOrder (PK) (text)
ScopeMiprAmt (currency)
DesignMiprAmt (currency)
AdditonalFundMiprAmt (currency)
constructionfundMiprAmt (currency)
TotalMiprsAmt (currency)


TblMiprbreak
DocID (PK) (autonum)
WorkOrderID (FK)
ScopeMiprNumber (text)
DesignMiprNumber (text)
ConstMiprNumber (text)
AddFundMiprNumber (text)
ScopeMiprDate (date/time)
DesignMiprDate (date/time)
ConstMiprDate (date/time)
AddFundMiprDate (date/time)
ScopeMiprLink (hyperlink)
DesignMiprLink (hyperlink)
ConstMiprLink (hyperlink)
AddFundMiprLink (hyperlink)

Tblemployee
EmployeeID (PK) (autonum)
FirstName (text)
LastName (text)
Title (text drop-down selection)
Phone (text)
Cellphone (text)
Email (hyperlink)


The below table is based on a forward and backward check calculation to ensure we stay inside our budget. This information is input into the form and not required to be calced and placed in the cell. While that would be nice, I think it is beyond my means at the moment.

Tblprjbreak
WorkOrder (PK) (text)
CurrentProjCostProgrammedAmt (currency)
CurrentProjCostPAKtrProposalAmt (currency)
ScopeFee (currency)
ContractingLaborCost (currency)
Profit (currency)
ConstructionCostLimit (currency)
CurrentProfitBasedonEstimate (currency)
CurrentProfitbasedonKtrProposal (currency)
CurrentCostEstimate (currency)
CurrentProjDuration (number)



TblProject
ProjectName (PK) (text)
BuildingID (FK) (text)
WorkOrder (FK) (text)
WorkOrderLink
FiscalYear (text)
NegotiatedProjDuration (text)
WageRatesLink (hyperlink)
ReqestForProposalLink (hyperlink)
ProjectImagesLink (hyperlink)
MeetingMinuetsLink (hyperlink)
PercentProjComplete (text) (hyperlink)
ProjectMainImage (ole object insert)
ProjectOverview (memo)
AssignedDataNumber (text)
DistrictTrackingNumber (text)


TblProjReview
ProjectID (PK) (Autonum)
ProjectName (FK) (text)
ForwardToCustomerReviewDate (date/time)
ReceivedCustomerCommentsDate (date/time)
CustomerSigDocLink (hyperlink)
ForwardToPublicWorksReviewDate (date/time)
ReceivedPublicWorksCommentsDate (date/time)
PublicWorksSigDocLink (hyperlink)
ForwardToEnvironmentalReviewDate (date/time)
ReceivedEnvironmentalCommentsDate (date/time)
EnvironmentalSigDocLink (hyperlink)
ForwardToFireDeptReviewDate (date/time)
ReceivedFireDeptCommentsDate (date/time)
FireDeptSigDocLink (hyperlink)
ForwardToCommunicationsReviewDate (date/time)
ReceivedCommunicationsCommentsDate (date/time)
CommunicationsSigDocLink (hyperlink)
ForwardToLawEnforcementReviewDate (date/time)
ReceivedLawEnforcementCommentsDate (date/time)
LawEnforcementSigDocLink (hyperlink)
ForwardToPublicReviewDate (date/time)
ReceivedPublicCommentsDate (date/time)
PublicSigDocLink (hyperlink)
ForwardToPublicReviewDate (date/time)
ReceivedPublicCommentsDate (date/time)
PublicSigDocLink (hyperlink)
ForwardToEngineeringDate (date/time)
ReceivedEngineeringDate (date/time)
EngineeringDocLink (hyperlink)
FinalScopeLink (hyperlink)
DesignLink (hyperlink)
ProjectSpecsLink (hyperlink)
SHPOconsultationDate (date/time)
DateExpectedfromSHPO (date/time)
TribeConsultationRequired (yes/no)
TrbalConsultationDateComplete (date/time)

 

[MajP]

It looks like you put some good thought into this, but it would help if you talk it through, how the buisness model works and how the table relate together. One thing you may consider. Your Project review could simply be

TblProjReviewMilestones
ReviewID (PK) you called it ProjID but that seems wrong
ProjName (FK)
MilestoneName (Forward To Public Works)
MilestoneDate
MilestoneNotes (Public works was satisfied...)

TblReviewDocuments
ReviewID (FK)
ProjName (FK)
DocumentType (example Customer Signature)
Document (hyperlink)

The ProjName FK is required since all documents relate to a project. It appears that some documents relate directly to a milestone event, but some do not. So the ones relating directly to a milestone would also include a foreign key to that event. If all programs have all of the listed events and all of the listed documents than this structure may not be better. But I doubt that is the case. Some projects may have additional milestones and additional documents, and this structure could handle that. Other projects may have a modified set of milestones and documents. My structure is easier to develop and maintain. As you can see far fewer fields. However,designing a user interface to handle this structure will be more difficult. In your design you would see what events are done and what events are null. In mine you only would see what events have been accomplished. I then would probably use an insert query as I create a new project it fills my milestone table with the default set of milestones. Then I could add and delete the relevant milestones.

If you get your data structure correct, you can always build a form that supports it. I do not really understand the buisness model but it looks like to seperate forms each with tabs and multiple subforms. I think there is a work order form with subforms containing a lot of the contract and cost tables. The other main form looks like it is the project managment with subforms such as the Review and documents.

 

[jshjnh]

Thank you for your reply!

I think you are correct when you state events are milestones; however, a lot of this information will be input as the form is filled anticipated dates will be placed and for the last two years it is the way the data has been viewed in a speadsheet. Unfortunately that is burned into my head and it is hard to think of viewing any other way not to mention the other users.

The general process:

The first documents we receive are usually the work order and the project scope MIPR.

We then set dates to visit the project, the user
Prepare the scope
Send it out for reviews
prepare estimate
log the info as it returns
send the project to contracting
project sent out for bids
receive the contractor proposals
prepare negotiation documents
negotiate
prepare final negotiation documents
award
turn over to project managers

major portions of the project

Project contacts
Project Scope preparation information and docs
Contracting
The contractor information and docs
negotiations
award

of course finances are linked to all..



there are the documents and dates in the tables not shown above. This process can take anywhere from 7 days to 5 months or longer and be as complicated as a 4000.00 project up to a 10 million or more dollar project.

Throughout the project scoping we forward status reports which could conain any of the information we are imputting. Using excel has became a task to say the least.
I have included a example of how I would hope the tables could be viewed but as I read more and more I think this is impossible. On the other hand this is the way the spreadsheet kind of works now.

At this present time defining relationships is just a much as a task as trying to figure out how to design the tables. The example attached has tables included.. these are just old thought proceses as I am learning and reading so please disregurd their layout. The form would be something I would like to make work if at all anyway possible.

Thanks for your input and suggestions. I have been spending a lot of time reading about the design process and learning the software better. Your further input is greatly appreciated.

 

[jshjnh]

One mroe thing I would like to add MajP.. I do like you design it is simplitic and meets the requirements. Could I use a drop down field that way I can ensure the exact names are placed in the db? then query those exact names..

not that I am that up on query's! .. could I display that infor every time the form is viewed? that way we would not have to produce a report to see what we have to do? it is a visual db.. meant so we can look at the current information wheather null or not.

thanks again!

 

[jshjnh]

any else have any suggestions?

thanks!!

 

[GKChesterton]

The answers to your last questions were "yes" and "yes." I am curious -- how is your project going?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[jshjnh]

I am stuck on the relationships still. I ahve not went to building the form as I am not sure if I have fully normalized my tables and made the correct relationships.. do you happen to have any suggestions on the relationships and normalizeation? if I can any more that is...

 

[GKChesterton]

Make your forms! You've reached the millionth quadracent of "Access newbies who understand and think about what they're doing," and your concern for avoiding spreadsheet-thinking is all you need to succeed on that point.

I only just noticed you had a link up at applewoodmanor. Tomorrow I can take a look at that (I'm on my home Linux now).

I'd be surprised if your app will replace only ONE over-size spreadsheet. If you want to post a copy of that, could be helpful.

My big Access project the last years has been similar to what you describe. We're an engineer-to-order manufacturer (dub-dub-dub dot fountainpeople dot com).

Project <---> Customer
BOM (a list of items; a design package really)
BOM
BOM
BOM 4 BOMS = 4 quotes .... this one sold!


Project <---> in-house Designer (1/2 sales, 1/2 engin'r)

BOM
Product (variable specs, blurred std/custom)
Product
Product

No part of this is simple; It's not just Customer, but architect, contractor, sales rep, facility manager ... Some times we don't know who the customer is until the PO arrives.

I think of my database as document management more than project management. I think that's because our process is not effectively controlled once it is sold and underway.

 

[GKChesterton]

1. Legible naming. Overly long names do get unwieldy; but your shorthand seems short to the point of being cryptic. Don't use spaces or punct'n marks (as you seem to know). Underscores and UseOfCapitalLetters work well. The 'description' column for table fields (visible in the DB Window, must be toggled on) is useful for explanation.

2. SubDataSheets are at least mildly hazardous in my opinion.

3. How many users will the app have? And how many concurrently over a network? Will anyone be plugging in from remote locations? An Access DB can survive poor design to to a wonderful degree if it's single user. A DB that is built well can sustain remarkably high volume. But a poorly done DB that is shared on a network is destined for big problems.

4. Also the user base determines how much automation you need (it "feels like a program," enforces consistent good data entry, facilitates work flow, etc). It's hard in my experience to budget for the time that goes into learning, building and polishing the automation. To put it another way, many times a quick promise has turned into unplanned days of work.

5. The Relationships window can be used more helpfully. It's good for only three things: [ul]
[li]visualizing your structure [/li]
[li]declaring 'template' relationships for when you construct queries later on[/li]
[li]declaring referential integrity [/li] [/ul]
And comments for these, respectively:[ul]
[li]I always keep a printout posted[/li]
[li]very handy for saving time, and the occasional confusion when you otherwise would have made a wrong connection. When you connect the tables with lines, ALL YOU ARE DOING is fulfilling these first two functions. [/li]
[li]critically important; you needen't establish RI using the Rel. Window, but you need to somehow and this is probably where you should start. And you haven't done it unless little diacritical marks are visible on your connections. [/li][/ul]

6. There are some basic structural problems. [ul][li]Two tables in a 1-to-1 relationship (e.g., they'll always have the same primary index and same number of records) are probably best combined. It's a drag having a table with dozens of fields, but dividing does not equal conquering. (The only exception I know is advanced security.) [/li]
[li]When a table is structured redundantly with key-value, key-value, key-value, then you probably need to configure it as a table with 2 fields: key and value. Plus another field to tie it the parent record. What you've done is converted it from a 1-to-1 relationship to 1-to-many. (Which helps the 'dozens of fields' problem!)[/li][/ul]

If you look me up at the web site cited earlier and ask for Scott, I can explain more and do it from a bigger perspective.

 

[jshjnh]

Thanks! You have provided some great information!. I will look you up, busy at the moment. What hours are you there? Perhaps an email? I am in and out of my desk all day long from 7 am to 6 pm... I have been playing with the tables and relationships since I posted that link above.

The database will have only 3 people entering information.. possibly 10-12 viewing over a network, not at the same time. I would estimate approximately no more then 4 max at one time. You are correct instating this is more a tracking database than a management type. We need to track where we are at certain times and the viewers can access the info when needed.

As far as the names, I understand what you are stating, the problem is they are too long and I rely on the description to tell me what the data is. I have included a more recent link and will try and contact you today if our hours match.

thanks!!! many thanks!!

 

[GKChesterton]

I'm eager to communicate more, but leery of posting more contact info (compliance with forum ground rules). You have what you need to contact Scott at

http://www.fountainpeople.com,

so do that as soon as you can.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

I would urge you to have the data from your existing spreadsheet(s) in place as you begin. It's a snapshot that will become stale; maybe it's only a representative sample, if the volume is unmanageable.

You can start shadowing your currently-used tools right away. You're not making any commitment to the data.

The joins in Relationships are good to see. Except, I'd think a SOW relates to a project and doesn't have a person intermediary.