Planning a table to avoid adding fields

[Eric D. Braden]

The Exposition -- I am currently planning out a new Access 2007
database. I have already built a prototype, but have started over to
accomodate changing needs. I am a relative amateur, with some
experience but not much in-depth Access work (this is my first venture
into 2007). I have what seems like a really simple question that I'm
having trouble wrapping my head around. DB planning and efficient
table relationships are sometimes like advanced physics to me.

The Question -- The dbase is a usage tracking system. Part of it will
keep count of how many learners of a particular type came in for each
exercise.

For instance, "3 doctors, 4 medical students, and 7 nurses came in for
life support training."

Is there a way to store this information without having a seperate
field in the exercise table for each learner type? That seems
ungainly to me.

I am trying to think of an efficient way to allow the end user to add
additional learner types, and it seems like adding new fields to
tables would be excessive.

Thank you for any assisance,

Eric B.

 

[Arvin Meyer [MVP]]

Build a tblLearnerType:

LearnerTypeID
LearnerType

then add LearnerTypeID to your person table.

 

[Eric D. Braden]

Thank you! If it was a snake...

I knew there was a simple solution staring me in the face.

 

[Eric D. Braden]

An additional beginner's question for any who feel like answering:

Why should I maintain an autonumber "ID" field? Wouldn't just having
one field "LearnerType" and setting it as the primary key be better,
as it would prevent duplicate learner types?

What is the "good practice" behind the ID field?

 

[Arvin Meyer [MVP]]

Because the ID field is what you'll use as a Primary Key in tblLearnerType
and a Foreign Key in any other linked table.

Perhaps it might be a good idea to get a basic understanding about a
relational database works. Have a look at this tutorial:

http://www.accessmvp.com/Strive4Peace/Index.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


An additional beginner's question for any who feel like answering:

Why should I maintain an autonumber "ID" field? Wouldn't just having
one field "LearnerType" and setting it as the primary key be better,
as it would prevent duplicate learner types?

What is the "good practice" behind the ID field?

 

[Keven Denen]

Because the ID field is what you'll use as a Primary Key in tblLearnerType
and a Foreign Key in any other linked table.

Perhaps it might be a good idea to get a basic understanding about a
relational database works. Have a look at this tutorial:

http://www.accessmvp.com/Strive4Peace/Index.htm
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

An additional beginner's question for any who feel like answering:

Why should I maintain an autonumber "ID" field?  Wouldn't just having
one field "LearnerType" and setting it as the primary key be better,
as it would prevent duplicate learner types?

What is the "good practice" behind the ID field?






- Show quoted text -

I think the OP's question goes more to "Why should I create an
additional field when I can just use the LearnerType to link the
tables?" From the sounds of it, this table is going to act simply as a
lookup for a drop-down on a form. I see a lot of databases that use
tables with one field to serve this purpose, with no ID column.

Essentially this comes down to the arguement over natural vs.
surrogate keys. A natural key is one that uses some natural piece of
information as your primary/foreign keys (like a social security
number or your LearnerType). A surrogate key is a value that has no
real world meaning (like the Autonumber data type in Access). If you
go with one column in your LearnerType table you've created a natural
key. That key has real world meaning.

The problem I have with natural keys is this; what happens when the
rules that define your natural key change (the government adds a 10th
number to the SSN)? What happens when you need to change the
LearnerType from "Doctor" to "Medical Professional"? Now you need to
go back in and make sure that all the records in any related table get
updated with the change. If you use a surrogate key like Arvin
suggests, you would simply update the LearnerType, the LearnerTypeID
would never need to change and you don't need to worry about updating
any foreign keys in the related table.

Keven

 

[Arvin Meyer [MVP]]

The only tables that ever correctly and successfully use a natural key are
very small and static text values like a state abbreviation.

In addition to what you've said below, anything over 2 characters should use
a long integer as a key whenever possible. The reason is that 2 characters
use 4 bytes, as does a long integer. There are only about 65k possibilities
using 4 bytes of text, while there are over 4 billion using a signed long
integer. More than 2 characters is not as efficient because it uses more
bytes. Also, never use a lookup field, I've out lined the reasons here:

