Am I a sinner already?

[Richard Hare]

Hello All,
I'm just starting on my first database, it is for keeping track of
employees, vacancies etc. I just started with a table of biographical stuff,
then when I wanted to put in their grades, and locations, I made up 2
separate tables, each with just these single fields in and used a 'lookup'
from a combo box from the biog table. Now having just read these 10
commandments on the mvps website
http://www.mvps.org/access/tencommandments.htm I am concerned that I have
sinned! I am very new to all the language used in databases, so am I right
in thinking that I have already broken commandment 2 "Thou shalt never
allow thy users to see or edit tables directly, but only through forms and
thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One."
I beg forgiveness if this is the case, but if so, could someone advise on
what is a better way of entering the information I require.

Regards
Rich

 

[Ed Warren]

Go ye forth and sin no more!

You are really 'ok'. Here is guidance to enlightment.
You have 'People'
People have grades and locations (and frankly probably a lot more). You may
need to handle telephones and addresses and how that is done depends on if
each person is allowed to have one and only one telephone and address
(database normalization)

In the case at hand.

You most likely really want to handle the case where the person has many
grades (over time) so I will discuss that case.

TablePeople:

PeopleID (autonumber, Key), LastName, FirstName, etc.

TableGrades

GradeID (autonumber, Key), GradeDescription(text)

TablePeople_Grades

People_GradeID (autonumber, Key), PeopleID (number, FK), GradeID
(number,FK), Date (date/time)

---------------------------------------now we build the relationships in the
relationship view.

TablePeople 1 --> M TablePeople_Grades
TableGrades 1 --> M TablePeople_Grades

All this does is say that each person can have many People_Grades and each
Grade can have several people assigned to it.

-----------------------------------------Now to the forms
(there are form wizards that build a reasonable form for you)

Build FormPeople to enter the data for the people
e.g.

Text box for LastName
Text box for FirstName
etc. for the rest of the fields

Build a Form for entering the data for the Grades
GradeID and Grade Description

Build a form for entering the data for the Relationship PeopleToGrades

drop down list for Person
drop down list for Grade
text field for Date

