Re-newing table

  • Thread starter Thread starter Eric G
  • Start date Start date
E

Eric G

Not sure where to post this question. I have A2K.

I could really use some expert help here.

I have a Students table which consists of approx. 600 names and has
seven fields:
StudentID, Last Name, First Name, Per1, Per2, Per3, Per4 (room nos.)

I need to update the table for semester two in such a way that the
StudentID numbers are maintained as well as Last and First name.
In other words only fields Per1,2,3,4 need to be updated.

That in and of itself wouldn't pose a problem.

However, some 60 odd new students have been added to the school.
I need to add these new students to the Student table along with their
per1,2,3,4 room numbers.

The easiest way to solve the problem would have been to scrap the old
Students table at the beginning of sem. two and start with a new
table. The problem with this however would be that the first semester
records could no longer be accessed.

And now that a week's worth of new records have been added, it further
complicates the mix.

I hope you understand my problem.
The school secretary should be providing, either:

1. List of new students + room numbers.
2. List of old students + new room numbers.
3. List of all students + room numbers.

I told her 1 and 2 would be much preferred, but I may have to settle
for 3.

I look forward to hearing some suggestions as to how I should proceed.
Thanks! Eric
 
Hi,
Do you have forms, queries, reports based on existing
table? Will you still need to access semester 1 info even
after semester 2 info is loaded into table?
It seems to me that #3 from the secretary would be the
easiest. You could create a new table, load all that
information into it, rename your semester 1 table to
something else, then rename your new table to the old
semester 1 table. Then, all your forms, queries, and
reports would now work against your semester 2 table.
If I totally misunderstood, I'm sorry.
 
Hi Les,

Thanks for helping out!
Hi,
Do you have forms, queries, reports based on existing
table? Will you still need to access semester 1 info even
after semester 2 info is loaded into table?
It seems to me that #3 from the secretary would be the
easiest. You could create a new table, load all that
information into it, rename your semester 1 table to
something else, then rename your new table to the old
semester 1 table. Then, all your forms, queries, and
reports would now work against your semester 2 table.
If I totally misunderstood, I'm sorry.
Click to expand...

I think you've understood most of what I'm up against. I'll fill you
in on a couple of things.
Yes I do have forms, queries and reports based on the existing table.

The main problem is that we would like to access semester 1 data even
though we're in semester 2. For example we'd like to pull up a
student's complete record, sem 1 and sem 2 combined.

So the problem is that if the secretary has to go with #3 and gives me
the new complete student list, it will be in alpha order, and new
students will be claiming old students' StudentID.
For example in the old list Peter Smith may have been StudentID 486.
Now Jack Smith, a new student, may be StudentID 486.

Is there some way to take two tables of identical structure, compare
them, and then cut and paste all the different records in the bigger
table to the end of the table? If that could be done, I'd just have to
throw all of these rearranged records into a new table and use that
one.

Eric


 
Where to start? Where to start?

Ok, the first thing to do is to break your data into at least two tables and
possibley more tables.

Table: Students
StudentID
LastName
FirstName

Table: StudentPeriods
StudentID
PeriodNum
RoomNumber
YearNumber
Semester


The Students table contains information about the students.
The nice feature here is that as you need to add information on the students
(DOB, HomePhone, etc) you can easily create a new field to hold that information

StudentPeriods holds information about each student's assignment to specific
Rooms during specific period during specific semesters.

You can transfer the existing data into the proper tables using append queries.
The toughest might be the StudentPeriods. To do that I would use a union query
as the source, but you could do that with four separate append queries.

Insert Into StudentPeriods
(StudentID, PeriodNum, RoomNumber, YearNumber,Semester)
SELECT StudentID, 1, Per1, 2003, "Fall"

Repeat the above for each period.

Now for the data from the secretary for the new semester. You could use the
secretary's input and match against the student names to identify the existing
students and add records to StudentPeriods only. The additional students would
need to have records added to both tables.

I hope this helps get you started.
 
Hi John,

Thanks very much for helping out!
Table: StudentPeriods
StudentID
PeriodNum
RoomNumber
YearNumber
Semester
Click to expand...
StudentPeriods holds information about each student's assignment to specific
Rooms during specific period during specific semesters.
Click to expand...

I'm trying to understand how this table will hold the Per. nos. and
Rm nos. for each student. Let's look at StudentID no.1:
Student ID Per1 Per2 Per3 Per4 Yr. Sem
1 A102 D103 B104 A105 10 1

Is the above how you are thinking I should set this up?
If so, then there really wouldn't be a "RoomNumber" field right?
Just a Per. fields which would house the room numbers, correct?
You can transfer the existing data into the proper tables using append queries.
The toughest might be the StudentPeriods. To do that I would use a union query
as the source, but you could do that with four separate append queries.

Insert Into StudentPeriods
(StudentID, PeriodNum, RoomNumber, YearNumber,Semester)
SELECT StudentID, 1, Per1, 2003, "Fall"

Repeat the above for each period.
Click to expand...

I think to make life easier, I'll add the other table(s) next
semester when it will be easier to make a fresh start with them.
Now for the data from the secretary for the new semester. You could use the
secretary's input and match against the student names to identify the existing
students and add records to StudentPeriods only. The additional students would
need to have records added to both tables.
Click to expand...

Could you please explain how I can "match against the student names
to identify existing students" ?
Let's assume I have my initial Student A table and I import the new
bigger table into Access and name it Student B table.

Thanks again for your help. Eric
 
Back
Top