http://www.mvps.org/access/lookupfields.htm

For various reasons, Access has methods and datatypes which do not scale to
other systems. I suggest that you avoid using them for several reasons.
First to maintain compatibility with every DBMS, and second to assure that
your data will conform to relational database methodology as devised by Date
and Codd.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



I think the OP's question goes more to "Why should I create an
additional field when I can just use the LearnerType to link the
tables?" From the sounds of it, this table is going to act simply as a
lookup for a drop-down on a form. I see a lot of databases that use
tables with one field to serve this purpose, with no ID column.

Essentially this comes down to the arguement over natural vs.
surrogate keys. A natural key is one that uses some natural piece of
information as your primary/foreign keys (like a social security
number or your LearnerType). A surrogate key is a value that has no
real world meaning (like the Autonumber data type in Access). If you
go with one column in your LearnerType table you've created a natural
key. That key has real world meaning.

The problem I have with natural keys is this; what happens when the
rules that define your natural key change (the government adds a 10th
number to the SSN)? What happens when you need to change the
LearnerType from "Doctor" to "Medical Professional"? Now you need to
go back in and make sure that all the records in any related table get
updated with the change. If you use a surrogate key like Arvin
suggests, you would simply update the LearnerType, the LearnerTypeID
would never need to change and you don't need to worry about updating
any foreign keys in the related table.

Keven

 

[Keven Denen]

The only tables that ever correctly and successfully use a natural key are
very small and static text values like a state abbreviation.

In addition to what you've said below, anything over 2 characters should use
a long integer as a key whenever possible. The reason is that 2 characters
use 4 bytes, as does a long integer. There are only about 65k possibilities
using 4 bytes of text, while there are over 4 billion using a signed long
integer. More than 2 characters is not as efficient because it uses more
bytes. Also, never use a lookup field, I've out lined the reasons here:

http://www.mvps.org/access/lookupfields.htm

For various reasons, Access has methods and datatypes which do not scale to
other systems. I suggest that you avoid using them for several reasons.
First to maintain compatibility with every DBMS, and second to assure that
your data will conform to relational database methodology as devised by Date
and Codd.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com



I think the OP's question goes more to "Why should I create an
additional field when I can just use the LearnerType to link the
tables?" From the sounds of it, this table is going to act simply as a
lookup for a drop-down on a form. I see a lot of databases that use
tables with one field to serve this purpose, with no ID column.

Essentially this comes down to the arguement over natural vs.
surrogate keys. A natural key is one that uses some natural piece of
information as your primary/foreign keys (like a social security
number or your LearnerType). A surrogate key is a value that has no
real world meaning (like the Autonumber data type in Access). If you
go with one column in your LearnerType table you've created a natural
key. That key has real world meaning.

The problem I have with natural keys is this; what happens when the
rules that define your natural key change (the government adds a 10th
number to the SSN)? What happens when you need to change the
LearnerType from "Doctor" to "Medical Professional"? Now you need to
go back in and make sure that all the records in any related table get
updated with the change. If you use a surrogate key like Arvin
suggests, you would simply update the LearnerType, the LearnerTypeID
would never need to change and you don't need to worry about updating
any foreign keys in the related table.

Keven

When I said lookup, I didn't mean the Lookup field type in Access, I
know that is ond of the Seven Deadly Sins. I just meant it in the
general sense, as in the drop down on the form uses it to lookup what
values to use. Sorry for the confusion.

Keven

 

[Eric D. Braden]

Thank you for all the sound advice. The natural vs surrogate key was,
in fact, my question. Nice to have that cleared up!

Also, thanks for the tip about lookup fields. I've been abusing them
so far, not realizing their disadvantages. The link below was useful,
but left me a little in the dark on an alternative. For anyone else
reading who is curious, the simple (and obvious, I guess) alternative
is to put the "lookup" action on whatever form you are using to
populate the table, not on the table itself. Quoting Pat Hartmen from
a thread at http://www.access-programmers.co.uk/forums/archive/index.php/t-115567.html:
--
Let me summarize. Lookups at the table level are bad. Lookups on forms
are good. In addition to what gemma mentioned, the real problems arise
once you start to write queries or vba. It is never clear whether you
need to use the numeric ID or the text description and some things
just don't work until you remove the lookup at the table level.
Captions can also cause problems with queries and VBA since you can't
get rid of them by aliasing a field and again once you get into
queries and VBA, they are more trouble than they're worth. At best
they save a couple of seconds when creating forms and reports. They
are no inconvienence to the user because the user NEVER looks at
tables or queries so your properly formatted names should not confuse
him.

