Getting the Tables correct?

[Steve]

I'm trying to build my first Access db. We run a non profit transitional
housing program and need to collect informatiion simular to property
management companies. We have applications, waiting list, leases, etc. We
also have to collect a lot of demographic data about our tenants/clients. I
have created a Tenant table with just basic unchanging info about
tenant/clients (Name, SSNumber, DOB, Ethnicity, Etc.). I then created a
Tenancy table because many people try our program two or three times. It
holds information specific to each stay (unit number, income level, etc.).
I am trying to make life as simple as possible as far as data entry goes so
I'm making an application table to track applications after they are turned
in. My question finally is how can I enter the names into the Application
table and have them also appear in the Tenant table when they are accepted?
Would I rename the Tenant table and put everyone in it some would have
information in the Application table and some would have information in the
Tenant table?

One more thing. Is it "ok" to use the same lookup table for multiple
fields in the same table. Example. I have an Income Table with
EntryIncomeSource and ExitIncomeSource. I'd like to use the same lookup
table for both fields. Is this acceptable? Seems to work.

 

[John Vinson]

My question finally is how can I enter the names into the Application
table and have them also appear in the Tenant table when they are accepted?
Would I rename the Tenant table and put everyone in it some would have
information in the Application table and some would have information in the
Tenant table?

Well... DON'T.

An applicant is a person; a tenant is also a person (the same person
at a different time). For that matter, it appears that a person can be
an Applicant several times, with different application dates, etc.

I'd store biographical information in a People table, *and only in
that table*. The Application table should NOT have *ANY* people's
names in it - only the PersonID as a foreign key; similarly for the
Tenent table.

One more thing. Is it "ok" to use the same lookup table for multiple
fields in the same table. Example. I have an Income Table with
EntryIncomeSource and ExitIncomeSource. I'd like to use the same lookup
table for both fields. Is this acceptable? Seems to work.

It's not - in my opinion - acceptable to use the Lookup Field
misfeature AT ALL. (Well, John Viescas can use it if he wants to but
then he's written a bunch of books about Access and knows how to avoid
its pitfalls).

In fact, you should never be looking at table datasheets except for
debugging.

Create Forms to interact with your data - e.g. a Form for People, with
a subform on it for Applications. You'll see the applications for that
person on the subform, and be able to add a new application. And you
can have two combo boxes ("lookups" if you will) on the Form, both
based on the Income table, but bound to EntryIncomeSource and
ExitIncomeSource.... *if* you want those two fields. What if the
person has three jobs? (not at all uncommon these days). Might you not
better have an IncomeSources table related one to many to the People
table?

 

[Steve]

Thanks for the valuable information. As you can see, I'm truly a beginner.
I understand what you mean about using a People table and treating the
Applications simular to Tenancies. I have read somewhere about lookup
tables being "poor design". Can you point me to more information about
this. The book I'm reading (Access 2002 Bible) suggests using "lookup
tables" or entering "lookup values" for a field to keep entries consistent.
I believe that there are pitfalls. I'd just like to know more about what
they are and how to avoid them.

Re: "can have two combo boxes ("lookups" if you will) on the Form, both

based on the Income table, but bound to EntryIncomeSource and
ExitIncomeSource.... *if* you want those two fields"

So what I'm understanding is that the form itself can contain lookup values
in a combo box? That would be great.

I have the Tenant table (soon to be called People) as the one side of a many
to one relationship with the Tenancy table. Then the Income table is on the
many side of a many to one relationship to the Tenancy table. My reasoning
was because a person can have multiple income sources which likely change
between tenancies. So far my reporting responsibilities require me to
report specific income information related to each Tenancy. Any ideas on
how I can set this up better. I could be required to report income
information about Applicants in the future. I'm a big believer in doing it
right the first time.

Thank you for all this great advice!
Steve

 

[Rolls]

There may be other unusual things happening with low income tenants, such as
multiple unrelated individuals with the same physical address and/or social
security number ;-).

You might want to structure your DB so that you can query this type of
exception and date the information received so that it can be tracked for
consistency over time.

 

[John Vinson]

Thanks for the valuable information. As you can see, I'm truly a beginner.
I understand what you mean about using a People table and treating the
Applications simular to Tenancies. I have read somewhere about lookup
tables being "poor design". Can you point me to more information about
this. The book I'm reading (Access 2002 Bible) suggests using "lookup
tables" or entering "lookup values" for a field to keep entries consistent.
I believe that there are pitfalls. I'd just like to know more about what
they are and how to avoid them.

