Form design problem

J

Jack Sheet

Rehi all

I am trying to create a table of repeating tasks (called "T Repeating
Tasks") required to be performed for clients.
There will be no primary key in this table (probably not relevant to the
problem, but I mention it just in case).
I intend to use a form to input these tasks.
The list of possible clients for whom the tasks are to be performed is
contained in another table ("T Clients") within the same database.
There is a primary key (client reference) in the table "T Clients".

When creating a new record in "T Repeating Tasks" it seems sensible first to
identify the client to whom the task applies, and I would expect to do this
from a drop-down list of options derived from the "T Clients" table. I
would like to give the user the choice of dropping down the Client Reference
or Client Name. Whichever method the user adopts, I would like the other
corresponding field (reference or name) to be displayed in the form (as a
cross-check by inspection), although only the Client Reference would be
entered in the "T Repeating Tasks" table.

I have no idea how to go about this, although I have created more simple
forms. This is the first "real" database that I have tried to create, so
apologies if it sounds obvious. Any suggestions would be appreciated. May
not be necessary to describe in depth, a general pointer and I may be able
to find the rest in help.
 
T

Tom Wickerath

Hi Jack,
There will be no primary key in this table (probably not relevant to the
problem, but I mention it just in case).
Click to expand...
Pretty much all tables should have a primary key (PK). I prefer using the
autonumber data type. I recommend creating a new field in this table, say
pkTaskID. I also recommend not including any spaces or special characters in
anything that you assign a name to. A better name for "T Repeating Tasks"
might be "tblRepeatingTasks" and for "T Clients" you might want to use
"tblClients".

What are the relationships between these repeating tasks and your clients?
It sounds like you have two one-to-many (1:M) relationships which, taken
together would form a many-to-many (M:N) relationship, ie.

1:M A client can have many tasks and
1:M A task can apply to many clients

If both of these statements are true, then you have a M:N relationship. This
is handled with a third table that serves as a junction or join table, and
holds the foreign key (ie. the "many" side) of each 1:M relationship. Of
course, you will definately need a primary key in the repeating tasks table
in order to set up any kind of "one" side relationship with another table.

The first thing to do is to identify the relationships, in English (or the
language of your choice) that describe the situation. You can use such
phrases to help drive the database design.

Here is some reading material for you:

Database Design
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

http://support.microsoft.com/?id=289533


Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

Using a Naming Conventio
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp


Reserved Words
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Rehi all

I am trying to create a table of repeating tasks (called "T Repeating
Tasks") required to be performed for clients.
There will be no primary key in this table (probably not relevant to the
problem, but I mention it just in case).
I intend to use a form to input these tasks.
The list of possible clients for whom the tasks are to be performed is
contained in another table ("T Clients") within the same database.
There is a primary key (client reference) in the table "T Clients".

When creating a new record in "T Repeating Tasks" it seems sensible first to
identify the client to whom the task applies, and I would expect to do this
from a drop-down list of options derived from the "T Clients" table. I
would like to give the user the choice of dropping down the Client Reference
or Client Name. Whichever method the user adopts, I would like the other
corresponding field (reference or name) to be displayed in the form (as a
cross-check by inspection), although only the Client Reference would be
entered in the "T Repeating Tasks" table.

I have no idea how to go about this, although I have created more simple
forms. This is the first "real" database that I have tried to create, so
apologies if it sounds obvious. Any suggestions would be appreciated. May
not be necessary to describe in depth, a general pointer and I may be able
to find the rest in help.
 
J

Jack Sheet

Thanks, Tom.

You are right, it seems to be a M:M relationship. There are indeed a number
of common tasks that are undertaken for many clients, and I was intending to
list the task types in a separate table.

I don't understand the need to create a primary key in each table, but I
shall read up on the links that you have provided, thank you, which will
hopefully explain it.
 
T

Tom Wickerath

Hi Jack,

Each of the database design links that I provided explains the use of a
primary key. Here is another paper on this topic:

Database Normalization Tips (by Luke Chung)
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

and here's a link to Jeff Conrad's supermarket of links, which includes one
for "Database Design 101":

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Thanks, Tom.

You are right, it seems to be a M:M relationship. There are indeed a number
of common tasks that are undertaken for many clients, and I was intending to
list the task types in a separate table.

I don't understand the need to create a primary key in each table, but I
shall read up on the links that you have provided, thank you, which will
hopefully explain it.
__________________________________________


Hi Jack,
There will be no primary key in this table (probably not relevant to the
problem, but I mention it just in case).
Click to expand...
Pretty much all tables should have a primary key (PK). I prefer using the
autonumber data type. I recommend creating a new field in this table, say
pkTaskID. I also recommend not including any spaces or special characters in
anything that you assign a name to. A better name for "T Repeating Tasks"
might be "tblRepeatingTasks" and for "T Clients" you might want to use
"tblClients".

What are the relationships between these repeating tasks and your clients?
It sounds like you have two one-to-many (1:M) relationships which, taken
together would form a many-to-many (M:N) relationship, ie.

1:M A client can have many tasks and
1:M A task can apply to many clients

If both of these statements are true, then you have a M:N relationship. This
is handled with a third table that serves as a junction or join table, and
holds the foreign key (ie. the "many" side) of each 1:M relationship. Of
course, you will definately need a primary key in the repeating tasks table
in order to set up any kind of "one" side relationship with another table.

The first thing to do is to identify the relationships, in English (or the
language of your choice) that describe the situation. You can use such
phrases to help drive the database design.

Here is some reading material for you:

Database Design
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

http://support.microsoft.com/?id=289533


Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

Using a Naming Convention
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp


Reserved Words
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Rehi all

I am trying to create a table of repeating tasks (called "T Repeating
Tasks") required to be performed for clients.
There will be no primary key in this table (probably not relevant to the
problem, but I mention it just in case).
I intend to use a form to input these tasks.
The list of possible clients for whom the tasks are to be performed is
contained in another table ("T Clients") within the same database.
There is a primary key (client reference) in the table "T Clients".

When creating a new record in "T Repeating Tasks" it seems sensible first to
identify the client to whom the task applies, and I would expect to do this
from a drop-down list of options derived from the "T Clients" table. I
would like to give the user the choice of dropping down the Client Reference
or Client Name. Whichever method the user adopts, I would like the other
corresponding field (reference or name) to be displayed in the form (as a
cross-check by inspection), although only the Client Reference would be
entered in the "T Repeating Tasks" table.

I have no idea how to go about this, although I have created more simple
forms. This is the first "real" database that I have tried to create, so
apologies if it sounds obvious. Any suggestions would be appreciated. May
not be necessary to describe in depth, a general pointer and I may be able
to find the rest in help.
 
J

Jack Sheet

That's fine, Tom, thanks.

I have no problem with creating key fields. As I see it the very worst that
can happen is that I might increase the file size for a purpose that I have
yet to imagine (and fields can be removed later if desired). In the
meantime I have no doubt that the light will dawn later in the design
process.

Once again, thanks for all the links.
 
Top