Re: Access or Excel - which is the way to go for my project?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Tina,

Thanks for responding. I will review the links within the link you provided.
A question though ... are my preliminary table layouts that far off that you
recommend I review the links rather than commenting on what I've done? :)

Regards,

Chris

"tina" wrote:

Before I outline my tables, let me say that I'm not exactly clear as to
the
proper use of foreign keys so please bear with me. :)

rather than answer your specific questions one by one, Chris, i'll recommend
that you STOP working on the database, and study up on relational design
principles until you have a clear understanding of the basics. it's good
that you don't have a tight time constraint because this is - to my mind,
anyway - the most difficult aspect of building a database. BUT the reward is
that if you spend the time now, you'll save tons of time and frustration in
every other aspect of building the database (you'd be amazed at the number
of post to these newsgroups asking for help with queries, forms and
reports - when the actual problem is the op's tables/relationships
structure). AND as you ask for help on specific issues, you'll be able to
communicate more clearly and understand the answers better - because we'll
all be speaking from the same base of knowledge. and ultimately, your
database will do a better of job of ensuring data integrity and manipulating
data to provide the information your business needs. for links to many
resources, see
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

once you've got the basics of relational design principles under your belt,
suggest you review your own tables/relationships structure, and make the
changes you deem appropriate. then, if you'd like some feedback to see if
you're on track, post a description of your tables/relationships and ask for
a critique.

hth


"CB" <CB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6FD1C318-C3A2-48AE-B576-56D12A64C332@xxxxxxxxxxxxxxxx
Hi John and others,

Let me apologize in advance for the lengthy post. I appreciate anyone who
is
willing to read through it and help me out. LOL!

Before I outline my tables, let me say that I'm not exactly clear as to
the
proper use of foreign keys so please bear with me. :)

Currently, there is one contractor who holds one multi-year contract. I'm
probably over-complicating things at the moment but I'm trying to look
ahead
to the next contract, which could possibly be held by a different
contractor.

tblContractorInfo
PK - CINum (autonumber)
CICompName
CIAddress
CICity
CIProv
CIPostCode
FK - CSContNum (one contractor could have multiple contracts though not at
the same time)
FK - CCName (one contractor could have multiple contacts)

tblContractorContacts
CCName - should this be the PK or should I use an autonumber?

tblContractSpecifics
PK - CSContNum (text - assigned number is alphanumeric)
CSTitle (not sure if this is necessary)
CSStartDate
CSEndDate
CSValue
CSCoding

The next part seems rather tricky to me. Since the contract is multi-year,
monies are assigned for a given fiscal year. If the monies aren't used or
the
amount is exceed, values for future fiscal years can be adjusted using a
change order. Here's how I see it:

ChangeOrder FiscalYr AssignedAmt
0 09/10 10,000
0 10/11 20,000
0 11/12 30,000
1 09/10 5,000 (only 5,000 used in 09/10 so 5,000 carried forward)
1 10/11 25,000
2 10/11 33,000 (8,000 overrun in 10/11 so 8,000 deducted from next year)
2 11/12 22,000

At all times the sum of the fiscal year values, shouldn't exceed CSValue.

Hmmm, should I have a fiscal year table as below?

tblFiscalYear
FiscYr - should this be the PK or should I use an autonumber?

Do I need two tables - one with contract amounts and with change order
information?

tblContractAmounts
PK - composite of two FKs below????
CAFiscalYr (or FK - FiscYr??)
CAAssignedAmt
FK - CONum (a given fiscal year amount could have multiple change orders)
FK - CSContNum

tblChangeOrder
PK - CONum (autonumber)
CODesc
COReason

The next bit is the quote requests and result work order submissions.

tblStationManagers
StnMgrName - should this be the PK or should I use an autonumber?

tblQuoteRequest
QRNum (autonumber)
FK - CSContNum
PK - composite of above two fields
QRDate
QRFiscalYr
QRDesc
FK - StnMgrName (one manager can generate may quote requests)

tblWorkOrder
WONum (text - can be 1, 1a (an amendment), 1b, 2, 3, etc.)
FK - CSContNum
FK - QRNum
PK - composite of above three fields
WODate
WOEstAmt
WOEstLink (submissions will be scanned and linked)
WOStatus (accepted, amendment requested, or rejected)
WOComment (may be null if WOstatus is accepted otherwise entry required)
WOStatusDate
FK - StnMgrName (one manager can accept or reject many work order
submissions)
WOActualAmt
WOActLink (submitted bills to be scanned and linked)

Well, I think that's it. Once again, thanks in advance for all your
comments
and suggestions.

Warm regards,

Chris


"John W. Vinson" wrote:

On Thu, 21 May 2009 14:20:49 -0700, CB <CB@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

It seems wrong somehow to have multiple worksheets with similar
information.
Is this a good candidate for a database or might it make more sense to
rework
the worksheets properly and incorporate coding and forms into Excel?

You have at least five different kinds of Entities (customers,
workorders,
jobs, quotes, and amendments) in one-to-many and many-to-many
relationships. I
admit I'm biased (I do Access, but very little Excel) but this just is
crying
out for an Access relational solution.

If Access is the way to go, I'll reply with how I think the tables
should be
laid out and ask for your advice as to whether or not I've normalized
properly.

Please do.
--

John W. Vinson [MVP]




.

Quantcast