table structure questions

[tjr]

I have a db in access 2003 to track our clients, the realestate contract's
info, the jobsite (where we are building) the lender, etc. Now I need help.

I track construction job sites and request 'draws' from the lenders for each
jobsite in increments predetermined by the lender in most cases.

I have to track each homes construction according to the schedule given to
us by each different lender. Each lender has different payment schedules.

Some banks work on a percent to complete (think line items on a home
construction invoice) other lenders require that a certain number of steps be
complete prior to requesting a 'draw' or pyt. from the bank.

I want to track everything relating to these payments including the contact
events that take place to request the funds each time (phone calls, faxes,
emails, documents submitted, etc.). I also want to track the contract amount,
$ received prior to current draw requests, current draw requests & $ balance
to finish.

in addition to the accounting of the money we receive at each draw , I also
want to track the construction of the house. Right now I have each banks
schedule in one-note (as a checklist) and I copy them as a template from
'banks' to the individual homeowners section and then email and print the
schedule for the appropriate field personnel. they check off what is done and
when I can determine from the returned lists when I can request $ from the
lender (according to the schedule) I follow the instructions provided by the
bank, submit the required documents and request the draw. (I do not expect to
be able to have others check off items as is happening now, I want to be able
to print the schedules and have field personnel indicate on paper what is
done so that I can enter it in the db. Mgmt. likes to see the schedule of
construction according to the rules of the lender and what has been completed
and what has been paid. - that I want to do in access by reports, etc.

I don't know the best way to have multiple schedules in access, link the
schedule to the contract or client and be able to indicate what has been
completed, what has been paid (and the amounts) and what is left over.

Please respond with questions if I can clarify the process that I am trying
to track using access.

I need to know how to structure the tables and create relationships between
the new tables.

also, should all phone numbers be in a seperate table? One last question,
when using a linking table to create a many to many relationship do i need to
physically enter data in the linking table or does it work behind the scenes.

Access inside out 2003 explained the design of linking tables but not so
much the usage of them.

Thank you very much for all of your help.

I appreciate your kindness.

 

[Edward Reid]

tjr,

This is a rather complex application to be building in Access given your
limited knowledge of databases. Have you searched resources related to the
construction industry to see whether software is already available to do
what you want? If so, what is it that your business requires that available
software doesn't do? And if it's close, is it possible that a software
vendor would add the feature you need?

When you can buy software that already does what you need, that's
preferable to building your own 99% of the time. (The exception is when the
only available software is horribly overpriced or just plain wretched
despite technically doing what you need.) You get more features, better
design, and an interface built by people who know your business. People in
your own line of business are the best ones to ask -- personal contacts,
trade associations, trade magazines, web sites. I don't know the
construction business, but I know that it's very large and thriving, and I
would assume that a lot of off-the-shelf software is available. It sounds
to me like your problems would be standard ones, though as I say I don't
know your line of business.

Often people hear that database software (such as Access) is a good tool,
so they say great, we'll just put our data in a database. The problem is
that, for all their power, database packages are just programming tools. If
you have enough experience in programming and database design and IT in
general to determine that building your own custom software is the best way
to go, and to make a reasonable estimate on what that is going to cost you
(including ongoing maintenance after implementation), then fine. But if you
are assuming that using Access means you are getting the advantages of
off-the-shelf software, think again. What you are doing is building custom
software, with all the attendant costs. You should do that only if you have
determined that it is the best, and cost-justified, solution for you.

Edward

 

[tjr]

Thank you for your response. I have tried several accounting packages each
with 'crm' modules built in or sold seperately, but they do not seem to offer
what I need in terms of specialization. MS SBA 2006 crashed my outlook bcm.
quickbooks is too quirky, too many windows and the crm module is not worth
the price.

I am 1 person working as a subcontractor, which means bluntly that I buy my
own software. I can not afford any of the more robust packages right now
whether they help or not. I do not need a fancy db with bells and whistles
and buttons that automatically open my garage door or turn on my television.
Just the basics, i will be the only one using the db.

I have done some searches, i don't seem to find anything.

Lets start with one thing at a time shall we? I do appreciate any and all
suggestions and will implement them all and see what works.

How am I going to go about entering multiple schedules where each step must
be marked as complete and then marked again as paid.

should each banks schedule be it's own table or should each schedule be a
record in the banks table?

I would appreciate the help. I admit to not knowing a ton about access but
I have read access inside out and am re-reading it to grasp the more
difficult topics.

Our company is just a year old and is growing by leaps and bounds. We
simply can not invest in anymore software.

That is why I am trying so hard to learn access. Do you have any suggestions
on the design for the schedules table/s?

I suppose I can just insert a hyperlink field to the one note page, but I
just can't begin to conceptualize how this would be simplest to set up.

Edward, thank you again for your advice, I do appreciate your time in
reading and responding to my posts.

 

[John Vinson]

Lets start with one thing at a time shall we? I do appreciate any and all
suggestions and will implement them all and see what works.

How am I going to go about entering multiple schedules where each step must
be marked as complete and then marked again as paid.

should each banks schedule be it's own table or should each schedule be a
record in the banks table?

Neither. I'm not sure what a "schedule" means in this context, but if
each Bank has multiple steps, and each Step may be completed for
multiple banks, then you have a "many to many" relationship, which
needs THREE tables:

Banks
BankID
BankName
<other info about the bank>

Steps
StepNo
Description
Cost <if that's appropriate>

Actions
BankID <link to Banks, who's doing the step>
StepNo <what are they doing>
StartDate <or date/time if that's appropriate>
Completed <yes/no>
Payment <currency or yes/no>

You'ld use a Form based on Banks with a subform based on Actions; each
step would be one more record in the Actions table.

I would appreciate the help. I admit to not knowing a ton about access but
I have read access inside out and am re-reading it to grasp the more
difficult topics.

Our company is just a year old and is growing by leaps and bounds. We
simply can not invest in anymore software.

That is why I am trying so hard to learn access. Do you have any suggestions
on the design for the schedules table/s?

I suppose I can just insert a hyperlink field to the one note page, but I
just can't begin to conceptualize how this would be simplest to set up.

That would be a LOT more complex, IMHO.

Check out the links at http://www.mvps.org/access and/or at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html,
especially the Database Design 101 links.

John W. Vinson[MVP]

 

[tjr]

Oh, thank you, thank you for helping me and trying to understand so I can
arrive at correct table structure. I wonder if I can better explain with
regards to the first part of your response, I'm not sure if, when you
said,"each step may be completed for multiple banks" will apply, unless i am
not 'normalizing' each table in my thinking properly. Please excuse me if i
go on & on here but it might help you to help me, and understand the goal or
the desired results,which I am very grateful for.

joe homeowner (mostly investors so multiple homes under constr. @ 1 time)
uses 1 or more lenders to finance each build. Each lender gives me a list of
rules to follow throughout construction, things like what documents
pertaining to the build I must provide (most of which I must track as well as
schedule, create, send out, receive back, file and record) and what the bank
inspector (whose inspection I must also schedule and track) will expect to
see complete @ any given disbursement request (that comes from me-I tell the
bank to 'show me the money' but only when they tell me I can according to
their predetermined disbursement schedule rules as outlined above.

In most cases each lender allows 5 sometimes 6 inspections throughout
construction. The inspector determines what has been completed and sends that
inspection report back to the lender where they determine how much money to
pay out each time i request an inspection and a corresponding disbursement or
draw request.

I must mention here that each lenders disbursement schedules are different
and sometimes they have more than one, however, only 1 draw schedule from
that lender will apply throughout construction. Some lenders allow a percent
to complete disbursement schedule where we can submit a request for funds
whenever we decide. Most however, outline in the schedule what must be
complete (they dictate a certain # of steps be complete prior to requesting
inspection otherwise we fail that inspection) in order to receive the
disbursement. Each disbursement schedule is technically a % to completion
based invoice of the build but some lenders bend a little bit on the timing.

The schedules all resemble invoices for building a house. Everything from
the concrete pad to the fixtures is accounted for however it is grouped
usually into general categories so there are only an average of 25-30 'line
items' per draw or disbursement schedule that we can request to be paid for
at increments of usually 5 or 6 times throughout construction. We can receive
more lender inspections but they cost alot more.

I need to track account for and inevitably 'see' in reports, etc. the
following things: the overall disbursement schedule per house which is
presently printed in list form and used similar to a checksheet for the
coordinator so they can report to me when it is time to request a
disbursement. I print the steps in the build that must be complete according
to each lender with a category heading like draw 1, draw 2, draw 3, etc. so
that the coordinators know that when they get to the last item in the
category marked "current disbursement" they can give the paper back to me and
I request the disbursement. I then give it back to them once disburs.
complete or print a new one, etc. and the cycle continues.

I also use this list during meetings with management where we focus on
expected receiveables according to ea. homes construction progress, and so
that they can micromanage the coordinators more effectively by seeing when
the coordinators should be reporting to me according to the banks
requirements, etc.

All disbursement requests require me to indicate not only the steps (or
'line items') in the disbursement schedule that I am requesting payment for
but also, the amount of money that I have requested so far, the amount that
each draw's total of 'line items' requested equals and the balance to finish.


Also sometimes there are special site improvements at certain jobsites where
there is a pool or a deck or a seawall for some examples that must be tracked
seperately from the disbursement requests for the construction of the home.

I hope that this lengthy but informative description of what it is that I
need to do helps you in helping me to come to a sensible table structure.

I can't thank you enough for your help and look forward to hearing from you
again soon.

Graciously yours,

tjr

 

[Edward Reid]

I have done some searches, i don't seem to find anything.

tjr,

Glad to see you've done the homework.

After reading your additional explanations, I still think you're biting off
a lot more than you realize, even while I sympathize with the
capitalization issue.

It looks to me like you have a very large number of variations on a theme.
Is there any way you can simplify it? Ignore a few details in the database
in order to decrease the number of special cases you have to program for,
and handle those details manually?

In any case, your initial task has to be codifying what data you have in
all these schedules, disbursement orders, etc. Then you'll need to codify
what has to be done with all those items. Only then can you start designing
tables. You obviously know all this stuff, but codifying it for a database
system is a quite different task.

I have to get on a plane in a few hours -- I understand your problem of
being a self-employed subcontractor, albeit in a different field. Good
luck.

Edward

 

[tina]

PMFJI, John - it may help you (or maybe not!) to read the thread from last
Nov where tjr and i discussed his process at some length. sounds like he's
starting from scratch again, so perhaps the discussion was not too helpful
in the end - but at least his posted process descriptions may give you a bit
more information.
http://groups.google.com/group/micr...ess*+author:tjr&rnum=1&hl=en#8d587bf4b5106144

hth

 

[tjr]

Tina,

God bless you for still being here helping poor saps like me, fyi:go go girl
power! Last night I tried to post a "Tina are you out there" post and my
modem went off line, so it never posted. So now I copy all text before I hit
post. I wanted to mention our conversation to John but thought it might seem
ungrateful for his fresh outlook on an old problem. Yes, I am back at it
again, other deadlines took precedence immediately after digging into it in
Nov. but now I am back at it and I am still having fun learning.

I followed the instructions that you had given me after the table revisions
and I am not sure what happened but the newly designed db seemed to 'not let
me in' I started at everypoint to enter data and it would error message that
I needed a field here there and everywhere else, which is good from a dbl
check accuracy standpoint but I could never get into a table to enter data
with out getting locked out.

So, since i am able to devote time to my project again I started from
scratch using your previous guidance and started adding the tables and
relationshiships slowly a few at a time. I am able to enter clients now and
link them to contracts and the corresponding jobsites. I am pleased so far.

I am just starting to add the lenders and all the disbursement schedules and
inspections, etc. In looking at my recent post and the confusion regarding
the process I thought it might be good to better explain it and perhaps come
to the best possible structure arrangement.

If I may ask you specific questions about that post you made in Nov?

You recommended certain table arrangements which I would like to clarify.

tblDrawSchedules
DrawSchedID (pk, Autonumber)
LenderID (fk from tblLenders, Long Integer)

***Q: do you think this table is where I should 'store a template' of the
draw/disbursement sch. in field with a combination of comment and/or
checkboxes to indicate perhaps if the lender only qualified a portion of the
% allowed or other comments and a yes/no to indicate paid/not paid, etc.
OR: Did you have something else in mind for this table?****

then, you mentioned 2 alt. tables if there was a sharing of a schedule which
there is not. However, since i am not employing the use of the
tblLenderDrawSchedules
LDSchedID (pk, Autonumber)
LenderID (fk from tblLenders, Long Integer)
DrawSchedID (fk from tblDrawSchedules, Long Integer)
(this is a linking table between lenders and draw schedules.

****which was one of the linking tables you designed, I am confused because
later in tblcontracts (below) you mentioned one of the fields used in the
alt table that I do not need so which field would I substitute here?****

tblContracts
ContractID (pk, Autonumber)
ClientID (fk from tblClients, Long Integer)
SiteID (fk from tblJobSites, Long Integer)
LDSchedID (fk from tblLenderDrawSchedules, Long Integer) **** Here. Would
I substitute: field drawscheduleid from tbldrawschedules?*****
ContractAmount

next...

you mentioned tbldrawdocuments
docid (pk autonum)
doc name

****what in your design's eye was this table holding. simply a doc name -
since there are standard docs amongst all contracts that must be provided to
ea. lender however, there are some non-standard docs, lender specific docs,
docs to owners, etc - where would I store these docs? In this table, too?****

now... tbl drawsteps
stepid (pk autonum)
stepname

****Q: perhaps i was wrong in my first table mentioned in this post that you
designed. Are you seeing me have the individual steps in each draw here?
This table confuses me a bit (I feel bad saying that because I can only
imagine how you feel re: my specific scenario.) I am just trying to
understand what you see as going where. a few more questions about your
previous table design ideas...

tblContractDraws
ConDrawID (pk, autonumber)
ConDrawNumber (i'm assuming that a specific draw is just a number in the
draw schedule, such as draw "3" in a seven-draw schedule)
ConDrawDate
ConDrawAmount
note: in this table, each draw for each contact would be listed once. so a
contract with seven draws would have seven records, and a contract with five
draws would have five records, etc.

tblContractDrawSteps -
******OH! I think I am closer to understanding your design Ideas. This
clears up my earlier misconception regarding the table draw steps mentioned
earlier. This is where the steps of the disbursment are listed. Does this
structure mean that i have to type the line items each time for each contract
or would a field be named "any of the possible draw steps" and get marked
somehow as complete here???? - Ok, sorry maybe I am still confused!!!********
DrawStepID (pk, Autonumber)
ConDrawID (fk from tblContractDraws, Long Integer)
StepID (fk from tblDrawSteps)
DrawStepDateCompleted
note: in this table, each step of each draw would be listed once. so a
specific contract draw with eight steps would have eight records, and a
contract draw with ten steps would have ten records, etc.
further note: ContractID is not included as a foreign key, because each
contract draw is already identified with a specific contract.

tblContractDrawDocuments
DrawDocID (pk, Autonumber)
ConDrawID (fk from tblContractDraws, Long Integer)
DocID (fk from tblDrawDocuments, Long Integer) * ***so draw docs (earlier
table) is a field for commonly used docs, again, how to deal with uncommon
variable docs???****
DateRequested
DateReceived (you could use a Y/N field here, rather than D/T. see the notes
in tblContractInspections regarding this choice.)


Tina, thank you for responding to this post, I really do thank you and
everyone responding for your generosity and kindness. Perhaps you can review
my reply to John of the description of the job to better understand the goal
(I know when I posted way back when the decription was not that good) and
then reply to my specific *Q's* listed here?

Really appreciate it,

tjr

 

[tjr]

Edward, thank you for your empathy. Unfortunately, I have removed all the
other hats i wear for the company from the database already. Would it help to
see an example of 2 of the different schedules? I can copy and paste the
text I have in one note for 2 of the schedules. the first is a categorized
schedule and the second is a % to complete schedule:

1st Payment-
Slab Poured

2nd Payment-
Building dried in

3rd Payment-
Tubs set
Windows and/or window frames installed
Rough Heating/AC
Rough Electrical and Plumbing Trim out
Interior Walls ready for covering

4th Payment-
Exterior and Interior walls complete (except painting)
Roof Complete
All windows and exterior doors in place
Tile work complete
Cabinet work installed
Interior trim installed and ready for paint

5th Payment-
Building substantially complete and accepted by owner

NOW HERES THE % COMPLETE VERSION...

Plumbing roughed in - 2 %
Foundation Slab poured - 12 %
Walls framed - 10 %
Roof Framed / Sheathing - 4 %
Exterior wall sheathing - 1%
Roof Shingles - 4 %
Windows/Doors/Garage - 3 %
Plumbing/Top out - 3 %
Cornice & Soffit - 2 %
HVAC-Ducts Installed - 4 %
Wiring rough in - 2 %
Insulation (wall & ceiling) - 1 %
Brick-Fireplace/siding - 8 %
Exterior Paint Complete - 1 %
Sheetrock - 4 %
Tape and float - 1 %
Interior Trim/Interior Doors installed - 5 %
Cabinets - 4 %
Ceramic Tile - 2 %
Interior Paint - 4 %
Formica/countertops - .5 %
Vanity tops - .5 %
Furnished flooring: BR/LVRM, FAMRM, Kitchen/Utility, Bath/Entry 6 - %
Hardware & Accessories - 1 %
Light fixtures - 2 %
Wallpaper - 1 %
Appliances - 2 %
Plumbing fixtures - 2 %
Mirrors/Shower Doors - 1 %
AC Compressor - 3 %
Drives/walks - 3 %
Landscaping - 1 %

I deal with approximately 30 different lenders each with different
schedules. Some lenders offer both a categorized version and a % complete
version, but only 1 gets used per build.

I hope this helps. Thank you for your post and again for your empathy.
Thanks again,

tjr

 

[tjr]

Tina and or John,

maybe this will help as well...

Edward responded and i replied with a better description of a sampling of
the schedules that I deal with. He eluded to not being able to help so I
thought I might copy the descriptions here so that you can see them too, He
recommended that I remove some stuff but, unfortunately, I have removed all
the other hats i wear for the company from the database already. Would it
help to see an example of 2 of the different schedules? I can copy and paste
the text I have in one note for 2 of the schedules. the first is a
categorized schedule and the second is a % to complete schedule:

1st Payment-
Slab Poured

2nd Payment-
Building dried in

3rd Payment-
Tubs set
Windows and/or window frames installed
Rough Heating/AC
Rough Electrical and Plumbing Trim out
Interior Walls ready for covering

4th Payment-
Exterior and Interior walls complete (except painting)
Roof Complete
All windows and exterior doors in place
Tile work complete
Cabinet work installed
Interior trim installed and ready for paint

5th Payment-
Building substantially complete and accepted by owner

NOW HERES THE % COMPLETE VERSION...

Plumbing roughed in - 2 %
Foundation Slab poured - 12 %
Walls framed - 10 %
Roof Framed / Sheathing - 4 %
Exterior wall sheathing - 1%
Roof Shingles - 4 %
Windows/Doors/Garage - 3 %
Plumbing/Top out - 3 %
Cornice & Soffit - 2 %
HVAC-Ducts Installed - 4 %
Wiring rough in - 2 %
Insulation (wall & ceiling) - 1 %
Brick-Fireplace/siding - 8 %
Exterior Paint Complete - 1 %
Sheetrock - 4 %
Tape and float - 1 %
Interior Trim/Interior Doors installed - 5 %
Cabinets - 4 %
Ceramic Tile - 2 %
Interior Paint - 4 %
Formica/countertops - .5 %
Vanity tops - .5 %
Furnished flooring: BR/LVRM, FAMRM, Kitchen/Utility, Bath/Entry 6 - %
Hardware & Accessories - 1 %
Light fixtures - 2 %
Wallpaper - 1 %
Appliances - 2 %
Plumbing fixtures - 2 %
Mirrors/Shower Doors - 1 %
AC Compressor - 3 %
Drives/walks - 3 %
Landscaping - 1 %

I deal with approximately 30 different lenders each with different
schedules. Some lenders offer both a categorized version and a % complete
version, but only 1 gets used per build.

 

[tina]

well, chances are that i wouldn't have seen your post anyway, so no worries
there; i've been in the NGs very seldom the last couple months (no time) so
it's just coincidence i came across this post. and i doubt that John
would've minded reference to the prior thread; usually the MVPs appreciate
all the information they can get when trying to help somebody - saves them
time.

actually, anyone who posts in this NG is batting 5000 if they get a response
from John Vinson, or Lynn Trapp, or Tim Ferguson - so hopefully John will
continue to assist you in this thread. i'll keep an eye on it, and give the
whole thing some more thought too, and if i have something useful to post in
the next day or so, i will.

 

[tina]

comments inline.

You recommended certain table arrangements which I would like to clarify.

tblDrawSchedules
DrawSchedID (pk, Autonumber)
LenderID (fk from tblLenders, Long Integer)

***Q: do you think this table is where I should 'store a template' of the
draw/disbursement sch. in field with a combination of comment and/or
checkboxes to indicate perhaps if the lender only qualified a portion of the
% allowed or other comments and a yes/no to indicate paid/not paid, etc.
OR: Did you have something else in mind for this table?****

your explanation of draw schedules elsewhere in *this* thread was "each
lenders disbursement schedules are different and sometimes they have more
than one, however, only 1 draw schedule from that lender will apply
throughout construction".

i'm reading that as saying the each lender has one or more disbursement
schedules they use in financing development projects; but for each house
being built, only one specific draw schedule is used by the lender for that
project.

if that's correct, then my original comments about tblDrawSchedules still
applies: the table should include "whatever fields describe a specific draw
schedule used by a specific lender. the table must *not* contain any fields
about specific clients, or specific project disbursements.) this table just
describes each disbursement schedule used by each lender.

then, you mentioned 2 alt. tables if there was a sharing of a schedule which
there is not. However, since i am not employing the use of the
tblLenderDrawSchedules
LDSchedID (pk, Autonumber)
LenderID (fk from tblLenders, Long Integer)
DrawSchedID (fk from tblDrawSchedules, Long Integer)
(this is a linking table between lenders and draw schedules.

****which was one of the linking tables you designed, I am confused because
later in tblcontracts (below) you mentioned one of the fields used in the
alt table that I do not need so which field would I substitute here?****

tblContracts
ContractID (pk, Autonumber)
ClientID (fk from tblClients, Long Integer)
SiteID (fk from tblJobSites, Long Integer)
LDSchedID (fk from tblLenderDrawSchedules, Long Integer) **** Here. Would
I substitute: field drawscheduleid from tbldrawschedules?*****

correct, use tblDrawSchedules.DrawSchedID.

ContractAmount

next...

you mentioned tbldrawdocuments
docid (pk autonum)
doc name

****what in your design's eye was this table holding. simply a doc name -
since there are standard docs amongst all contracts that must be provided to
ea. lender however, there are some non-standard docs, lender specific docs,
docs to owners, etc - where would I store these docs? In this table,

too?****

yes, i would probably list all draw documents in this table. it might be
helpful to add a comments field where you can note that a particular
document is lender-specific, or is to-owner, or whatever.

now... tbl drawsteps
stepid (pk autonum)
stepname

****Q: perhaps i was wrong in my first table mentioned in this post that you
designed. Are you seeing me have the individual steps in each draw here?
This table confuses me a bit.

taking your explanation elsewhere in *this* thread: "Everything from
the concrete pad to the fixtures is accounted for however it is grouped
usually into general categories so there are only an average of 25-30 'line
items' per draw or disbursement schedule that we can request to be paid for
".
you probably have a lot of homes built on a concrete pad, correct? so that's
a common item, and so are most of the other items. so this table is just a
list of steps, any of which might be required for any particular draw of any
particular contract. examine the list of relationships i posted in Nov, and
you'll see
tblDrawSteps 1:n tblContractDrawSteps
so one draw step may be used in many different contract draws.

a few more questions about your
previous table design ideas...

tblContractDraws
ConDrawID (pk, autonumber)
ConDrawNumber (i'm assuming that a specific draw is just a number in the
draw schedule, such as draw "3" in a seven-draw schedule)
ConDrawDate
ConDrawAmount
note: in this table, each draw for each contact would be listed once. so a
contract with seven draws would have seven records, and a contract with five
draws would have five records, etc.

tblContractDrawSteps -
******OH! I think I am closer to understanding your design Ideas. This
clears up my earlier misconception regarding the table draw steps mentioned
earlier. This is where the steps of the disbursment are listed. Does this
structure mean that i have to type the line items each time for each contract
or would a field be named "any of the possible draw steps" and get marked
somehow as complete here???? - Ok, sorry maybe I am still confused!!!

you would populate the draw step field of each record from tblDrawSteps - so
no, you don't have to type the whole thing out each time. just use a combo
box in the data entry form to list all the draw steps, and choose the
appropriate one for each record you enter.

********

DrawStepID (pk, Autonumber)
ConDrawID (fk from tblContractDraws, Long Integer)
StepID (fk from tblDrawSteps)
DrawStepDateCompleted
note: in this table, each step of each draw would be listed once. so a
specific contract draw with eight steps would have eight records, and a
contract draw with ten steps would have ten records, etc.
further note: ContractID is not included as a foreign key, because each
contract draw is already identified with a specific contract.

tblContractDrawDocuments
DrawDocID (pk, Autonumber)
ConDrawID (fk from tblContractDraws, Long Integer)
DocID (fk from tblDrawDocuments, Long Integer) * ***so draw docs (earlier
table) is a field for commonly used docs, again, how to deal with uncommon
variable docs???****

list them in the same table, as i noted above.

DateRequested
DateReceived (you could use a Y/N field here, rather than D/T. see the notes
in tblContractInspections regarding this choice.)


Tina, thank you for responding to this post, I really do thank you and
everyone responding for your generosity and kindness. Perhaps you can review
my reply to John of the description of the job to better understand the goal
(I know when I posted way back when the decription was not that good) and
then reply to my specific *Q's* listed here?

well, you're welcome for the help. frankly, i have to agree with Edward's
assessment; this seems to me to be a mission-critical project for your
business, and especially if you're "growing by leaps and bounds" i don't
think you can afford to *not* invest in either tested-and-proven software,
or the services of an experienced Access developement contractor to build
the custom database you want. you obviously don't yet have a firm grasp of
normalization principles - which is not knocking you - and not surprising,
because it's a difficult subject for most people to grasp, taking a lot of
study and practice, and practical experience, to get a firm understanding of
even standard table/relationship designs. but to plunge into a
mission-critical project from where you're currently standing is very risky,
and relying on the advice and suggestions of people (including me, and
including MVPs) in the newsgroups, who are not in a position to do a
thorough analysis of your business process, is carrying "risky" to extremes,
in my opinion.

however, it's your business and your decision. i hope my responses to your
questions have helped somewhat, and i do wish you the best of luck with your
project.

tina

 

[mnature]

Sometimes it helps to see examples of tables when you are starting with a new
database. Here are some links:

Data Models (whole list):

http://www.databaseanswers.org/data_models/index.htm

Contract Management Data Model:

http://www.databaseanswers.org/data_models/contract_management/index.htm

Construction and Manufacturing Data Model:

http://www.databaseanswers.org/data_models/construction_and_manufacturing/index.htm

It is best to concentrate on getting your tables set up correctly. Once
that is done, creating the queries/forms/reports that you need will be fairly
easy. For instance, your lists of work done (such as slab poured, building
dried in, tubs set, etc.) would probably be one table:

tbl_Work
WorkID (primary key)
WorkDescription

You would let WorkID be an autonumber that self-generates, and each record
would be a separate work that can be performed, such as slab poured, building
dried in, tubs set. You would then use the primary key from this table as a
foreign key in another table, to reference dates performed, bank agreements,
schedules, etc.

 

[mnature]

I googled for "software construction management." There were a lot of hits.
This one looked promising:

http://www.smartcontractor.com/