So, yes you should be creating proper relational tables. Yes you
should be storing the ID rather than the text field. Yes you should
use lookups on your forms. You just shouldn't use lookups at the table
level. This is a minor inconvienence to you as a developer. If you
want to see the text value as well as the numeric value, you'll need
to create a query to join to the lookup table rather than just opening
the main table. It is transparent to the user because the user should
NEVER be looking at tables or queries, he should be looking only at
forms and reports.
--

The caption comment is new to me, so I'll be doing a little Googling
on that one.


Thanks for all your help,

Eric

 

[robson]

"Keven Denen" <[email protected]> escreveu na mensagem

The only tables that ever correctly and successfully use a natural key are
very small and static text values like a state abbreviation.

In addition to what you've said below, anything over 2 characters should
use
a long integer as a key whenever possible. The reason is that 2 characters
use 4 bytes, as does a long integer. There are only about 65k
possibilities
using 4 bytes of text, while there are over 4 billion using a signed long
integer. More than 2 characters is not as efficient because it uses more
bytes. Also, never use a lookup field, I've out lined the reasons here:

http://www.mvps.org/access/lookupfields.htm

For various reasons, Access has methods and datatypes which do not scale
to
other systems. I suggest that you avoid using them for several reasons.
First to maintain compatibility with every DBMS, and second to assure that
your data will conform to relational database methodology as devised by
Date
and Codd.
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com



I think the OP's question goes more to "Why should I create an
additional field when I can just use the LearnerType to link the
tables?" From the sounds of it, this table is going to act simply as a
lookup for a drop-down on a form. I see a lot of databases that use
tables with one field to serve this purpose, with no ID column.

Essentially this comes down to the arguement over natural vs.
surrogate keys. A natural key is one that uses some natural piece of
information as your primary/foreign keys (like a social security
number or your LearnerType). A surrogate key is a value that has no
real world meaning (like the Autonumber data type in Access). If you
go with one column in your LearnerType table you've created a natural
key. That key has real world meaning.

The problem I have with natural keys is this; what happens when the
rules that define your natural key change (the government adds a 10th
number to the SSN)? What happens when you need to change the
LearnerType from "Doctor" to "Medical Professional"? Now you need to
go back in and make sure that all the records in any related table get
updated with the change. If you use a surrogate key like Arvin
suggests, you would simply update the LearnerType, the LearnerTypeID
would never need to change and you don't need to worry about updating
any foreign keys in the related table.

Keven

When I said lookup, I didn't mean the Lookup field type in Access, I
know that is ond of the Seven Deadly Sins. I just meant it in the
general sense, as in the drop down on the form uses it to lookup what
values to use. Sorry for the confusion.

Keven

 

[Arvin Meyer [MVP]]

Lookups are not bad. Lookups in tables are bad. Instead of a table Lookup,
build another table with the Long Integer (usually an autonumber) and the
descriptive text. Use that Long Integer Primary Key as a Foreign Key in the
table. Now all you need is a query as a row source and a combo to get the
very same thing in a form.

It us a bad habit anyway to work directly in tables. Even the head DBA of a
major Fortune 500 company doesn't directly work in tables. All of their work
is done using Stored Procedures and Views. For Access, we should be using
forms, mostly because we can write code in forms to validate data, and we
cannot in tables.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Thank you for all the sound advice. The natural vs surrogate key was,
in fact, my question. Nice to have that cleared up!