build a query to lookup person. Note set the sort order under display as
ascending
lkpPerson:: select PersonID, [lastname] & ", " & [firstname] as display from
TablePeople; (this will produce a "view" with

PersonID Display
1 Adams, Joe
2 Jones, Jane

build a query to lookup grade
lkpGrade:: selectGradeID,GradeDescription from TableGrade;

finally use these as the source of the lookup fields
set the 'bound' column to 1, number of columns to 2, column width to 0;2

Now you have a form with two dropdown lists in which you see 'meaningful'
stuff (e.g. a name and description) but store only a number in the database.

We are near the journey!!

Now you can add the PeopleToGrades to the FormPeople as a subform, with the
child/parent relationship set to PeopleID
Hide the PeopleDrop dropdown by setting it's visible property to "no".

Now allow the user to open the form and enter data

They never see the tables, and all the complexity behind the relationships.
They see a form in which they enter the data in a meaningful manner (to
them)

You on the otherhand have the ability now to go back into the database and
make changes without touching all the data

example

the powers that BE!! come down and decree
All GradeA people are now GradeAA and we want a new grade added, GradeA.

Go to the formGrade, change the text from GradeA to GradeAA (add one A) -->
all the current GradeA's are now instantly GradeAA's (because they are
really stored as (5).
Add a new record GradeA and it now appears as a choice on your drop down
list. (value 10)

Pretend to faint over this mega-task, say it will take weeks to sort it all
out, then go fishing !!


Best of luck

Ed Warren

 

[Richard Hare]

Hey Ed

That's an awesome entree for me! Can't wait for the main course and
Bizarely of course I am a keen fisherman, so will tackle this megatask with
the procrastination it deserves, after the Easter break!
Just so I am sure of what you are talking about though,
'Key' refers to primary key and 'FK' is a foreign key? ( a primary key in
another table?)
(Sorry this really is babtism of fire for me!)

Many thanks for taking the time to steer me,

Regards
Rich

Go ye forth and sin no more!

You are really 'ok'. Here is guidance to enlightment.
You have 'People'
People have grades and locations (and frankly probably a lot more). You may
need to handle telephones and addresses and how that is done depends on if
each person is allowed to have one and only one telephone and address
(database normalization)

In the case at hand.

You most likely really want to handle the case where the person has many
grades (over time) so I will discuss that case.

TablePeople:

PeopleID (autonumber, Key), LastName, FirstName, etc.

TableGrades

GradeID (autonumber, Key), GradeDescription(text)

TablePeople_Grades

People_GradeID (autonumber, Key), PeopleID (number, FK), GradeID
(number,FK), Date (date/time)

---------------------------------------now we build the relationships in the
relationship view.

TablePeople 1 --> M TablePeople_Grades
TableGrades 1 --> M TablePeople_Grades

All this does is say that each person can have many People_Grades and each
Grade can have several people assigned to it.

-----------------------------------------Now to the forms
(there are form wizards that build a reasonable form for you)

Build FormPeople to enter the data for the people
e.g.

Text box for LastName
Text box for FirstName
etc. for the rest of the fields

Build a Form for entering the data for the Grades
GradeID and Grade Description

Build a form for entering the data for the Relationship PeopleToGrades

drop down list for Person
drop down list for Grade
text field for Date

build a query to lookup person. Note set the sort order under display as
ascending
lkpPerson:: select PersonID, [lastname] & ", " & [firstname] as display from
TablePeople; (this will produce a "view" with

PersonID Display
1 Adams, Joe
2 Jones, Jane

build a query to lookup grade
lkpGrade:: selectGradeID,GradeDescription from TableGrade;

finally use these as the source of the lookup fields
set the 'bound' column to 1, number of columns to 2, column width to 0;2

Now you have a form with two dropdown lists in which you see 'meaningful'
stuff (e.g. a name and description) but store only a number in the database.

We are near the journey!!

Now you can add the PeopleToGrades to the FormPeople as a subform, with the
child/parent relationship set to PeopleID
Hide the PeopleDrop dropdown by setting it's visible property to "no".

Now allow the user to open the form and enter data

They never see the tables, and all the complexity behind the relationships.
They see a form in which they enter the data in a meaningful manner (to
them)

You on the otherhand have the ability now to go back into the database and
make changes without touching all the data

example

the powers that BE!! come down and decree
All GradeA people are now GradeAA and we want a new grade added, GradeA.

Go to the formGrade, change the text from GradeA to GradeAA (add one A) -->
all the current GradeA's are now instantly GradeAA's (because they are
really stored as (5).
Add a new record GradeA and it now appears as a choice on your drop down
list. (value 10)

Pretend to faint over this mega-task, say it will take weeks to sort it all
out, then go fishing !!


Best of luck

Ed Warren

Hello All,
I'm just starting on my first database, it is for keeping track of
employees, vacancies etc. I just started with a table of biographical
stuff,
then when I wanted to put in their grades, and locations, I made up 2
separate tables, each with just these single fields in and used a 'lookup'
from a combo box from the biog table. Now having just read these 10
commandments on the mvps website
http://www.mvps.org/access/tencommandments.htm I am concerned that I have
sinned! I am very new to all the language used in databases, so am I
right
in thinking that I have already broken commandment 2 "Thou shalt never
allow thy users to see or edit tables directly, but only through forms and
thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One."
I beg forgiveness if this is the case, but if so, could someone advise
on
what is a better way of entering the information I require.

Regards
Rich

 

[Ed Warren]

Yes,
Key --> Primary Key
FK -> Foreign Key from a related table.

Enjoy the fishing,

(lots of really good stuff at the 'tencommandants' web site, we really owe a
debt of gratitude to those that worked to put it together for us)

Ed Warren

Hey Ed

That's an awesome entree for me! Can't wait for the main course and
Bizarely of course I am a keen fisherman, so will tackle this megatask
with
the procrastination it deserves, after the Easter break!
Just so I am sure of what you are talking about though,
'Key' refers to primary key and 'FK' is a foreign key? ( a primary key in
another table?)
(Sorry this really is babtism of fire for me!)

Many thanks for taking the time to steer me,

Regards
Rich

Go ye forth and sin no more!

You are really 'ok'. Here is guidance to enlightment.
You have 'People'
People have grades and locations (and frankly probably a lot more). You may
need to handle telephones and addresses and how that is done depends on
if
each person is allowed to have one and only one telephone and address
(database normalization)

In the case at hand.

You most likely really want to handle the case where the person has many
grades (over time) so I will discuss that case.

TablePeople:

PeopleID (autonumber, Key), LastName, FirstName, etc.

TableGrades

GradeID (autonumber, Key), GradeDescription(text)

TablePeople_Grades

People_GradeID (autonumber, Key), PeopleID (number, FK), GradeID
(number,FK), Date (date/time)

---------------------------------------now we build the relationships in the
relationship view.

TablePeople 1 --> M TablePeople_Grades
TableGrades 1 --> M TablePeople_Grades

All this does is say that each person can have many People_Grades and
each
Grade can have several people assigned to it.

-----------------------------------------Now to the forms
(there are form wizards that build a reasonable form for you)

Build FormPeople to enter the data for the people
e.g.

Text box for LastName
Text box for FirstName
etc. for the rest of the fields

Build a Form for entering the data for the Grades
GradeID and Grade Description

Build a form for entering the data for the Relationship PeopleToGrades

drop down list for Person
drop down list for Grade
text field for Date

build a query to lookup person. Note set the sort order under display as
ascending
lkpPerson:: select PersonID, [lastname] & ", " & [firstname] as display from
TablePeople; (this will produce a "view" with

PersonID Display
1 Adams, Joe
2 Jones, Jane

build a query to lookup grade
lkpGrade:: selectGradeID,GradeDescription from TableGrade;

finally use these as the source of the lookup fields
set the 'bound' column to 1, number of columns to 2, column width to 0;2

Now you have a form with two dropdown lists in which you see 'meaningful'
stuff (e.g. a name and description) but store only a number in the database.

We are near the journey!!

Now you can add the PeopleToGrades to the FormPeople as a subform, with the
child/parent relationship set to PeopleID
Hide the PeopleDrop dropdown by setting it's visible property to "no".

Now allow the user to open the form and enter data

They never see the tables, and all the complexity behind the relationships.
They see a form in which they enter the data in a meaningful manner (to
them)

You on the otherhand have the ability now to go back into the database
and
make changes without touching all the data

example

the powers that BE!! come down and decree
All GradeA people are now GradeAA and we want a new grade added, GradeA.

Go to the formGrade, change the text from GradeA to GradeAA (add one A) -->
all the current GradeA's are now instantly GradeAA's (because they are
really stored as (5).
Add a new record GradeA and it now appears as a choice on your drop down
list. (value 10)

Pretend to faint over this mega-task, say it will take weeks to sort it all
out, then go fishing !!


Best of luck

Ed Warren

Hello All,
I'm just starting on my first database, it is for keeping track of
employees, vacancies etc. I just started with a table of biographical
stuff,
then when I wanted to put in their grades, and locations, I made up 2
separate tables, each with just these single fields in and used a 'lookup'
from a combo box from the biog table. Now having just read these 10
commandments on the mvps website
http://www.mvps.org/access/tencommandments.htm I am concerned that I have
sinned! I am very new to all the language used in databases, so am I
right
in thinking that I have already broken commandment 2 "Thou shalt never
allow thy users to see or edit tables directly, but only through forms and
thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One."
I beg forgiveness if this is the case, but if so, could someone advise
on
what is a better way of entering the information I require.

Regards
Rich

 

[Lynn Trapp]

In spite of what Ed says, you have actually committed the unforgivable sin
and no amount of penance will set you free. ;-) JUST KIDDING. I would
recommend that you get your hands on a copy of Designing Effective Database
Systems by Rebecca M. Riordan. You will be glad you did.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Richard Hare]

Okay thanks Lynn,

I got another suggestion for the list of sins,

Thou shalt never allow the person who asked you to come up with a database
to see the advance possibilities of the application, least he/her begin
every conversation with the words "Wouldn't it be nice if it was able
to........etc etc etc!

Rich

In spite of what Ed says, you have actually committed the unforgivable sin

and no amount of penance will set you free. ;-) JUST KIDDING. I would

recommend that you get your hands on a copy of Designing Effective Database

Systems by Rebecca M. Riordan. You will be glad you did.


Lynn Trapp

MS Access MVP

Access Security: www.ltcomputerdesigns.com/Security.htm

 

[Lynn Trapp]

I got another suggestion for the list of sins,

Thou shalt never allow the person who asked you to come up with a database
to see the advance possibilities of the application, least he/her begin
every conversation with the words "Wouldn't it be nice if it was able
to........etc etc etc!

Or, better yet.... "Thou shalt never say to end users, 'The sky is the
limit.'"

My supervisor actually said that to a group of people we were developing
something for and I almost never managed to get through that project
alive...LOL

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Richard Hare]

Hmm...
I can see there is a definate benefit here to mastering the art of the
understatement. Just the little I've learned since agreeing to take on
this project has taught me that as with most things in life, it's never as
easy as it looks! Why would an occupational therapist suddenly think he can
become a database developer overnight? beats me!
Rich

Or, better yet.... "Thou shalt never say to end users, 'The sky is the

 

[Fred Boer]

Well, Lynn... maybe you should think of this as a compliment from your
supervisor... He obviously thinks you are capable of miracles!

Fred

 

[Tom Lake]

Why would an occupational therapist suddenly think he can

become a database developer overnight? beats me!
Rich

Sometimes the even if he doesn't know too much, the OT is more knowledgeable
than anyone else in the immediate vicinity. The French have a saying, "In
the land of the blind, the man with one eye is king."

Tom Lake

 

[Richard Hare]

Lol, nice thought, but from a myopic perspective there seems a lot of 20/20
vision around!

 

[Lynn Trapp]

Well, Lynn... maybe you should think of this as a compliment from your

supervisor... He obviously thinks you are capable of miracles!

Thanks Fred. If he thinks that then he has an overblown opinion of my
skills...LOL

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Lynn Trapp]

easy as it looks! Why would an occupational therapist suddenly think he

can
become a database developer overnight? beats me!

Well, I would much rather have an occupational therapist try to become a
database developer than for a manager to try it...<g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Richard Hare]

