HELP: Duplicating Entries

T

Tim

ACCESS 2000

I have a database which keeps track of classes that need to be taken on a
yearly basis. I currenly have my tables arranged in the following manner

Auto# YEAR NAME CLASS 1 CLASS 2
1 2004 Adams 1-feb-04 6-JUL-04
2 2004 Smith 3-FEB-04 6-JUL-04
3 2005 Adams 5-feb-05 7-JUL-05
4 2005 Smith 5-feb-05 8-JUL-05


Is there a way to format the tables to automatically add Adams and Smith for
2005 as opposed to going into the table and entering them manually.
And if I am doing this completly wrong, I am open to ideas that will help.

Thanks
 
M

[MVP] S.Clark

The part that you're doing completely wrong is known as "Committing
Spreadsheet". Access is a database that prefers to have its data
"normalized".

So you should have several tables like:
Employee
Class
Employee_Class
....which would break the information into more concise pieces.

This allows the data to grow horizontally, not vertically.
 
J

John Vinson

ACCESS 2000

I have a database which keeps track of classes that need to be taken on a
yearly basis. I currenly have my tables arranged in the following manner

Auto# YEAR NAME CLASS 1 CLASS 2
1 2004 Adams 1-feb-04 6-JUL-04
2 2004 Smith 3-FEB-04 6-JUL-04
3 2005 Adams 5-feb-05 7-JUL-05
4 2005 Smith 5-feb-05 8-JUL-05


Is there a way to format the tables to automatically add Adams and Smith for
2005 as opposed to going into the table and entering them manually.
And if I am doing this completly wrong, I am open to ideas that will help.

Thanks
Click to expand...

No, you can't "format" the table to automagically add data. If you
could, would you want it to add records for 2006? 2018? 2426?

I'm not sure what you're trying to accomplish with this table. Your
Class 1 and Class 2 fields are non-normalized, for one thing; they
appear to be redundant with the Year field.

Could you step back and describe what you're trying to *accomplish*?
How is it determined that a person needs a class, and which class? Do
all people in the (undefined) table of People need to take every
class, or do some people take some classes and other people take
different ones?

John W. Vinson[MVP]
 
T

Tim

Keep in mind my knowledge of Microsoft Access is very limited, mostly self
taught, very little formal classes/training, so I do not know what
"normalized" means; S. Clark said the same thing in his response.

My goal is to set up a training database, and the classes I have in my table
are required annually.

The current structure is that I have a BIO table with the names of all
employees with the primary key set to their SS#.

The table I have with the classes is linked to the BIO table so I can pull
the names from a drop-down menu.

I had the YEAR field in the table was the only way I could think of to get a
generalized grouping. And when you would open the corresponding report, you
would be prompted to enter the year. You would enter 2004, and the report
would show those entries for 2004.

I hope this answers your questions, and I thank you for your help.
 
J

John Vinson

Keep in mind my knowledge of Microsoft Access is very limited, mostly self
taught, very little formal classes/training, so I do not know what
"normalized" means; S. Clark said the same thing in his response.

My goal is to set up a training database, and the classes I have in my table
are required annually.

The current structure is that I have a BIO table with the names of all
employees with the primary key set to their SS#.
Click to expand...

Again: does EVERY employee have to take EVERY course? Or is it
selective?
The table I have with the classes is linked to the BIO table so I can pull
the names from a drop-down menu.
Click to expand...

You need *three* tables not two. Maybe you do and I'm
misunderstanding. Since you have a many to many relationship from
classes to employees, you need an Enrollment table with the ClassID
and the StudentID.
I had the YEAR field in the table was the only way I could think of to get a
generalized grouping. And when you would open the corresponding report, you
would be prompted to enter the year. You would enter 2004, and the report
would show those entries for 2004.
Click to expand...

If you have a Date/Time field in the Enrollment (or the Class) table,
you can easily create a Query with a calculated field by typing

ClassYear: Year([classdate])

This will extract the year from the date/time field, and you can use
this field as a criterion or for grouping. It is not necessary to
store it in the table.
I hope this answers your questions, and I thank you for your help.
Click to expand...


John W. Vinson[MVP]
 
T

Tim

Your last post was helpful, and it gave me some ideas.

To answer your question, yes, these classes are annual requirements for all
employees.

The reason I had the table structured in the way I had it (adding a list of
blank entries per employee for every year) was I needed to know who has not
had the training. I had a IS NULL code in the corresponding query to
truncate to the list to show only those who have not attended or completed
the training.
 
J

John Vinson

Your last post was helpful, and it gave me some ideas.

To answer your question, yes, these classes are annual requirements for all
employees.

The reason I had the table structured in the way I had it (adding a list of
blank entries per employee for every year) was I needed to know who has not
had the training. I had a IS NULL code in the corresponding query to
truncate to the list to show only those who have not attended or completed
the training.
Click to expand...

You can determine who didn't take the course with an Unmatched query,
selecting those people in the employee table who do not have a record
in the enrollment table for a given year. It's not necessary to add a
bunch of blank entries - in my experience, doing so is risky because
they have a way of never getting filled in!

John W. Vinson[MVP]
 
T

Tim

I think I am on the right track now. Here is what I have done:

I have a BIO table that contains the EMPLOYEES with the primary key set to
their SS#.
I have a TRAINING LISTING table that is an entry table that contains the
class they attended and the date they started and completed the class.
Branching off the TRAINING LISTING table is a CLASS table that contains the
name of the class.

I have tried to create a Unmatched Query. It pulls SURNAME from BIO, NAME
from TRAINING LISTING and CLASS from TRAINING LISTING; however, my query
comes up blank.

Where did I go wrong?
 
J

John Vinson

I think I am on the right track now. Here is what I have done:

I have a BIO table that contains the EMPLOYEES with the primary key set to
their SS#.
I have a TRAINING LISTING table that is an entry table that contains the
class they attended and the date they started and completed the class.
Branching off the TRAINING LISTING table is a CLASS table that contains the
name of the class.
Click to expand...

Tables don't "branch off". If you're using the Subdatasheet option in
your table datasheets, be aware that it's buggy and is a severe
performance hog, and it's not necessary.

What is the actual structure of your three tables? What do you mean by
"an entry table"? Does the TRAINING LISTING table have a SS# field as
a foreign key to BIO? Don't join the TRAINING LISTING table by name;
names are NOT unique, you very well might have two employees who (by
relationship or chance) have the same surname.
I have tried to create a Unmatched Query. It pulls SURNAME from BIO, NAME
from TRAINING LISTING and CLASS from TRAINING LISTING; however, my query
comes up blank.
Click to expand...

Please open this query in SQL view and post the SQL here.

John W. Vinson[MVP]
 
T

Tim

The BIO (my master table) contains SS#(key), Surname, First, MI. I have a
1-to-Many relationship to the TRAINING LISTING table.
TRAINING LISTING contains:
RECORD # - an auto number entry (primary key)
NAME – employee’s name (this is connected to SS# in the BIO table; when
putting entries into the table, a drop-down menu appears with 3 columns,
containing all parts of the name. There is a 4th column which is hidden that
contains the SS#.)
CLASS – name of the class being taken.
START DATE – Date the class was started
END DATE – Date the class was completed

The third table is called CLASSES which is a listing of the classes that
have been taken. This is a sub-table of TRAINING LISTING, a 1-to-many
relationship.

What I meant by TRAINING LISTING being an entry table is that this is the
table I used to enter all of the training each employee has taken.

If you need to, you can contact me directly at my e-mail address posted in
my profile.
 
J

John Vinson

The BIO (my master table) contains SS#(key), Surname, First, MI. I have a
1-to-Many relationship to the TRAINING LISTING table.
TRAINING LISTING contains:
RECORD # - an auto number entry (primary key)
NAME – employee’s name (this is connected to SS# in the BIO table; when
putting entries into the table, a drop-down menu appears with 3 columns,
containing all parts of the name. There is a 4th column which is hidden that
contains the SS#.)
Click to expand...

Ok... you're another victim of Microsoft's misfeature, the Lookup
Wizard.

It *APPEARS* that the TRAINING LISTING table contains the name. It
doesn't. What it contains is the SS#; that simple fact is *concealed*
from your view by the Lookup combo box. Any query that you build with
TRAINING LISTING expecting the name to appear will cause confusion
because it *doesn't* (and shouldn't) contain the name.
CLASS – name of the class being taken.
START DATE – Date the class was started
END DATE – Date the class was completed

The third table is called CLASSES which is a listing of the classes that
have been taken. This is a sub-table of TRAINING LISTING, a 1-to-many
relationship.
Click to expand...

Again... it's not really a "subtable". The Access table interface
presents it that way, but it's in fact a perfectly real, valid table
in its own right.
What I meant by TRAINING LISTING being an entry table is that this is the
table I used to enter all of the training each employee has taken.
Click to expand...

I would strongly suggest that table datasheets (and subdatasheets, and
lookup fields)

*****NEVER*****

be used for data entry or display. They are of very limited utility
and flexibility. The relatively new additions of Lookup Fields and
Subdatasheets lets table datasheets get from A to D instead of just
from A to B - but they're still crippled data entry tools.

Instead, create a Form. You can base a Form on the BIO table, with a
Subform based on TRAINING LISTING, linked by SS#. The Subform would
have a Combo Box based on the Class table to enter the class
information.

To get back to your original question - you can create an Append Query
to enroll a whole group of students into a class in one operation, by
appending their SS# and the class information to the TRAINING LISTING
table. You'll need some sort of criteria to identify which students;
if it's everyone in the BIO table, simply create a Query based on BIO.
Select only the SS# field. In vacant field cells in the Query put the
Class Name (or Class ID, if the CLASSES table has an ID, you don't
say), and put the dates in two more vacant field cells. Turn this
query into an Append query and append it to TRAINING LISTING.
If you need to, you can contact me directly at my e-mail address posted in
my profile.
Click to expand...

Private EMail support is available at my standard consulting rates, if
you're interested.


John W. Vinson[MVP]
 
J

John Vinson

would you send me the info please?
Click to expand...

Consulting info? Sure. Please send me a valid email address at

JVinson <at>
WysardOfInfo <dot>
com

Don't post it here, or the spambots will get it.


John W. Vinson[MVP]
 
Top