Also, thanks for the tip about lookup fields. I've been abusing them
so far, not realizing their disadvantages. The link below was useful,
but left me a little in the dark on an alternative. For anyone else
reading who is curious, the simple (and obvious, I guess) alternative
is to put the "lookup" action on whatever form you are using to
populate the table, not on the table itself. Quoting Pat Hartmen from
a thread at
http://www.access-programmers.co.uk/forums/archive/index.php/t-115567.html:
--
Let me summarize. Lookups at the table level are bad. Lookups on forms
are good. In addition to what gemma mentioned, the real problems arise
once you start to write queries or vba. It is never clear whether you
need to use the numeric ID or the text description and some things
just don't work until you remove the lookup at the table level.
Captions can also cause problems with queries and VBA since you can't
get rid of them by aliasing a field and again once you get into
queries and VBA, they are more trouble than they're worth. At best
they save a couple of seconds when creating forms and reports. They
are no inconvienence to the user because the user NEVER looks at
tables or queries so your properly formatted names should not confuse
him.

So, yes you should be creating proper relational tables. Yes you
should be storing the ID rather than the text field. Yes you should
use lookups on your forms. You just shouldn't use lookups at the table
level. This is a minor inconvienence to you as a developer. If you
want to see the text value as well as the numeric value, you'll need
to create a query to join to the lookup table rather than just opening
the main table. It is transparent to the user because the user should
NEVER be looking at tables or queries, he should be looking only at
forms and reports.
--

The caption comment is new to me, so I'll be doing a little Googling
on that one.


Thanks for all your help,

Eric

 

[Eric D. Braden]

I've been searching most of the morning, and I have yet to find an
answer this, so pardon me for asking yet another question.

The discussion below led me to eliminate lookup fields in my tables.
I now have an additional table set up to link them. I now have a
listbox on my form with it's row source set as a query which pulls
from tblModality (SELECT [tblModality].[ModID], [tblModality].
[Modality] FROM tblModality; ). I have the Multi Select property set
to Simple so the user can select more than one modality for each
case.

I am having trouble figuring out how to transfer the selections from
the listbox into my new linking table (tblCaseMod). The selections
would go in the ModID field, and hopefully I can figure out how to get
it to just set the CaseID field to whatever the current case is on the
form. Is it possible to do it without VBA? If it's only possible
through VBA, I guess now is as good a time as any to start learning!
Am I just wrong-headed and going about it in the wrong way?

Here my tables:

tblCases
CaseID (PK)
CaseName
DateCreated
-many other descriptive fields-

tblModality
ModID (PK)
ModType

tblCaseMod
CaseModID (PK)
CaseID (FK)
ModID (FK)

Thank you again,

Eric B

 

[Arvin Meyer [MVP]]

So now you are violating a database normalization rule, that's actually a
fairly serious violation. That being do not store multiple values in a
single field for 1 record. You should use a combo box instead and have
multiple rows of data, instead of a single row with multiple values in 1
field.

When designing and using databases, think long rather than wide. More rows,
less fields. More rows, 1 field.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I've been searching most of the morning, and I have yet to find an
answer this, so pardon me for asking yet another question.

The discussion below led me to eliminate lookup fields in my tables.
I now have an additional table set up to link them. I now have a
listbox on my form with it's row source set as a query which pulls
from tblModality (SELECT [tblModality].[ModID], [tblModality].
[Modality] FROM tblModality; ). I have the Multi Select property set
to Simple so the user can select more than one modality for each
case.

I am having trouble figuring out how to transfer the selections from
the listbox into my new linking table (tblCaseMod). The selections
would go in the ModID field, and hopefully I can figure out how to get
it to just set the CaseID field to whatever the current case is on the
form. Is it possible to do it without VBA? If it's only possible
through VBA, I guess now is as good a time as any to start learning!
Am I just wrong-headed and going about it in the wrong way?

Here my tables:

tblCases
CaseID (PK)
CaseName
DateCreated
-many other descriptive fields-

tblModality
ModID (PK)
ModType

tblCaseMod
CaseModID (PK)
CaseID (FK)
ModID (FK)

Thank you again,

Eric B

 

[Eric D. Braden]

I think I may be misunderstanding (or I just didn't explain
correctly). I *think* I'm doing the right thing. In attempting to
avoid multiple values in one field, I created the tblCaseMod table.
My idea is that it would store a linkage that in practical terms
basically said "Case #32 uses Modalities 3, 4 and 5", but would take 3
records to do it.

