Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Access 2003 table help

by vixtran Jun 22, 2009 1:57PM PDT

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)

Discussion is locked

3 Posts
- Collapse + Expand Details
- Collapse -
Re: Access tables
by Kees Bakker Jun 23, 2009 6:27AM PDT

I didn't really study it, just had a quick look. But it doesn't look too bad. This might not be the final solution, but it might very well be a good step on the way to it.

My preference for making a data model is top-down. Start with entities, give them keys and one or two atttributes to give an idea what they mean. Then define the relationships. Both type (1:1, 1:n, n:n) and the pronunciation both ways ("A customer HAS zero or more orders", "An order IS MADE BY exactly one customer"). Then draw th ERD (entity relationship model). Then change all n:n-relationships to 2 1:n-relationships. Then add all necessary attributes to make a basic relational model. Then add all validation/lookup/masterdata tables (I use them more or less as synonyms) you can think of. More will follow, that's for sure.

I'm moderately experienced with making an Access application. My first guess for something like yours (consisting of tables, queries, forms, reports, macro's and probably a lot of VBA-code to make it useable by other people then yourself and glue it all together) would be that it would take me a months or so (could become 2) to write it. Without experience, it will be longer.

Kees

- Collapse -
moving to forms
by vixtran Jun 23, 2009 1:41PM PDT

thank you Kees!

Once I determine all of the relationships I am not sure how to mend it all together and even more what access is capable of using forms in simple ways. Meaning not so complicated I have to hire a programmer to make it work and at the same time have others understand it.

Where would I start with the forms? Would I have to populate the customers, faciliy addresses, and project employees prior to the other tables? I would like to set it up to be populated in sequence maybe in a tabbed form starting with the project information, address, the financial information, the user, employee.. and so on.. is this possible using a tabbed form and the basic table layout that I have?

thanks again!

- Collapse -
Re: forms
by Kees Bakker Jun 24, 2009 8:03PM PDT

Forms to enter data into the system, and modify it, typically are based on one table only, but it's quite well possible to base them on an updatable query also. Of course, they can display data from other tables and use data from other tables in drop-down boxes.
And it's possible to have subforms with 'related' data that is not in the table itself.

Then we have the difference between 'master data' and 'transaction data'.
Master data are for entities like 'type of order', 'way of payment', 'category of customer', 'product', but also employees and - most often - customers. The things represented by master data exist even when the company has nothing to do. They just are there.
Transaction data represent - broadly speaking - the things the company makes money with: orders, projects and related data like customer contacts, documents, bills and payments.

Master data must be present before you can enter transaction data. Those generally are separate screens, maybe even in a separate menu or with another authorisation.
The best way to organise transaction forms is to suit it somehow to the workflow. If orders and payments are handled by different departments, it are separate transactions on different forms. The strict "one table = one form, used by everybody who has to change something in the table" needs not be optimal.

If you have a transaction with so many 'subtransactions' that it doesn't fit on one screen, or uses different tables, you can use something like tabbed screens indeed. Or use a 'wizard-like' interface with 'previous' and 'next' buttons. But be sure to use a 'rollback' if the user decides to cancel a multi-tab or multi-screen transaction.

It's not really easy, all of this, I'm afraid.


Kees

Back to Office & Productivity Software forum

CNET Forums

Operating Systems
Software
Electronics & Gadgets
Hardware
Tablets & Mobile Devices
General Help
Brand Forums
Roadshow Autos
Off Topic

Other Forums

Forum Info