Hi Ed,
Don't know if you are still following this thread, it's late this side of
the pond and I'm about to through my hands in the air!
I've done my tables as suggested and am starting on the forms, can you
clarify what you mean about building a form for the 'Relationship
PeopletoGrades'? I'm just using the wizards, I'm not really sure where I ask
for the fields to put in it.
Maybe I need some sleep now!

Rich

Go ye forth and sin no more!

You are really 'ok'. Here is guidance to enlightment.
You have 'People'
People have grades and locations (and frankly probably a lot more). You may
need to handle telephones and addresses and how that is done depends on if
each person is allowed to have one and only one telephone and address
(database normalization)

In the case at hand.

You most likely really want to handle the case where the person has many
grades (over time) so I will discuss that case.

TablePeople:

PeopleID (autonumber, Key), LastName, FirstName, etc.

TableGrades

GradeID (autonumber, Key), GradeDescription(text)

TablePeople_Grades

People_GradeID (autonumber, Key), PeopleID (number, FK), GradeID
(number,FK), Date (date/time)

---------------------------------------now we build the relationships in the
relationship view.

TablePeople 1 --> M TablePeople_Grades
TableGrades 1 --> M TablePeople_Grades

All this does is say that each person can have many People_Grades and each
Grade can have several people assigned to it.

