Access Table - Relationship Question

[vixtran]

Good morning everyone.



I am quite new at access and have been reading a lot and trying to set up what I would consider to be a fairly reasonable database to give you some background.

The database is to collect information about projects. This information would include no less than the following:

Project name
financial information: estimates, funding, negotiating information
Key dates
Individuals working on project
The customer information
Work Orders
Key project documents
Project notes

Many of these topics are broken down into the respective data required for each project and the only information what would apply to the many side of the database if the user and the individuals working the project. Every other piece of information is only specific to that project, can, and will never be used again. Right now all if this information is contained in a spreadsheet; however, it is becoming massive and too much to maintain.

I have a form set up with a tabbed control on a main form. The main form contains the project title, location, work order and a couple key dates and links. The tabbed form contains other relevant information to the project and their related tables. When I update the main form and fill out all of the tabbed sections the main form will go to a new record. The tabbed forms will not and I cannot add new records. Also, when I go back to record 1 on the main form it will not display my information, though, I verified it is located in the tables.

Right now my relationships are set up on one to many based on the work order cell contained in each table. With the primary key set to auto number in every table with the exception of the main table where the work order is the primary key. I was going the one to one approach; however, I have read over and over that this is not common at all and thought I may be barking up the wrong tree.

I guess my questions are.. am I approacing the table design correctly?

What would be causing my form problems?

and in reality is this a flat database?

Like I said I am new and learning.. I might not be asking the right questions and thus not finding the answers I need through net searches. Not to mention I am having a hard time getting away from the "spreadsheet" approach to life!

Thanks in advance!

[redknite]

Lots of good reasons to be using access for this! I would set up something along these lines:
tblProject-
projectID (PK)
datestart
dateend
CustomerID (FK)
WorkID (FK)
LocationID (FK)
Docs (attachment)
Notes
FinancialID (FK)

tblEmployee-
EmployeeID (PK)
first
last
email
phone
etc

tblCustomer-
CustomerID (PK)
first
last
email
phone
etc

tblLocation-
LocationID (PK)
LocationName
street
city
state
etc

tblWorkOrder-
WorkID (PK)
EmployeeID (FK)
Date

tblFinance
FinancialID
Any info you're collecting

This is a very basic database setup...but it should get you started in the right direction...
Work order table could be changed as I'm not sure if that's what you want to base the info on or the project or the work order (ie why is project name different than work order? are there more than one work order per project or more than one project per work order?)...so this may be a little off base. but financials and docs could be stored in tblwork too.

If you want to base the work order as main...

You can see employee and customer are similar tables, but if you want to collect diffent info about them, you should have 2 tables for them.

Tabbed forms might not be the direction you want to go just yet, but it would be worth looking at your database (sans incriminating data!) and see where it's going.

[vixtran]

Thanks for the reply! I have read so much; but it is great to see an example. Our projects are based on a unique work order. The work order will never be duplicated and there is only one work order per project. While there may be a few instances where there are more than one work order, we must still track that work order as a second project financially.

Many departments track the projects by their work order, I figured it would be good to follow suit. My database is a mess I am sure! if you would liek to take a look at it I would be glad to show it to you.. how would I do that?

I have to warn you though.. I learn by diving in lol.. then figuring the problems as I encounter them.. while this might not be the best approach it allows to me think deeper into the problems and ask questions I would have never asked..

I have to track a large amount of information.. to include dates or submissions, files and so on..

thank you so much for your reply!

[redknite]

so there is 1 work order per project. Is there multiple projects per work order? If not, then you have redundant tables here...

You can attach the file by zipping it and then uploading through the "manage attachments" button below when you're responding.

As for your approach to database design, the actual name, as I know it, is Agile design. Many people make their databases through the process you're going through and many swear it's the only way! A note would be to get the database structured the right way before you go any further with forms though.

[vixtran]

Yes there is only one work order per project. I have attached the databases with the forms I was messing with and the new one with the tables I am starting (trying) to restructure. I know the concept of data layout is probably pretty simple; however, after being around spreadsheets so long it is hard to look at information any other way.

All of the information I have listed in the tables is required through one form of report or another. Mostly tracking where and what stage we are at in the development of a project. As you can see the visual information is just as important as the reports we will get from the database.

I understand what you are saying about proper structure prior to form building. I know it is easier in the long run.. Playing with the software allows me to become familiar with it. I have been doing a lot of reading in books and on the net as I encounter little things. I know it isnt the right way to go about it though.

thanks again for your insight and help!

[vixtran]

Hello again everyone.. I have been working on trying to get the tables figured out. Perhaps somebody can offer ideas on how to simplify or even link them together.

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 [COLOR=blue !important][COLOR=blue !important]documents[/COLOR][/COLOR]/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 ([COLOR=blue !important][COLOR=blue !important]hyperlink[/COLOR][/COLOR])


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)
[COLOR=blue !important][COLOR=blue !important]Email[/COLOR][/COLOR] (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)