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

  • Thread starter Thread starter CB
  • Start date Start date
C

CB

Hi all,

This is the first time in almost a decade that I’m creating a database from
scratch. I don’t think I normalized my last one properly so I’m hoping to do
things right the first time.

I guess my first question should be whether you think my project is worth
migrating from Excel (only very recently; earlier as Word) to Access. The
project would be managing work orders for a maintenance contract.

The process is as follows:
1) Currently, there is only one contractor that holds a multi-year contract.
Dollar values are assigned for each year but may be changed, for example, if
monies allocated for one year aren’t all used, they can be carried forward.

2) When a particular job needs to be done, the customer sends a quote
request to the contractor. Multiple quote requests can be sent out in one
year.

3) The contractor responds with a work order estimate.

4) The customer either
• Accepts the work order, in which case they send out an acceptance letter.
• Asks for amendment to the work order, in which case they send out a letter
requesting an amendment and the process reverts to step three above.
• Rejects the work order, in which case they send out a rejection letter.

Only recently, an Excel workbook has been used. All worksheets are laid out
and formatted to LOOK like forms when printed. The first worksheet holds the
contract information. The second worksheet is for change orders to the
contract to transfer amounts from one year to the next (hasn’t been used
yet). The third worksheet is for a quote request. The last worksheet is a
work order approval.

There can be multiple copies of the change orders worksheet (one for each
change order), quote request worksheet (one for each quote request), and work
order approval worksheet (one for each work order approval).

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?

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.

Thanks for your assistance!

Chris
 
The short answer is yes. Anytime you have a situation where you are dealing
with the storage and entry of data, you should be using a database. You can
track who makes which changes and when. You track progress and makes sure
the same data elements are used across the entire project. If you have more
than one copy of the same data, one those copies will change without the
other changing and you will have problems.

That being said, you also need to look at the amount of effort it may take
to develop the database vs. the amount of effort it takes right now.
Personally, I have never found a situation where it was better to leave
something in Excel.

At the very least, you should set up a development database to test some of
the key ideas. If they work better, database away.
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.
 
The short answer is yes. Anytime you have a situation where you are dealing
with the storage and entry of data, you should be using a database. You can
track who makes which changes and when. You track progress and makes sure
the same data elements are used across the entire project. If you have more
than one copy of the same data, one those copies will change without the
other changing and you will have problems.

That being said, you also need to look at the amount of effort it may take
to develop the database vs. the amount of effort it takes right now.
Personally, I have never found a situation where it was better to leave
something in Excel.

At the very least, you should set up a development database to test some of
the key ideas. If they work better, database away.
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.
 
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?
Click to expand...

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.
Click to expand...

Please do.
 
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?
Click to expand...

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.
Click to expand...

Please do.
 
Hi PJ,

Thanks for your comments PJ. It confirms what I thought. I was concerned at
first that the amount of development time might not make this small database
worth it. On the other hand, it will be good to refamiliarize myself with
designing a database from scratch. Since this is was on my supervisor's
wishlist with no real requirement to get it done, I can fit it in where I can
so development time isn't really an issue.

Regards,

Chris
 
Hi PJ,

Thanks for your comments PJ. It confirms what I thought. I was concerned at
first that the amount of development time might not make this small database
worth it. On the other hand, it will be good to refamiliarize myself with
designing a database from scratch. Since this is was on my supervisor's
wishlist with no real requirement to get it done, I can fit it in where I can
so development time isn't really an issue.

Regards,

Chris
 
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
 
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
 
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. :)
Click to expand...

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 said:
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


Click to expand...
 
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. :)
Click to expand...

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 said:
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


Click to expand...
 
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 said:
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. :)
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
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 said:
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. :)
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
i didn't review them thoroughly, hon, just a quick glance. i was more
concerned with your statement about not understanding foreign keys. in most
cases, in these newsgroups, my response is going to be geared toward
encouraging folks to learn the basics, for the reasons i already posted.
there is just no good way to use Access effectively without those skills.

and, once you've got them, you're the best person to do a process analysis
and write a model for your data - because you'll know more about the process
and the data than you could ever hope to tell anyone in a newsgroup thread.

hth


CB said:
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 said:
Before I outline my tables, let me say that I'm not exactly clear as
Click to expand...
to
the
proper use of foreign keys so please bear with me. :)
Click to expand...

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 said:
Hi John and others,

Let me apologize in advance for the lengthy post. I appreciate anyone
Click to expand...
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
Click to expand...
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
Click to expand...
or
the
Click to expand...
Click to expand...
 
i didn't review them thoroughly, hon, just a quick glance. i was more
concerned with your statement about not understanding foreign keys. in most
cases, in these newsgroups, my response is going to be geared toward
encouraging folks to learn the basics, for the reasons i already posted.
there is just no good way to use Access effectively without those skills.

and, once you've got them, you're the best person to do a process analysis
and write a model for your data - because you'll know more about the process
and the data than you could ever hope to tell anyone in a newsgroup thread.

hth


CB said:
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 said:
Before I outline my tables, let me say that I'm not exactly clear as
Click to expand...
to
the
proper use of foreign keys so please bear with me. :)
Click to expand...

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 said:
Hi John and others,

Let me apologize in advance for the lengthy post. I appreciate anyone
Click to expand...
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
Click to expand...
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
Click to expand...
or
the
Click to expand...
Click to expand...
 
Back
Top