vixtran

Good evening everyone. I am tackling a database and while I have a little experience working with access 2003 I am a bit above novice level. I am creating 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?
Ideas on relationships?


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 calculated 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)