Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009

    Unanswered: Help with Tables

    Good Afternoon 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!

    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)

    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)

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

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

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

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

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

    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)

    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.

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

    ProjectName (PK) (text)
    BuildingID (FK) (text)
    WorkOrder (FK) (text)
    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)

    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)

  2. #2
    Join Date
    Jul 2004
    South Dakota
    Welcome to the Forum!

    You seem to be headed in the right direction but it looks like you have a ways to go. Keep reading about normilization as the table designs will be key to getting to getting everything working right and you'll avoid headaches down the road.

    Let's just take a quick look at your Contract table. Remember, the Contract table should only contain information directly relating to the Contract itself. (Not knowing the intricacies of the project some of these assumptions will most definitely be wrong.) The Funding details or Project Awarded details, I wouldn't think, should be part of the table. Does that make sense? I don't need to know if the Project was awarded if I'm only interested in Contract specifics.

    Keep at it as it can take a long time to get the tables setup correctly. Good luck.


  3. #3
    Join Date
    Jun 2009
    thank you for your comments canupus! any suggestions or comments help me look at things in a diiferent way.

    The funding type has to do with what color the money is as to what we can do on the project. There are types of money only used for repairs, types only used for equipment, and other only used for new construction. This also may inpact how the project is let.

    The the funding details, the awarding of the project, and the anticipated award are all done in the contarcting department. These dates are used by use to help us ensure we meet our goals. That does not mean they should not be in that table, my question is after hearing my explanation do you think they should still be there? The table could be called contracting?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts