Table - consolidate into fixed number of rows, many columns (1 Viewer)

sarah23

New member
Local time
Today, 07:27
Joined
Oct 15, 2014
Messages
6
Hi there,

I'm new to Access and know pretty much nothing about coding.

I have a table ("Student Info") that looks like this:

ID....Student.....Student #......Teacher Name......Teacher ID
1........A...............432...............Alice...................62
2........B...............674...............Alice...................62
3........C...............875...............Alice...................62
4........D...............934...............Katie..................18
5........E...............345...............Katie..................18
6........F...............134...............Dan.....................4
7........G...............734...............Dan.....................4

The above table suits me well for data entry.
For analysis reasons, I want it to have another table or query or something (let's call it "Teacher Info") that looks like this:

Teacher ID....Teacher Name....St1.....St2......St3...St#1...St#2...St#3
62......................Alice...........A.........B........C.....432.....674.....875
18......................Katie...........D.........E........-.....934.....345......-
4........................Dan............F.........G........-.....134.....734......-

Is there any way that I could do this? I have been struggling with this for a while.

Just so you know how this data is connected - At the moment, I have a form where I put in new Teacher information. I have a subform attached to that, where I put the student names and numbers. This way, the students are linked with their student #s, and each of them are linked to a teacher. Unfortunately, the table ("New Teacher Registration") that this creates looks like this:

id....Teacher Name
62...... Alice
(+ tab).........A.........432
...................B.........674
...................C.........875
18.......Katie
(+ tab).........D.........934
...................E.........345
4........Dan
(+ tab).........F.........134
...................G.........734

I created the "Student Info" table (above) from this to help with data entry (drop-downs and such). Now I'd like to create a "Teacher Info" table (above) for analysis help.

If someone could please help me with this, I would be very, very grateful!
Thanks!
Sarah
 

sarah23

New member
Local time
Today, 07:27
Joined
Oct 15, 2014
Messages
6
Thanks for responding to my post, jdraw.

I understand that this may not be the most ideal circumstance to keep my database normalized. Unfortunately, this is an old database, and I've been asked to "clean it up." So I don't have the authority to revamp the whole thing from scratch.

My supervisor has asked me to have the data in this format:
Teacher ID....Teacher Name....St1.....St2......St3...St#1...St#2...St#3
62......................Alice...........A......... B........C.....432.....674.....875
18......................Katie...........D......... E........-.....934.....345......-
4........................Dan............F......... G........-.....134.....734......-

Is there any way that I can have it in this format, while ensuring that St1 is linked to St#1, St2 is linked to St#2, and so forth? I am open to suggestions.

The reason why I had made the "Student Info" table was to create drop-down boxes (that are dependent on linked combo boxes) in other, related forms. If there is another way to make dependent drop-downs while keeping the data in the desired format, I am all ears!

I'd very much appreciate any advice!
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:27
Joined
Jan 23, 2006
Messages
15,362
Perhaps we should start with:
a) the database and tables that were given to you (you were given)
b) what you were asked to do explicitly.

a)You can get a screen capture of your Relationships window, save it as a jpg, but zip the file before posting (since you don't yet have 10 posts--zipping will allow you to attach a file).
see http://www.access-programmers.co.uk/forums/showthread.php?t=212125

b)Tell us about the original database --what info does it contain and who uses it.
Then identify exactly what you were asked to do.
 

sarah23

New member
Local time
Today, 07:27
Joined
Oct 15, 2014
Messages
6
Thanks again for staying with me here, jdraw.

The database that was given to me had these things:
1) New Teacher Registration Form
that fed into
2) New Teacher Registration Table
it also had
3) Detention forms

The form had text boxes to put the student info (name and number), which linked to the table. However, each student's name was not intrinsically linked to his/her number. This became a problem later - I will get back to that.
So the table looked like this
Teacher ID....Teacher Name....St1.....St2......St3...St#1...St#2...St#3
62......................Alice...........A......... B........C.....432.....674.....875
18......................Katie...........D......... E........-.....934.....345......-
4........................Dan............F......... G........-.....134.....734......-
And my supervisor was happy.
However, this setup became troublesome when I began to fill out other forms. Let's call these other forms "Detention Forms." Initially, these were all filled out manually, which produced bad quality data. So I went about fixing it, by using drop-downs and linked combo boxes:
When I fill out a detention form, I need to start by putting in the Teacher. I wanted to format the forms so that, when I put in a Teacher in the "Teacher" combo box, the combo box drop-down for "Student" is restricted to only the students in his/her class. Then, once the student was selected, I wanted his/her "Student number" to automatically be filled in another text box.
This had to be done for better data entry.
So that's why I created the subform for the students' names and numbers, and the "Student Info" table. I linked everything up, and now my drop-downs and such are all working smoothly.