A corresponding example set of records in tblCaseMod would have the
following values:

CaseModID - 3 (automatically generated PK)
CaseID - 32 (FK from tblCases)
ModID - 3 (FK from tlbModality)

CaseModID - 4
CaseID - 32
ModID - 4

CaseModID - 5
CaseID - 32
ModID - 5

*If* that is correct methodology, I'm having issues coming up with a
way to get the input out of a "New Case" or "Edit Existing Case" form
into the joined table.

I'm trying very hard to think in terms of normalization, but I know
the concept slips by me pretty often. I was under the impression that
using a joining table like this was in the spirit of normalization,
instead of having something like a bunch of Yes/No fields in my
tblCases table for each modality (leading to a situation in which I
would have to do a bunch of work to add or change a modality).

If these questions are too elementary, let me know. I'd like to sit
down and take a course or read for several hours about normalization
theory, but since I'm learning as I'm creating this for work, I don't
really have that option and just take in snippets at a time. This is
a brute-force, on-the-job, self-taught process. I truly enjoy it, but
some days it can be mighty frustrating.


Eric B.

So now you are violating a database normalization rule, that's actually a
fairly serious violation. That being do not store multiple values in a
single field for 1 record. You should use a combo box instead and have
multiple rows of data, instead of a single row with multiple values in 1
field.

When designing and using databases, think long rather than wide. More rows,
less fields. More rows, 1 field.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I've been searching most of the morning, and I have yet to find an
answer this, so pardon me for asking yet another question.

The discussion below led me to eliminate lookup fields in my tables.
I now have an additional table set up to link them.  I now have a
listbox on my form with it's row source set as a query which pulls
from tblModality (SELECT [tblModality].[ModID], [tblModality].
[Modality] FROM tblModality; ).  I have the Multi Select property set
to Simple so the user can select more than one modality for each
case.

I am having trouble figuring out how to transfer the selections from
the listbox into my new linking table (tblCaseMod).  The selections
would go in the ModID field, and hopefully I can figure out how to get
it to just set the CaseID field to whatever the current case is on the
form.  Is it possible to do it without VBA?  If it's only possible
through VBA, I guess now is as good a time as any to start learning!
Am I just wrong-headed and going about it in the wrong way?

Here my tables:

tblCases
CaseID (PK)
CaseName
DateCreated
-many other descriptive fields-

tblModality
ModID (PK)
ModType

tblCaseMod
CaseModID (PK)
CaseID (FK)
ModID (FK)

Thank you again,

Eric B

Lookups are not bad. Lookups in tables are bad. Instead of a table Lookup,
build another table with the Long Integer (usually an autonumber) and the
descriptive text. Use that Long Integer Primary Key as a Foreign Key in
the
table. Now all you need is a query as a row source and a combo to get the
very same thing in a form.

It us a bad habit anyway to work directly in tables. Even the head DBA of
a
major Fortune 500 company doesn't directly work in tables. All of their
work
is done using Stored Procedures and Views. For Access, we should be using
forms, mostly because we can write code in forms to validate data, and we
cannot in tables.

messageThank you for all the sound advice. The natural vs surrogate key was,
in fact, my question. Nice to have that cleared up!

Also, thanks for the tip about lookup fields. I've been abusing them
so far, not realizing their disadvantages. The link below was useful,
but left me a little in the dark on an alternative. For anyone else
reading who is curious, the simple (and obvious, I guess) alternative
is to put the "lookup" action on whatever form you are using to
populate the table, not on the table itself. Quoting Pat Hartmen from
a thread
athttp://www.access-programmers.co.uk/forums/archive/index.php/t-115567....
--
Let me summarize. Lookups at the table level are bad. Lookups on forms
are good. In addition to what gemma mentioned, the real problems arise
once you start to write queries or vba. It is never clear whether you
need to use the numeric ID or the text description and some things
just don't work until you remove the lookup at the table level.
Captions can also cause problems with queries and VBA since you can't
get rid of them by aliasing a field and again once you get into
queries and VBA, they are more trouble than they're worth. At best
they save a couple of seconds when creating forms and reports. They
are no inconvienence to the user because the user NEVER looks at
tables or queries so your properly formatted names should not confuse
him.