There is nothing at all wrong with LOOKUP TABLES. They're universal,
every good database has them. What's been a real problem for a lot of
people is Microsoft's misguided decision to put LOOKUP FIELDS into the
Table definition window. Basically, what they do is conceal the actual
content and structure of your table from view, and make it harder -
not easier - to develop a quality application.

You will certainly want "lookup tables" such as (for example) a table
of Cities if your clients may live in different cities, or a table of
agencies if there are several funding agencies, etc. But they should
be used correctly - you can and should put combo boxes on Forms bound
to these lookup tables.

So what I'm understanding is that the form itself can contain lookup values
in a combo box? That would be great.

That was where combo boxes GOT STARTED! Putting them into Tables is a
recent innovation (and a step in the wrong direction in my opinion).

I have the Tenant table (soon to be called People) as the one side of a many
to one relationship with the Tenancy table. Then the Income table is on the
many side of a many to one relationship to the Tenancy table. My reasoning
was because a person can have multiple income sources which likely change
between tenancies. So far my reporting responsibilities require me to
report specific income information related to each Tenancy. Any ideas on
how I can set this up better. I could be required to report income
information about Applicants in the future. I'm a big believer in doing it
right the first time.

I'd be a bit queasy about linking Tenancy to Income. Rental agreements
don't get paid wages; people do. You could have some tenants who have
a steady job across multiple tenancies, and others who have several
jobs over the course of a single tenancy.

What you might consider instead is to use date ranges. That is, the
Tenancy and Employment tables should be related to People, but each
should contain StartDate and EndDate fields. It would be fairly easy
to construct a Query to find all employment periods which
chronologically overlap a tenancy, or vice versa, without the
ambiguity and possible error of linking a job directly to a tenancy.

Good luck... and I heartily applaud your determination to build a firm
foundation before you start elaborating the superstructure!

 

[Steve]

Fantastic information! I think I'm beginning to understand! That seems to
make much more sense. I was thinking about having other tables linked to
the Tenancy but the idea of using date ranges has made me step back and
think. Thanks much.
Steve

 

[Steve]

Makes sense. Thanks!

 

[Rolls]

That is what I was alluding to when I said "date the information received."

Certain types of information are only valid during a time interval, such as
a period of employment or tenancy. It's a design decision whether to
replace outdated info as it changes or to assign start/end dates to the
various intervals. SQL can handle the complexity very well with the right
table structure.

 

[Steve]

I'm wondering about keeping track of Families. They are certainly People.
But they are related to other People. I'm required to track information
about family members of our tenants (they don't always live together). In
this business the family composition changes nearly as often as income.
Would you recommend putting family members in a family table and using a
dating system the same as your recommendations regarding income or is there
a way to put them in the people table and still relate them to a "primary
tenant or client" and tenancy? Hope you guys can understand this question.
My brain is tired.

 

[Steve]

It seems like the design decision basically boils down to whether you wish
to maintain the history of information or just current/last known values.
Is this correct thinking? I'm running into an interesting situation when I
think about family members. In our business these change regularly. I have
to report demographic information about anyone the tenant considers to be a
spouse or an immediate family member during their tenancy. The next time
they try out our program they may not have any family members (at least not
as far as the agencies I have to report to are concerned). Because I need
to record a history of tenants/clients and their tenancies would you
recommend starting and ending dates for family members also?

 

[John Vinson]

I'm wondering about keeping track of Families. They are certainly People.
But they are related to other People. I'm required to track information
about family members of our tenants (they don't always live together). In
this business the family composition changes nearly as often as income.
Would you recommend putting family members in a family table and using a
dating system the same as your recommendations regarding income or is there
a way to put them in the people table and still relate them to a "primary
tenant or client" and tenancy? Hope you guys can understand this question.

Any time you start mixing table relationships with human relationships
things can get VERY snarky.

I've done this in the past in a couple of ways: one is to have a
Family table, related one-to-many to the People table. This works best
for stable households though, where the Family can be considered a
more or less concrete entity.

Another - more flexible, but harder to use in some ways - method is to
have an "Indirect Many to Many Self Join" - a fancy phrase which has a
fairly simple table structure. You'ld have your People table with
*all* people - the primary tenant and any family members; and a
Relationships table. This might have five fields:

Relationships
PrimaryPersonID <link to People table>
RelatedPersonID <also a link to People table>
Relationship <e.g. Spouse, whatever euphamism you're using for
'person of opposite sex sharing living quarters', Child, Mother,
Roommate, ...>
DateStarted <date this relationship started>
DateTerminated <date the lazy SOB finally left... oops! sorry! <g>>