However, now the student names and numbers are feeding into the subform table, and the "Student Info" table - they are no longer going into the "New Teacher Registration" table, as they were previously. My supervisor needs this data to be view-able in the format she had previously had it in. Is there any way to do that?

Thanks again!
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:27
Joined
Jan 23, 2006
Messages
15,362
What about the relationships window?
You don't start with forms. Start with the tables and your requirements. You have to design tables and relationships to meet/support the business.
 

accedeholdings

Registered User.
Local time
Today, 07:27
Joined
Sep 29, 2014
Messages
15
I was just about to ask the same question.


What about the relationships window?
You don't start with forms. Start with the tables and your requirements. You have to design tables and relationships to meet/support the business.
 

sarah23

New member
Local time
Today, 07:27
Joined
Oct 15, 2014
Messages
6
I am not able to post a screenshot of the relationships window due to privacy issues.

The original database's relationships window had the New Teacher Registration table, which included:
- id
- Teacher Name
- Street Address
- Email
- (and other information about the teachers)
- St1
- St2
- St3
- St#1
- St#2
- St#3
None of these were a primary key.

This table was linked to another table ("Teacher Linkage") by the "Teacher Name" field. The Teacher Linkage table linked to all the other tables (from Detention forms, and other similar forms). I know this sounds inefficient, but it's a long story and I'd just like to leave that part the way it is!

Now I've added the "Student Info" table. It has the fields:
- ID (primary key)
- Student
- Student #
- Teacher Name
- Teacher ID
This table is linked to the New Teacher Registration table in a one-to-many relationship:
id (now a primary key, from New Teacher Registration) is the "one," and it's linked to the Teacher ID (from Student Info), which is the "many."

The way I've set it up now, the original "St1, St2, St3, St#1, St#2, St#3" fields in the New Teacher Registration Table are not linked to anything. If I could link them somehow so that they'd be filled from the Student Info table, that would be fantastic.
If that's not possible, I'd like to make a query or something that uses the New Teacher Registration table and the Student Info table to get a thing of some kind that looks like the original New Teacher Registration table, except with the Students linked up nicely to their student numbers and their teachers.

Again, I really appreciate any help you can provide me!
Thanks again!
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:27
Joined
Jan 23, 2006
Messages
15,362
I don't understand this
I am not able to post a screenshot of the relationships window due to privacy issues.

We're talking about boxes and lines between them.There are no data values involved.

Here's a data model involving teachers, students, classes... to show we aren't asking for anything personal or confidential.
http://www.databaseanswers.org/data_models/student_rosters/index.htm

For your own benefit, do not have spaces in field or table names. And limit field and table names to alphanumeric and "_" only; avoid special chars # etc.
 
Last edited:

sarah23

New member
Local time
Today, 07:27
Joined
Oct 15, 2014
Messages
6
I know, there is no actual personal data in the relationships window. But because the group I'm working for has very tight confidentiality rules, I'm just not comfortable with posting any aspect of my database online. (Even the names of the tables and fields are pseudonyms). I know it sounds ridiculous, but I'd just rather be cautious.
Is the information I've provided sufficient to understand the relationships between the tables?

I actually tried to change the names of my tables and fields to not have spaces, nor # signs, but it threw all my relationships off, the forms didn't work, etc. Do you think I can work around it the way it is?

So, do you think there is any way I could get my data to be presented in this format?
Teacher ID....Teacher Name....St1.....St2......St3...St#1...St#2...St#3
62......................Alice...........A......... B........C.....432.....674.....875
18......................Katie...........D......... E........-.....934.....345......-
4........................Dan............F......... G........-.....134.....734......-
 

sarah23

New member
Local time
Today, 07:27
Joined
Oct 15, 2014
Messages
6
Just an update, I figured it out.
I used the instructions from a thread on Ozgrid Excel Help & Best Practices Forums and a page from Microsoft Office support to make two crosstab queries - one for students, and one for student numbers. Then I made another query to view both crosstab queries at the same time, AND included relevant fields from the New Teacher Registration table. Yay!

Thanks again for your time.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:27
Joined
Jan 23, 2006
Messages
15,362
Glad you have it solved.
 

Users who are viewing this thread

Top Bottom