TWO additional tables: a table of Rooms, and a table of
RoomAssignments. RoomAssignments should have (at least) two fields -
the student ID and the room number (or unique room ID).
Yes this makes sense to me now.
A couple of things are bothering me though.
You're suggesting I set it up something like this?:
Student table:
[StudentID] [Lastname] [Firstname]
Yes...
Rooms table:
[RmID] [RoomNum]
Yes...
Timetable table:
[StudentID] [Per1RmID] [Per2RmID] [Per3RmID] [Per4RRmID]
NO.
Tables should expand DOWN not ACROSS. Timetable would be:
[StudentID] [Period] [RmID]
If a student is assigned to six periods, there would be SIX RECORDS -
not six fields! - in this table for that student.
Not sure how to combine Per1, Per2 etc. with RmID
You don't. You'ld run four Append queries from your wide-flat table,
or one "Normalizing Union Query":
SELECT StudentID, (1) AS Period, Rm1 AS RmID
FROM timetable
WHERE Rm1 IS NOT NULL
UNION ALL
SELECT StudentID, (2) AS Period, Rm2 AS RmID
FROM timetable
WHERE Rm2 IS NOT NULL
UNION ALL
SELECT StudentID, (3) AS Period, Rm3 AS RmID
FROM timetable
WHERE Rm3 IS NOT NULL
UNION ALL
SELECT StudentID, (4) AS Period, Rm4 AS RmID
FROM timetable
WHERE Rm4 IS NOT NULL
and base an Append query on this.
Also, I will probably start to make use of the school's
StudentIDNumber system next semester so that I can uniquely identify
every student. They use a nine-digit number for each student.
If I use it, should I do away with my StudentID autonumber system and
use the school's ID number as the Key index field in my Student table?
Yes. Some will disagree with me (I'm in a discussion on this very
subject in the thread entitled "DB Normalization" in
m.p.a.gettingstarted), but I'd say if you have a unique, stable,
reasonably-sized natural key such as an officially assigned StudentID,
USE IT. You'll want to include it in your table ANYHOW, uniquely
indexed, so you already will *have* the overhead!
What I don't like about this though, is that my tables will be
cluttered with much larger numbers (Student ID's 9 digits vs. 2 or 3
digits). Also the creation of these additional tables will necessitate
storing 600+ of these large student IDs in them.
<g> An Access database can hold 2 GByte of data. If you had 600,000
students you might want to start getting a bit concerned; if you had
six million you'ld be using SQL Server anyway.
Wouldn't it be more efficient just to throw in the four room numbers
into my Student table as I now have it?
IF you can be ABSOLUTELY CERTAIN that you will NEVER in the course of
the user of the database ever need a fifth room. Oh yes... you did say
you needed to add four new rooms already... oh my... <g>
In addition, with the normalized design it's dead easy to find out
"Who's assigned to room 3 anytime today" or "how many students are in
rooms 3 or 4 in the morning" or all sorts of changes - searching only
one field. In your case you need a complex search of four fields.
I created a couple of test tables to try out your code (TestOrig) and
(TestLatest) and ran the following code:
INSERT INTO TestOrig ( LastName, FirstName, HomeForm, Per3 )
SELECT TestLatest.LastName, TestLatest.FirstName, TestLatest.HomeForm,
TestLatest.Per3
FROM TestOrig LEFT JOIN TestLatest ON (TestOrig.LastName =
TestLatest.LastName) AND (TestOrig.FirstName = TestLatest.FirstName)
WHERE (((TestLatest.LastName) Is Null));
But the append query couldn't find any records to append. I made the
tables quite simple. The TestOrig had 10 records. For TestLatest I
added a new record in the number 10 spot and copied the TestOrig's
10th record to number 11 spot.
So the Append query should find the 'new' number 10 record and append
it to TestOrig.
Any ideas why it didn't work?
Because it duplicated the 10th record. That's what the query is
designed to do - to append only records that are NOT duplicates. This
record IS a duplicate.