So, yes you should be creating proper relational tables. Yes you
should be storing the ID rather than the text field. Yes you should
use lookups on your forms. You just shouldn't use lookups at the table
level. This is a minor inconvienence to you as a developer. If you
want to see the text value as well as the numeric value, you'll need
to create a query to join to the lookup table rather than just opening
the main table. It is transparent to the user because the user should
NEVER be looking at tables or queries, he should be looking only at
forms and reports.
--

The caption comment is new to me, so I'll be doing a little Googling
on that one.

Thanks for all your help,

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Arvin Meyer [MVP]]

Yes, you would have 3 records as you show in your example.

A subform is usually used to add records in the many-side table. In your
case it appears that would be based on tblCaseMod.

You will get significant benefit out of Crystal Long's Video and PDF
tutorials at:

http://www.accessmvp.com/Strive4Peace/Index.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I think I may be misunderstanding (or I just didn't explain
correctly). I *think* I'm doing the right thing. In attempting to
avoid multiple values in one field, I created the tblCaseMod table.
My idea is that it would store a linkage that in practical terms
basically said "Case #32 uses Modalities 3, 4 and 5", but would take 3
records to do it.

A corresponding example set of records in tblCaseMod would have the
following values:

CaseModID - 3 (automatically generated PK)
CaseID - 32 (FK from tblCases)
ModID - 3 (FK from tlbModality)

CaseModID - 4
CaseID - 32
ModID - 4

CaseModID - 5
CaseID - 32
ModID - 5

*If* that is correct methodology, I'm having issues coming up with a
way to get the input out of a "New Case" or "Edit Existing Case" form
into the joined table.

I'm trying very hard to think in terms of normalization, but I know
the concept slips by me pretty often. I was under the impression that
using a joining table like this was in the spirit of normalization,
instead of having something like a bunch of Yes/No fields in my
tblCases table for each modality (leading to a situation in which I
would have to do a bunch of work to add or change a modality).

If these questions are too elementary, let me know. I'd like to sit
down and take a course or read for several hours about normalization
theory, but since I'm learning as I'm creating this for work, I don't
really have that option and just take in snippets at a time. This is
a brute-force, on-the-job, self-taught process. I truly enjoy it, but
some days it can be mighty frustrating.


Eric B.

So now you are violating a database normalization rule, that's actually a
fairly serious violation. That being do not store multiple values in a
single field for 1 record. You should use a combo box instead and have
multiple rows of data, instead of a single row with multiple values in 1
field.

When designing and using databases, think long rather than wide. More
rows,
less fields. More rows, 1 field.
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

messageI've been searching most of the morning, and I have yet to find an
answer this, so pardon me for asking yet another question.

The discussion below led me to eliminate lookup fields in my tables.
I now have an additional table set up to link them. I now have a
listbox on my form with it's row source set as a query which pulls
from tblModality (SELECT [tblModality].[ModID], [tblModality].
[Modality] FROM tblModality; ). I have the Multi Select property set
to Simple so the user can select more than one modality for each
case.

I am having trouble figuring out how to transfer the selections from
the listbox into my new linking table (tblCaseMod). The selections
would go in the ModID field, and hopefully I can figure out how to get
it to just set the CaseID field to whatever the current case is on the
form. Is it possible to do it without VBA? If it's only possible
through VBA, I guess now is as good a time as any to start learning!
Am I just wrong-headed and going about it in the wrong way?

Here my tables:

tblCases
CaseID (PK)
CaseName
DateCreated
-many other descriptive fields-

tblModality
ModID (PK)
ModType

tblCaseMod
CaseModID (PK)
CaseID (FK)
ModID (FK)

Thank you again,

Eric B

Lookups are not bad. Lookups in tables are bad. Instead of a table
Lookup,
build another table with the Long Integer (usually an autonumber) and
the
descriptive text. Use that Long Integer Primary Key as a Foreign Key in
the
table. Now all you need is a query as a row source and a combo to get
the
very same thing in a form.

