Combining (appending?) tables to form a 'new' table

[Guest]

I have imported several tables from EXCEL that are my class lists and other
info. I would like to combine these lists to make a master list of my
students. How can I combine the tables to a new table - and realizing that a
couple of the students are listed more than once - different classes, but I
(obviously) only want them listed once. I have tried appending with no
results - I suppose I do not know how to. I tried earlier but was informed
that I had problems with the primary key. I have changed the primary key to
student ID #, but look at the situation either way. I have a field with
LastName and another with FirstName.
Please be patient and be willing to walk me thru a couple of times. I
tried using the Office Assistant, but have had limited/no help.
In advance, thank you very much for your help.
How do I get this combined table to be a new table?

 

[Guest]

1. Create the main table with a key student ID
2. Run append queries for all the table you imported into the new table you
have created in the first stage.

All the duplicares won't be inserted again duo to duplicate key, so in the
end you will be left with one entry for each student.

 

[Guest]

If I want to keep each individual table, How do I create this main table that
you are talking about? If I am keeping each individual class list (table),
is it reasonable to have an ID key (1-25 or so as the primary key) Does that
create major problems with appending and avoiding duplicates of James Smith
with student ID# of 123456789?

 

[John Vinson]

If I want to keep each individual table

If you want to keep the individual tables, you are violating the basic
principles of relational database design, and causing yourself endless
problems maintaining this database in the future. It's simply
*incorrect*.

Your choice though - use a wrong design and pay the price in
complexity, or use a correctly normalized design as Ofer suggests, use
Queries to recover the individual sets of data, and work with Access
instead of against it. Take your pick!

John W. Vinson[MVP]

 

[Vincent Johns]

If you want to keep the individual tables, you are violating the basic
principles of relational database design, and causing yourself endless
problems maintaining this database in the future. It's simply
*incorrect*.

Your choice though - use a wrong design and pay the price in
complexity, or use a correctly normalized design as Ofer suggests, use
Queries to recover the individual sets of data, and work with Access
instead of against it. Take your pick!

John W. Vinson[MVP]

If you're worried about losing the format of the old Tables that you're
familiar with, I think you shouldn't worry too much. Even after
normalizing your Tables (getting rid of redundant stuff), you'll
probably still be able to define Queries to display the information in
the format you're used to. If you want to do that and have trouble with
it, no doubt someone here will be able to offer suggestions.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

I would like to keep an alphabetical list within each class, so I'm thinking
of keeping each class. Would the other choice be a form with a subform -
with the subform indicating the class, year and period?

 

[John Vinson]

I would like to keep an alphabetical list within each class, so I'm thinking
of keeping each class. Would the other choice be a form with a subform -
with the subform indicating the class, year and period?

If you're used to Excel, then it makes sense to think of keeping
separate Tables (sheets), each in alphabetical order.

But that is Excel; this is Access. THEY ARE DIFFERENT.

A Table in Access is a data repository - and is *NOT* used for data
viewing or reporting. IN particular, you shouldn't consider a Table to
have ANY particular order.

If you want to have a list of the students in a class, use a Form with
a Subform as you suggest; base the Subform on a query sorting the
names alphabetically. It is not necessary (in fact it's a great deal
of extra work!) to base each subform on another table. You're not
"keeping" an alphabetical list - you're dynamically generating an
alpabetically sorted list on demand, as needed.

John W. Vinson[MVP]

 

[Vincent Johns]

I would like to keep an alphabetical list within each class, so I'm thinking
of keeping each class. Would the other choice be a form with a subform -
with the subform indicating the class, year and period?

:

I hope I'm not duplicating something someone else has already said, but
I think you need at least 3 Tables (to support a many (students) to many
(classes) relationship):

- Students (from the class lists, as I suppose you have already done)
- Classes (probably a Table with only a few fields)
- Table in which each record specifies enrollment of one student in
one class

In this 3rd Table, you might have several students appearing in one
class, and you might have two or more classes linked to one student.
The Table might include as few as two fields per record, [StudentID] and
[ClassID], though you might have reason to add others, such as grade
received or date enrolled, stuff like that.

Fields in a record in the [Class] Table would relate to the class
generally, nothing relating to any specific student. These could
include class title, textbook used, meeting place/time, &c.

Fields in a record in the [Student] Table would relate to a student
generally, not to any specific class. These could include student's
first name, student ID number, stuff like that.

If you wished to record different data for some class that met in
multiple years, you could have one Table for [Class], with information
covering all years for that class, and another one, [ClassYear],
specifying the year, a reference to the [Class], and perhaps details
such as where that class met in the given year. You'd have multiple
[ClassYear] records linked to one [Class] record if you do it this way.

As John Vinson pointed out, the "alphabetical" part is not something you
need to worry about in the Tables -- you can do the sorting and
filtering via the Queries you define. However, if you wish, you can
also apply sorting and filtering to a Table to make it easier to read
(and if you like the results, you can save your filter as a Query,
giving it a name when you save it). But it's optional.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.