I can't solve this problem. Any ideas as to what to try next would be most
welcome. You'll need to view this article in a non-proportional font if you
want to read the diagrams.
I have to use Access, Excel, or some other readily available tool, to
convert a modern relational database to a sequential data base of the sort
that used to exist 100 years ago.
The data base describes enrolments on classes. The present, relational
database consists of four tables, as follows:
Students: The fields in the Students table are: Student number (key),
Initial, Surname. For example (below), student C. Houseman has the student
number S003.
Courses: The fields in the Courses table are: Course number (key), Course
name. For example, History has course number C002.
Presentations: The fields in the Presentations table are: Presentation
number (key), Course number, Date. For example, the first record means that
there is a presentation of course C003 (Chemistry) on the first of March (or
the third of January) and this record has the presentation number P001.
Students Courses Presentations
S001 A Fennel C001 Geography P001 C003 01/03/2000
S002 B Gherkin C002 History P002 C001 01/03/2000
S003 C Houseman C003 Chemistry P003 C001 03/03/2000
S004 D Inkwell C004 Mathematics P004 C002 03/03/2000
S005 E Jumpoff C005 Literature P005 C002 04/03/2000
C006 Art
The fields in the Enrolments table are: Enrolment number (key), Student
number, Presentation number. For example, the first record means that
student S002 (B. Gherkin) is enrolled on presentation P004 (History on 3
March) and this record has the enrolment number E001.
Enrolments
E001 S002 P004
E002 S001 P005
E003 S005 P003
E004 S004 P003
E005 S004 P005
Now, the format into which this has to be converted uses presentation
numbers, course numbers and student numbers in the same way, but it uses a
sequential file to record enrolments. Data is stored as follows:
start of file ----> Presentation number
Student number
Student number
Student number
Student number
Presentation number
Student number
Student number
Zero or more Student Numbers follow each Presentation Number. The
Presentation Number gives the number of the presentation on which the
students are enrolled, e.g. the following arrangement means
Presentation P001 is empty
Presentation P002 is empty
Students S004 and S005 are enrolled on presentation P003
Student S002 is enrolled on presentation P004
Student S004 is enrolled on presentation P005
start of file ----> P001
P002
P003
S004
S005
P004
S002
P005
S004
The sequential file is typically 10,000 records long so I can't convert it
by hand.
One other thing: I have to export or save this file in Fixed Width Field
format, Therefore, producing a report in Access and exporting it as a text
file doesn't work, because the field widths are all lost by the time the
data gets into the report.
All ideas are welcome. I'm damned if I can think how to do it.