It us a bad habit anyway to work directly in tables. Even the head DBA
of
a
major Fortune 500 company doesn't directly work in tables. All of their
work
is done using Stored Procedures and Views. For Access, we should be
using
forms, mostly because we can write code in forms to validate data, and
we
cannot in tables.

messageThank you for all the sound advice. The natural vs surrogate key was,
in fact, my question. Nice to have that cleared up!

Also, thanks for the tip about lookup fields. I've been abusing them
so far, not realizing their disadvantages. The link below was useful,
but left me a little in the dark on an alternative. For anyone else
reading who is curious, the simple (and obvious, I guess) alternative
is to put the "lookup" action on whatever form you are using to
populate the table, not on the table itself. Quoting Pat Hartmen from
a thread
athttp://www.access-programmers.co.uk/forums/archive/index.php/t-115567...
--
Let me summarize. Lookups at the table level are bad. Lookups on forms
are good. In addition to what gemma mentioned, the real problems arise
once you start to write queries or vba. It is never clear whether you
need to use the numeric ID or the text description and some things
just don't work until you remove the lookup at the table level.
Captions can also cause problems with queries and VBA since you can't
get rid of them by aliasing a field and again once you get into
queries and VBA, they are more trouble than they're worth. At best
they save a couple of seconds when creating forms and reports. They
are no inconvienence to the user because the user NEVER looks at
tables or queries so your properly formatted names should not confuse
him.

So, yes you should be creating proper relational tables. Yes you
should be storing the ID rather than the text field. Yes you should
use lookups on your forms. You just shouldn't use lookups at the table
level. This is a minor inconvienence to you as a developer. If you
want to see the text value as well as the numeric value, you'll need
to create a query to join to the lookup table rather than just opening
the main table. It is transparent to the user because the user should
NEVER be looking at tables or queries, he should be looking only at
forms and reports.
--

The caption comment is new to me, so I'll be doing a little Googling
on that one.

Thanks for all your help,

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Eric D. Braden]

A followup to this, in case it might help anyone else:

I checked out Crystal Long's site (along with many many many others)
and Arvin Meyer's advice, and I ended up using a continuous subform
with just a combo box on it to populate the joining table. Obvious I
know! I turned off record selectors, nav buttons, and scrollbars to
help it "sink in" to the main form visually, and wrote code to keep it
sized correctly and move other elements around in relation to it. The
code I used to size it is below:

'mainFrm - Me if on main form. Me.Parent if on subform.
'mainName - Name of main form in "quotes".
'subName - Name of sub form in "quotes".
'subWidth - Width of sub form.

