Entering data in new tables & want to 'see' related data

[Guest]

Hello. I am designing my first robust (for new user) db in Access 2003. I
have spent alot of time reading books on access and have re-designed my
tables according to good advice of Tina (replied to previous posts). I am
about to start entering data into newly designed tables and how I am going to
be able to "see" related data while entering other data boggles my mind so
fresh into this design. I am hoping for some advice on ways to ease my fears
of hours of wasted time entering 'garbage'.

I am designing a database to track the houses we sell and all that goes
along with the builder receiving payments (called draws) throughout a
construction project, that which is related to how the client pays
(mortgages, etc & all the contacts that go along with that) and also the
selling price and other certain property information, in addition to
insurances and inspections, etc. and also the most difficult and also the
most important thing to track is how the lender pays us for the construction
process (5-7 pyts spread out over time) and each lender can have any one of
many 'payment schedules' that we must adhere to and track % of completion
amount paid to date amount of each payment, etc, etc,...

I have the following tables and will only list table name and primary and
foreign key to save time and space (hope ok)...

tblClients
ClientID (pk, autonum)

tblClientPhones <Q: is this this where I would enter the # itself, which
field?>
PhoneID (pk, autonum)
PhoneTypeID (fk from tblPhones, # long int)

tblPhoneTypes <Q: Would this be where the type of connection fax ph or
mobile, etc.>
PhoneTypeID (pk, autonum)

tblJobSites
SiteID (pk, autonum)

tblLenders <Q: Should make a seperate lenders branches tbl to track each
branch that I deal with or keep all in Lenders tbl. Just afraid of dual
entry, etc.>
LenderID (pk, autonum)

tblLenderRepresentatives <Q: I think this is a linking table?>
RepID (pk, autonum)
LenderID (fk from tblLenders, # long int)

tblLenderRepresentatives
RepresentativePhoneID (pk, autonum)
RepresentativeID (fk from tblPhoneTypes, # long int)
PhoneTypeID (fk from tblPhoneTypes, # long int)

tblRepresentativeContacts
ContactID (pk, autonum)
RepresentativeID (fk from tblLenderRepresentatives, # long int)
ClientID (fk from tblClients, # long int)

tblDrawSchedules <Q: Do I enter all criterion imposed by bank here or as it
happens during construction, but I cant get paid till certain construction
criteria are met (can I avoid somehow retyping the criterion for every Lender
when they use a particular schedule? Please see also the tblDrawSteps below,
this Q may be more applicable there??>
DrawScheduleID (pk, autonum)
LenderID (fk from tblLenders, # long int)

tblLendersDrawSchedules - linking table (Lenders/DrawSchedules)
LDScheduleID (pk, autonum)
LenderID fk from tblLenders, # long int)
DrawScheduleID (fk from tblDrawSchedules, # long int)

tblContracts
ContractID (pk, autonum)
ClientID (fk from tblClients, # long int)
SiteID (fk from tblJobSite, # long int)
LDScheduleID (fk from tblLenderDrawSchedule, # long int)

tblInspectionTypes
InspectionTypeID (pk, autonum)

tlbInspections
InspectionID (pk, autonum)
InspectionTypeID fk from tblInspectionTypes, # long int)

tblContractInspections
ContractInspectionID (pk, autonum)
ContractID (fk from tblContracts, # long int)
InspectionID (fk from tblInspections, # long int)

tblDrawDocuments
DocumentID (pk, autonum)
DocumentName (added other important field name-for explanation later?)

tblDrawSteps
StepID (pk, autonum)
StepDescription

tblContractDraws
ContractDrawID (pk, autonum)

tblContractDrawSteps
DrawStepID (pk, autonum)
ContractDrawID (fk from tblContractDraws, # long int)
StepID (fk from tblDrawSteps)

tblContractDrawDocuments
DrawDocumentID (pk, autonum)
ContractDrawID (fk from tblContractDraws, # long int)
DocumentID (fk from tblDrawDocuments, # long int)


When I am entering data to track the payments for a particular client's
house what is the best way for me to be sure that I am entering data for John
Smith's house on 123 Elm Street?

Should I use a look up field everywhere in the child tables that looks up
the clients last name and house number to 'bring in' the related data
<(HOW?)> so that I as the designer and also the end user do not have to
additionally remember that John Smith is autonumber field 147, while his
property on 123 Elm Street is JobSite autonumber 83 and the lender is
autonumber 229 and the first payment or draw is autonumber 71 and what about
remembering the autonumber fields for all property related inspections and
milestone dates and documents that are required to be submitted, etc. etc.
the list goes on and on.

I am paranoid about wasting time entering data if all I can do is have no
choice but to remember a bunch of autonumber fields. (no real primary keys in
any tables...)

I would appreciate some input on how exactly to accomplish relating to
client name and property address instead of autonumber fields and also how is
the best way to set up the draw schedule tracking system (the table structure
has to be so complex due to the number of variables on any given house)

Please help me if you can, I would really like to keep a very cool new job
that I love (how often does that happen?) and need to be able to accurately
track all of the variable and amounts. Please also note specific confusion
issues marked with <Q:> after tlb names above.

Any input even general guidance would be greatly appreciated. Thank you so
much for your time, I enjoy reading the posts, thank you!

Teri

 

[Vincent Johns]

I hope that Tina and others are not discouraged from answering you as
well, but I have some comments that I hope will help.

Hello. I am designing my first robust (for new user) db in Access 2003. I
have spent alot of time reading books on access and have re-designed my
tables according to good advice of Tina (replied to previous posts). I am
about to start entering data into newly designed tables and how I am going to
be able to "see" related data while entering other data boggles my mind so
fresh into this design. I am hoping for some advice on ways to ease my fears
of hours of wasted time entering 'garbage'.

I am designing a database to track the houses we sell and all that goes
along with the builder receiving payments (called draws) throughout a
construction project, that which is related to how the client pays
(mortgages, etc & all the contacts that go along with that) and also the
selling price and other certain property information, in addition to
insurances and inspections, etc. and also the most difficult and also the
most important thing to track is how the lender pays us for the construction
process (5-7 pyts spread out over time) and each lender can have any one of
many 'payment schedules' that we must adhere to and track % of completion
amount paid to date amount of each payment, etc, etc,...

I have the following tables and will only list table name and primary and
foreign key to save time and space (hope ok)...

tblClients
ClientID (pk, autonum)

tblClientPhones <Q: is this this where I would enter the # itself, which
field?>
PhoneID (pk, autonum)
PhoneTypeID (fk from tblPhones, # long int)

Yes, you could include a field here for phone number, and maybe another
field indicating what kind of phone it is (emergency, cell, desk,
supervisor, secretary, evening, etc.), that you might use for deciding
which number to call under varying circumstances. You might also want
some indication of what time of day each phone is available, but maybe
that's detail that you don't need to store in your database.

Since you did not define [tblPhones] in your list here, this may be a
typo... but if it's a real Table, maybe you're planning to store numbers
and types there. That would allow multiple phones to be attached to a
[tblClientPhones] record, but then I'm not sure why you'd need several
[tblClientPhones] records attached to a single client. (I notice that
you do NOT show any attachment to [tblClients], so perhaps I don't know
what you want to do with this Table.)

tblPhoneTypes <Q: Would this be where the type of connection fax ph or
mobile, etc.>
PhoneTypeID (pk, autonum)

You probably want that info to be somewhere, but I'd put it wherever you
keep the phone numbers. I see little need to separate a phone number
from the type of phone it is.

Do you intend this Table to hold generic information of some kind about
faxes, as opposed to home phones, or to cell phones? If so, this kind
of Table could be useful, but I'd expect to see it used mostly by a
communications company. I'm not sure why you'd need it.

tblJobSites
SiteID (pk, autonum)

tblLenders <Q: Should make a seperate lenders branches tbl to track each
branch that I deal with or keep all in Lenders tbl. Just afraid of dual
entry, etc.>
LenderID (pk, autonum)

This makes sense if you need contact information (e.g., phone number)
for each of several branches, and also need information on the lender's
main office for the branch.

tblLenderRepresentatives <Q: I think this is a linking table?>
RepID (pk, autonum)
LenderID (fk from tblLenders, # long int)

Yes, if (as I infer) you can have some representatives who can represent
more than one lender. If each representative is employed exclusively by
one lender, you don't need this.

tblLenderRepresentatives
RepresentativePhoneID (pk, autonum)
RepresentativeID (fk from tblPhoneTypes, # long int)
PhoneTypeID (fk from tblPhoneTypes, # long int)

I think you should try to avoid giving two Tables the same name, even in
a design document. (Access won't let you do it in a database, but email
messages don't do any such checking.) Try to give them names that
concisely reflect what they contain.

Incidentally, to try to keep things as simple as I can, I normally give
each Table's primary key a name that matches that of the Table itself,
but with "ID" or "_ID" attached to the end. I use the same name for
fields in other Tables that refer to it (those other fields would be
"foreign keys", but with the same name). This makes it easy to
determine if the links attach to the proper fields, and to determine
which Table I want to add when I'm designing a Query. It's obvious just
by looking at the key name.

tblRepresentativeContacts
ContactID (pk, autonum)
RepresentativeID (fk from tblLenderRepresentatives, # long int)
ClientID (fk from tblClients, # long int)

I suggest putting contact information for a generic human being (stuff
like phone #s, pets' names, address) into one Table, and letting your
tables that need personal information all link to the one Table. You
would likely have this kind of information for both lender
representatives and clients, and maybe employees. Fields specific to
one class or another of these people could be kept in a separate Table,
with a link to the personal contact information.

tblDrawSchedules <Q: Do I enter all criterion imposed by bank here or as it
happens during construction, but I cant get paid till certain construction
criteria are met (can I avoid somehow retyping the criterion for every Lender
when they use a particular schedule? Please see also the tblDrawSteps below,
this Q may be more applicable there??>
DrawScheduleID (pk, autonum)
LenderID (fk from tblLenders, # long int)

I suggest putting all schedules that you know about into a Table
describing schedules. Even if you have a contract whose terms don't fit
a defined schedule, you can put those terms here, too. Maybe only one
contract will ever use certain of these records. Big deal. It won't
take up lots of space, and having related fields located in this Table
will probably make auditing easier. You might even, by perusing it,
notice anomalies (mistakes?) in some contracts that don't match the
general pattern. If the Table is too large, it would be hard to notice
anything like that.

tblLendersDrawSchedules - linking table (Lenders/DrawSchedules)
LDScheduleID (pk, autonum)
LenderID fk from tblLenders, # long int)
DrawScheduleID (fk from tblDrawSchedules, # long int)

tblContracts
ContractID (pk, autonum)
ClientID (fk from tblClients, # long int)
SiteID (fk from tblJobSite, # long int)
LDScheduleID (fk from tblLenderDrawSchedule, # long int)

tblInspectionTypes
InspectionTypeID (pk, autonum)

tlbInspections
InspectionID (pk, autonum)
InspectionTypeID fk from tblInspectionTypes, # long int)

tblContractInspections
ContractInspectionID (pk, autonum)
ContractID (fk from tblContracts, # long int)
InspectionID (fk from tblInspections, # long int)

tblDrawDocuments
DocumentID (pk, autonum)
DocumentName (added other important field name-for explanation later?)

Don't worry too much about adding fields later. Access is flexible.
You want to plan well, but if you later discover you've omitted
something, you won't have to start over. Also, if you discover that you
don't need a field you've included, you can (with care) delete
no-longer-needed fields.

tblDrawSteps
StepID (pk, autonum)
StepDescription

tblContractDraws
ContractDrawID (pk, autonum)

tblContractDrawSteps
DrawStepID (pk, autonum)
ContractDrawID (fk from tblContractDraws, # long int)
StepID (fk from tblDrawSteps)

tblContractDrawDocuments
DrawDocumentID (pk, autonum)
ContractDrawID (fk from tblContractDraws, # long int)
DocumentID (fk from tblDrawDocuments, # long int)


When I am entering data to track the payments for a particular client's
house what is the best way for me to be sure that I am entering data for John
Smith's house on 123 Elm Street?

Should I use a look up field everywhere in the child tables that looks up
the clients last name and house number to 'bring in' the related data
<(HOW?)> so that I as the designer and also the end user do not have to
additionally remember that John Smith is autonumber field 147, while his
property on 123 Elm Street is JobSite autonumber 83 and the lender is
autonumber 229 and the first payment or draw is autonumber 71 and what about
remembering the autonumber fields for all property related inspections and
milestone dates and documents that are required to be submitted, etc. etc.
the list goes on and on.

That's certainly what I'd do. (Some people on this newsgroup dislike
lookup fields, but I think this feature is just about indispensable for
foreign keys, for the reasons you mention. But you can look around in
the newsgroup for competing opinions. You do need to be aware that the
value displayed, in such cases, isn't what's stored in the foreign-key
field; the actual key value is what's stored there.)

What I normally do is a 2-step process: (1) define a Lookup Query for
each primary key, which for me is normally a random Autonumber field,
and (2) set lookup properties on all foreign keys referring to it.

You don't need a lookup property on a primary key (and Access wisely
won't let you put one there).

Here's an example. Suppose you have Tables of names and phone numbers,
like these, in which [Phones].[Persons_ID] is linked to
[Persons].[Persons_ID]:

[Persons]

Persons_ID First Name Last Name
----------- ---------- ---------
-1082011867 Mary Smith
1078524438 John Jones

[Phones]

Phones_ID Number Type Persons_ID
----------- -------- --------- -----------
1197868596 555-1882 home 1078524438
-1898431013 555-2993 fax -1082011867
1963178463 555-6997 cell 1078524438
439134082 732-1234 Desk -1082011867
-749362816 911 Emergency -1082011867

(I usually use random Autonumbers for my keys, as I did here, to help me
avoid mistaking them for other data. Access doesn't care.) Now, isn't
this ugly? You can't tell by looking only at [Phones] whose phone
555-1882 is.

Now I define a Lookup Query (prefixing the Table name with "QL_") with
only 2 fields, the primary key and something meaningful:

[QL_Persons] SQL:

SELECT [Persons].[Persons_ID],
Left$([Persons]![First Name],8) & " "
& [Persons]![Last Name] AS Name
FROM Persons
ORDER BY [Persons].[Last Name],
[Persons].[First Name];

In this case, the meaningful stuff is the first 8 characters of the
person's first name, followed by the entire last name. This needs to be
unique, otherwise users may have trouble distinguishing records. You
might need to include a couple of digits from the key to make it unique.

[QL_Persons] Query Datasheet view:
Persons_ID Name
----------- -----------
1078524438 John Jones
-1082011867 Mary Smith

OK, now I have my Lookup Query defined. You might notice (well, not
here, because I didn't choose the names well, but you can try it
yourself) that the first name is listed first, but the last name is
sorted first. So Ann Smith would also be listed after John Jones but
before Mary Smith. You have lots of freedom in choosing what your users
will see here.

Second step (having defined your Lookup Query for the primary key) is to
set lookup properties on the foreign keys. In Table [Phones], where
[Phones].[Persons_ID] is a foreign key linking to [Persons], we open
Table Design View, select the field, and select the Lookup tab. I
change some properties as follows:

Display Control: List Box (instead of the default Text Box)
Row Source: QL_Persons
Column Count: 2
Column Widths: 0;1

Since all of my Lookup Queries begin with the same 2 fields (key and
lookup value), the only one of these properties which changes from one
foreign key to another is which Row Source to use. You don't even have
to type it; you can select it from a drop-down list.

NOW, when we switch to Table Datasheet view, things look better:

[Phones] Table Datasheet view:

Phones_ID Number Type Persons_ID
----------- -------- --------- ----------
1197868596 555-1882 home John Jones
-1898431013 555-2993 fax Mary Smith
1963178463 555-6997 cell John Jones
439134082 732-1234 Desk Mary Smith
-749362816 911 Emergency Mary Smith

The primary key is still ugly, but you can hide that (I usually do) via
Format --> Hide Columns. (You can bring it back later, if you need to,
via Format --> Unhide Columns.) For now, it's unhidden, so you can see
what's going on.

Now, in a Query, with the lookup property enabled, you get your choice
of which you want to see: lookup value, raw key, or both. For example,
let's just look at names and phone numbers:

[Q_PhoneAndName] SQL:

SELECT Phones.Number, Phones.Persons_ID
FROM Phones
ORDER BY Phones.Number;

We get a usable list this way, showing each person's (abbreviated) name.

[Q_PhoneAndName] Query Datasheet view:

Number Persons_ID
--------- ----------
555-1882 John Jones
555-2993 Mary Smith
555-6997 John Jones
732-1234 Mary Smith
911 Mary Smith

But if we, for some weird reason, really want to see the raw key value,
it's still available (but we have to explicitly add the linked Table to
the Query Design View in this case):

[Q_PhoneAndKey] SQL:

SELECT Phones.Number, Phones.Persons_ID,
Persons.Persons_ID
FROM Persons INNER JOIN Phones
ON Persons.Persons_ID = Phones.Persons_ID
ORDER BY Phones.Number;

Now we get, in addition to the names, the raw keys:

[Q_PhoneAndKey] Query Datasheet view:

Number Phones.Persons_ID Persons.Persons_ID
-------- ----------------- ------------------
555-1882 John Jones 1078524438
555-2993 Mary Smith -1082011867
555-6997 John Jones 1078524438
732-1234 Mary Smith -1082011867
911 Mary Smith -1082011867

I am paranoid about wasting time entering data if all I can do is have no
choice but to remember a bunch of autonumber fields. (no real primary keys in
any tables...)

With good reason. (What is a "real" primary key? Any unique index can
be used as a primary key, though I usually prefer random Autonumbers.)
Although I don't recommend letting your users enter data directly into
Tables or Queries -- too much chance of ruining good information stored
there -- you can probably do that for initial testing.

As I said, not only shouldn't you have to remember raw key field values,
I think you shouldn't even have to look at them.

Your should define Forms for data entry by your users. Forms can allow
you to protect fields and records from accidental changes.

I would appreciate some input on how exactly to accomplish relating to
client name and property address instead of autonumber fields and also how is
the best way to set up the draw schedule tracking system (the table structure
has to be so complex due to the number of variables on any given house)

I'm afraid I need to pass on your draw schedule tracking system design.
(It's late, and I need to work on another database.) But using a
consistent naming scheme, and setting lookup properties on your foreign
keys, should help some. I think you already know about avoiding
"repeating groups" and about normalizing your Tables.

Please help me if you can, I would really like to keep a very cool new job
that I love (how often does that happen?) and need to be able to accurately
track all of the variable and amounts. Please also note specific confusion
issues marked with <Q:> after tlb names above.

Any input even general guidance would be greatly appreciated. Thank you so
much for your time, I enjoy reading the posts, thank you!

Teri

Good luck.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Vincent Johns wrote:
[...]

Any input even general guidance would be greatly appreciated. Thank
you so much for your time, I enjoy reading the posts, thank you! [...]

Teri

Something else that I forgot to mention in my previous posting was
"subdatasheets", which you can look up in Access Help. They can save
you a lot of time in populating and browsing your Tables or in using
Queries.

But again, I'd recommend them for your own use. I think you should
replace them later with subforms for your users to use for data entry.
(Forms and subforms give you more control over what can be changed.)

For even better security, you could require all data entry to be done
via stored procedures (in Modules), but that's a lot of extra work and
is maybe not warranted. It depends on the risk. You might be better
off with a consistent, vigorous program of backing up your database
frequently and checking for mistakes.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

I thank you very much for your input and advice. I will try to utilize your
recommendations and see how it goes. I really appreciate the newsgroups
response to questions and love browsing the replies. Keep up the great work!
Thanks much Mr. Johns~Have a great Sunday I will post how I've made out and
will be sure to post any design or data entry questions that come up,
admitedly am very new to query building for the purpose of data entry!

Vincent Johns wrote:
[...]

Any input even general guidance would be greatly appreciated. Thank
you so much for your time, I enjoy reading the posts, thank you! [...]

Teri

Something else that I forgot to mention in my previous posting was
"subdatasheets", which you can look up in Access Help. They can save
you a lot of time in populating and browsing your Tables or in using
Queries.

But again, I'd recommend them for your own use. I think you should
replace them later with subforms for your users to use for data entry.
(Forms and subforms give you more control over what can be changed.)

For even better security, you could require all data entry to be done
via stored procedures (in Modules), but that's a lot of extra work and
is maybe not warranted. It depends on the risk. You might be better
off with a consistent, vigorous program of backing up your database
frequently and checking for mistakes.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

I thank you very much for your input and advice. I will try to utilize your
recommendations and see how it goes. I really appreciate the newsgroups
response to questions and love browsing the replies. Keep up the great work!
Thanks much Mr. Johns~Have a great Sunday I will post how I've made out and
will be sure to post any design or data entry questions that come up,
admitedly am very new to query building for the purpose of data entry!

Yes, I'd be happy to hear about your experiences (good or bad -- if bad,
maybe I can suggest something else).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.