-----------------------------------------Now to the forms
(there are form wizards that build a reasonable form for you)

Build FormPeople to enter the data for the people
e.g.

Text box for LastName
Text box for FirstName
etc. for the rest of the fields

Build a Form for entering the data for the Grades
GradeID and Grade Description

Build a form for entering the data for the Relationship PeopleToGrades

drop down list for Person
drop down list for Grade
text field for Date

build a query to lookup person. Note set the sort order under display as
ascending
lkpPerson:: select PersonID, [lastname] & ", " & [firstname] as display from
TablePeople; (this will produce a "view" with

PersonID Display
1 Adams, Joe
2 Jones, Jane

build a query to lookup grade
lkpGrade:: selectGradeID,GradeDescription from TableGrade;

finally use these as the source of the lookup fields
set the 'bound' column to 1, number of columns to 2, column width to 0;2

Now you have a form with two dropdown lists in which you see 'meaningful'
stuff (e.g. a name and description) but store only a number in the database.

We are near the journey!!

Now you can add the PeopleToGrades to the FormPeople as a subform, with the
child/parent relationship set to PeopleID
Hide the PeopleDrop dropdown by setting it's visible property to "no".

Now allow the user to open the form and enter data

They never see the tables, and all the complexity behind the relationships.
They see a form in which they enter the data in a meaningful manner (to
them)

You on the otherhand have the ability now to go back into the database and
make changes without touching all the data

example

the powers that BE!! come down and decree
All GradeA people are now GradeAA and we want a new grade added, GradeA.

Go to the formGrade, change the text from GradeA to GradeAA (add one A) -->
all the current GradeA's are now instantly GradeAA's (because they are
really stored as (5).
Add a new record GradeA and it now appears as a choice on your drop down
list. (value 10)

Pretend to faint over this mega-task, say it will take weeks to sort it all
out, then go fishing !!


Best of luck

Ed Warren

Hello All,
I'm just starting on my first database, it is for keeping track of
employees, vacancies etc. I just started with a table of biographical
stuff,
then when I wanted to put in their grades, and locations, I made up 2
separate tables, each with just these single fields in and used a 'lookup'
from a combo box from the biog table. Now having just read these 10
commandments on the mvps website
http://www.mvps.org/access/tencommandments.htm I am concerned that I have
sinned! I am very new to all the language used in databases, so am I
right
in thinking that I have already broken commandment 2 "Thou shalt never
allow thy users to see or edit tables directly, but only through forms and
thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One."
I beg forgiveness if this is the case, but if so, could someone advise
on
what is a better way of entering the information I require.

Regards
Rich

 

[Ed Warren]

If you through your hands in the air, how in heck will you be able to catch
them??

Ok you build a form for People.

You build a form "People_to_Grades"
Forms --> Create form by using wizard
AutoForm Tabular
Table --> PeopleToGrades
Finish
Design form
find the GradeID textbox, changeto: comobox then set the required
parameters.

in this form you have two text fields and a combo box.
Form: Format: Default View --> Continuous Forms (want to have a
list not just one form)
Text field1: bound to PeopleID (set format:visible --> false) (you
don't want to see this in the main form)
TextField2: bound to Date
ComboBox: bound to GradeID
Open the properties page and enter the following under the
appropriate tabs:
Data: rowsource --> tablegrades (select * from
tablegrades order by gradedescription)
Data: Bound Column --> 1
Format: Column Count --> 2 (we have two columns in the
'lookup table'
Format: Column Widths --> 0;2 (first column does not
show "0" and second column shows with a width of 2 inches (or whatever you
want)
Save the form

Open your main people form in design mode:

add a subform/report :: Set parent/child to PeopleID (links the two forms on
the PeopleID field)

Save the People Form.

Open it and now you have the ability to add


Now try to figure out how you're gonna catch them hands now that they are
flying about without any arms.

Cheers,

Ed Warren

Hi Ed,
Don't know if you are still following this thread, it's late this side of
the pond and I'm about to through my hands in the air!
I've done my tables as suggested and am starting on the forms, can you
clarify what you mean about building a form for the 'Relationship
PeopletoGrades'? I'm just using the wizards, I'm not really sure where I
ask
for the fields to put in it.
Maybe I need some sleep now!

Rich

Go ye forth and sin no more!

You are really 'ok'. Here is guidance to enlightment.
You have 'People'
People have grades and locations (and frankly probably a lot more). You may
need to handle telephones and addresses and how that is done depends on
if
each person is allowed to have one and only one telephone and address
(database normalization)

In the case at hand.

You most likely really want to handle the case where the person has many
grades (over time) so I will discuss that case.

TablePeople:

PeopleID (autonumber, Key), LastName, FirstName, etc.

TableGrades

GradeID (autonumber, Key), GradeDescription(text)

TablePeople_Grades

People_GradeID (autonumber, Key), PeopleID (number, FK), GradeID
(number,FK), Date (date/time)

---------------------------------------now we build the relationships in the
relationship view.

TablePeople 1 --> M TablePeople_Grades
TableGrades 1 --> M TablePeople_Grades

All this does is say that each person can have many People_Grades and
each
Grade can have several people assigned to it.

-----------------------------------------Now to the forms
(there are form wizards that build a reasonable form for you)

Build FormPeople to enter the data for the people
e.g.

Text box for LastName
Text box for FirstName
etc. for the rest of the fields

Build a Form for entering the data for the Grades
GradeID and Grade Description

Build a form for entering the data for the Relationship PeopleToGrades

drop down list for Person
drop down list for Grade
text field for Date

build a query to lookup person. Note set the sort order under display as
ascending
lkpPerson:: select PersonID, [lastname] & ", " & [firstname] as display from
TablePeople; (this will produce a "view" with

PersonID Display
1 Adams, Joe
2 Jones, Jane

build a query to lookup grade
lkpGrade:: selectGradeID,GradeDescription from TableGrade;

finally use these as the source of the lookup fields
set the 'bound' column to 1, number of columns to 2, column width to 0;2

Now you have a form with two dropdown lists in which you see 'meaningful'
stuff (e.g. a name and description) but store only a number in the database.

We are near the journey!!

Now you can add the PeopleToGrades to the FormPeople as a subform, with the
child/parent relationship set to PeopleID
Hide the PeopleDrop dropdown by setting it's visible property to "no".

Now allow the user to open the form and enter data

They never see the tables, and all the complexity behind the relationships.
They see a form in which they enter the data in a meaningful manner (to
them)

You on the otherhand have the ability now to go back into the database
and
make changes without touching all the data

example

the powers that BE!! come down and decree
All GradeA people are now GradeAA and we want a new grade added, GradeA.

Go to the formGrade, change the text from GradeA to GradeAA (add one A) -->
all the current GradeA's are now instantly GradeAA's (because they are
really stored as (5).
Add a new record GradeA and it now appears as a choice on your drop down
list. (value 10)

Pretend to faint over this mega-task, say it will take weeks to sort it all
out, then go fishing !!


Best of luck

Ed Warren

Hello All,
I'm just starting on my first database, it is for keeping track of
employees, vacancies etc. I just started with a table of biographical
stuff,
then when I wanted to put in their grades, and locations, I made up 2
separate tables, each with just these single fields in and used a 'lookup'
from a combo box from the biog table. Now having just read these 10
commandments on the mvps website
http://www.mvps.org/access/tencommandments.htm I am concerned that I have
sinned! I am very new to all the language used in databases, so am I
right
in thinking that I have already broken commandment 2 "Thou shalt never
allow thy users to see or edit tables directly, but only through forms and
thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One."
I beg forgiveness if this is the case, but if so, could someone advise
on
what is a better way of entering the information I require.

Regards
Rich

 

[Richard Hare]

Hi Ed,
I don't know how I'll catch them, but I'm certainly very grateful for you
taking the time to help me on this, I'm sure it will save me a load of head
scratching. (assuming I can find something to scratch it with!)

Thanks a lot

Rich