Public Sub SubResize(mainFrm As Form, mainName As String, subName As
String, subWidth As Long)
If DCount(mainName & "ID", "t_" & mainName & subName, mainName & "ID =
Forms.f_" & mainName & "Edit." & mainName & "ID") > 2 Then 'Does
subform contain more than 2 records?
mainFrm("sub_" & mainName & subName).Height = 945 'Set height to 3
lines
mainFrm("sub_" & mainName & subName).Form.ScrollBars = 2 'Vertical
scrollbar on
mainFrm("sub_" & mainName & subName).Width = subWidth + 300 'Increase
subform width to accomodate scrollbar
Else
mainFrm("sub_" & mainName & subName).Height = (DCount(mainName & "ID",
"t_" & mainName & subName, mainName & "ID = Forms.f_" & mainName &
"Edit." & mainName & "ID") + 1) * 315 'Set height to appropriate
number of records
mainFrm("sub_" & mainName & subName).Form.ScrollBars = 0 'Scrollbars
off
mainFrm("sub_" & mainName & subName).Form.Width = subWidth 'Set
subform width without scrollbars
End If
End Sub


I'm working a good generic solution for relocating elements, but I
haven't come up with it yet. I'm currently using something like the
following wherever necessary:

Public Sub SessionRelocate(frm As Form)
frm.sub_SessionStaff.Top = frm.sub_SessionDate.Top +
frm.sub_SessionDate.Height + 120
frm.sub_SessionInstructor.Top = frm.sub_SessionStaff.Top +
frm.sub_SessionStaff.Height + 120
frm.sub_SessionLearner.Top = frm.sub_SessionInstructor.Top +
frm.sub_SessionInstructor.Height + 120
frm.sub_SessionSP.Top = frm.sub_SessionLearner.Top +
frm.sub_SessionLearner.Height + 120
frm.Comments.Top = frm.sub_SessionSP.Top + frm.sub_SessionSP.Height +
120

frm.sub_SessionStaffLabel.Top = frm.sub_SessionStaff.Top
frm.sub_SessionInstructorLabel.Top = frm.sub_SessionInstructor.Top
frm.sub_SessionLearnerLabel.Top = frm.sub_SessionLearner.Top
frm.sub_SessionSPLabel.Top = frm.sub_SessionSP.Top
frm.CommentsLabel.Top = frm.Comments.Top
End Sub

To make that into a generic function, I'm working on a solution for
figuring out which control (if any) is next on the form, so that I
don't have to know each control ahead of time. I figure I can then
put it into a loop with argument substitutions like the resizing
function.

Thanks for all your help, I'm learning a lot!

Eric Braden

Yes, you would have 3 records as you show in your example.

A subform is usually used to add records in the many-side table. In your
case it appears that would be based on tblCaseMod.

You will get significant benefit out of Crystal Long's Video and PDF
tutorials at:

http://www.accessmvp.com/Strive4Peace/Index.htm
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I think I may be misunderstanding (or I just didn't explain
correctly).  I *think* I'm doing the right thing.  In attempting to
avoid multiple values in one field, I created the tblCaseMod table.
My idea is that it would store a linkage that in practical terms
basically said "Case #32 uses Modalities 3, 4 and 5", but would take 3
records to do it.

A corresponding example set of records in tblCaseMod would have the
following values:

CaseModID - 3 (automatically generated PK)
CaseID - 32 (FK from tblCases)
ModID - 3 (FK from tlbModality)

CaseModID - 4
CaseID - 32
ModID - 4

CaseModID - 5
CaseID - 32
ModID - 5

*If* that is correct methodology, I'm having issues coming up with a
way to get the input out of a "New Case" or "Edit Existing Case" form
into the joined table.

I'm trying very hard to think in terms of normalization, but I know
the concept slips by me pretty often.  I was under the impression that
using a joining table like this was in the spirit of normalization,
instead of having something like a bunch of Yes/No fields in my
tblCases table for each modality (leading to a situation in which I
would have to do a bunch of work to add or change a modality).

If these questions are too elementary, let me know.  I'd like to sit
down and take a course or read for several hours about normalization
theory, but since I'm learning as I'm creating this for work, I don't
really have that option and just take in snippets at a time.  This is
a brute-force, on-the-job, self-taught process.  I truly enjoy it, but
some days it can be mighty frustrating.

Eric B.

So now you are violating a database normalization rule, that's actuallya
fairly serious violation. That being do not store multiple values in a
single field for 1 record. You should use a combo box instead and have
multiple rows of data, instead of a single row with multiple values in 1
field.

When designing and using databases, think long rather than wide. More
rows,
less fields. More rows, 1 field.

messageI've been searching most of the morning, and I have yet to find an
answer this, so pardon me for asking yet another question.

The discussion below led me to eliminate lookup fields in my tables.
I now have an additional table set up to link them. I now have a
listbox on my form with it's row source set as a query which pulls
from tblModality (SELECT [tblModality].[ModID], [tblModality].
[Modality] FROM tblModality; ). I have the Multi Select property set
to Simple so the user can select more than one modality for each
case.

I am having trouble figuring out how to transfer the selections from
the listbox into my new linking table (tblCaseMod). The selections
would go in the ModID field, and hopefully I can figure out how to get
it to just set the CaseID field to whatever the current case is on the
form. Is it possible to do it without VBA? If it's only possible
through VBA, I guess now is as good a time as any to start learning!
Am I just wrong-headed and going about it in the wrong way?

Here my tables:

tblCases
CaseID (PK)
CaseName
DateCreated
-many other descriptive fields-

tblModality
ModID (PK)
ModType

tblCaseMod
CaseModID (PK)
CaseID (FK)
ModID (FK)

Thank you again,

Eric B

...

read more »- Hide quoted text -

- Show quoted text -