Data seperation for form with many fields

[dhhorowitz77]

I am importing a .xls file which contains the information to be used on a
form (outside of access) with many fields.
I need to figure out a way to seperate the information so that the form will
be properly populated.
The .xls file has multiple instances of each person in the database, as many
as 9 entries each, therefore using the ID Number as the primary key won't
work.
The basis of the database is for student id cards, and the school
administration wants the class schedule on the back of the card.
So the form I am using has 36 fields on it to be populated, Student ID,
First Name, Last Name, Photo, R1, R1 Course, R1 Room, R1 Teacher (these are
repeated with R3, R5, R7, W2, W4, W6, and W8)
Does anyone out there know how I can seperate the imported .xls into tables
or queries where the Student ID matches on each for form population? If it's
any help we are using Datacard's IDCenter v6.5 Gold.

 

[M Skabialka]

You need a table with Student/Staff info, ID, last and first name, etc -
unique info for this person
123 Jones Greg student
345 Smith Mary student
987 White James staff
976 Brown Jane staff

Not being sure what R and W are, your other table would have the ID,
RorWindicator, RorWNumber, Course, Room, Teacher ID etc and could be
imported directly from your spreadsheet which seems to be in this format
already.
123 R 1 Science 311 987
123 R 2 Math 200 976
123 W 2 History 137 842
As you can see there are NOT 36 fields - there shouldn't be many fields
holding the same kind of data, e.g. course or room number.
A one to many join query would match student info with their schedule for
your ID cards.

 

[Klatuu]

First, you do not populate forms in this context. Forms don't contain data,
they only present data stored in tables. Only tables store data.

It is also important to point out the philosophies of data storage in a
spreadsheet and a relational database are very different. Because of their
nature, spreadsheets, as you mentioned in your post, carry a lot of
redundant data. They are also short and wide. That is, there are a lot of
columns and a few rows because all the related data has to be on one row.

Relational models, on the other hand, are tall and narrow. That is, a few
fields in a table with a lot of records. In a relational model, there may
be many rows in a table that would all be on the same row in a spreadsheet.
So, to get your data right, you have to have a basic understanding of
relational database normalization. If you don't understand it, you should
do some research before you start. It will save you a lot of time and
grief. An incorrectly normalized database is very hard to work with and
performance usually suffers.

So, what you need to do is determine what tables you need to configure the
data for a relational database. As it happens, A school database is an
excellent learning model and is often used in teaching because it touches on
just about all the basics.

Here is a very basic description of the model:
The tables you will need are:
tblStudent - Contains demographic information on the students
tblTeacher - Contains demographic information on the teachers
tblCourse - Contains information on each course offered.
tblClass - Contains information about a specific instance of a course
offering. It will have a relation to tblTeacher to indentify who is
teaching this instance of the course
tblClassStudents - This is what is called a junction table. It is required
when there is a many to many relationships to resolve (Many Students may
attend many Classes) and It will contain foreign keys to tblClass and
tblStudent to specify which students are in a class and which classes a
student is attending.

Once you have that modeled, you need to map the data items (Columns in the
spreadsheet to the Fields in the correct table).

The next step is to write queries that will populate the fields in the
tables with data from the cells in the spreadsheet.

And the actual execution would be to link to the spreadsheet as if it were a
table and run the queries to actually put the data in the tables.

Best of Luck to you as you have some work ahead.

 

[dhhorowitz77]

Apparently I'm doing something wrong. I cannot get the One to Many join query
to work. Could you possibly list the steps I need to take, so that I can see
what I missed?

 

[dhhorowitz77]

Let me clarify, There are 143 students to make cards for, they are each in
the spreadsheet/database(from import) 8 or 9 times each. There are only 7
columns, and there are 1148 records. In the "Beginning Period" column there
are the numbers 1 - 8, these need to be seperated into individual tables or
queries. This is a high school that utilizes the 2-day system, they have a
Red day and a White day. The odd numbered classes are on the Red day (R1,
R3...) and the even numbered classes are on the White day (W2, W4...) All of
this information is being linked to an outside program where the cards are
created. Each piece of information goes into a field in that program. When
all is said and done, I need 36 total fields for this to work.
The fields (or columns) I need are as follows:
Student ID, First Name, Last Name, R1, R1 Course, R1 Room, R1 Teacher, R3,
R3 Course, R3 Room, R3 Teacher, R5, R5 Course, R5 Room, R5 Teacher, R7, R7
Course, R7 Room, R7 Teacher, W2, W2 Course, W2 Room, W2 Teacher, W4, W4
Course, W4 Room, W4 Teacher, W6, W6 Course, W6 Room, W6 Teacher, W8, W8
Course, W8 Room, W8 Teacher.
The fields (or columns) that I have to work with are:
Student ID, First Name, Last Name, Beginning Period, Course Title, Room
Number, Teacher Name.

example:
1234, Bob, Smith, 1, Math, 48, Jones
1234, Bob, Smith, 2, English, 98, Thompson
1234, Bob, Smith, 3, History, 72, Peters
1234, Bob, Smith, 4, Science, 33, Williams

All 143 students are in the database like that.

Any new ideas on how to seperate them while keeping the students connected
to their respective classes?

 

[Klatuu]

If you follow the steps I described previously, you can create a query that
will provide that information for you.

 

[dhhorowitz77]

I can't use the spreadsheet as a Link Table....one of the fields needs to be
text and it is linking as number...yet, when I import the spreadsheet into a
new table, the field is set to text...as for the spreadsheet design, it is
NOT short and wide....it is long and narrow, I don't know if this makes any
difference.

 

[Klatuu]

I can't use the spreadsheet as a Link Table....one of the fields needs to be
text and it is linking as number

Two options, convert the value as needed during the import or import the raw
data into a table defined with the correct data types.

as for the spreadsheet design, it is NOT short and wide....it is long and
narrow, I don't know if this makes any difference

It is immaterial. My description was only to point out one of the
differences between spreadsheet and relational data models. When converted
to a relational model, it should be narrower and longer.