jackd1
asked on
Display two dimensional array as a grid in an access form
I am trying to create a form in Microsoft Access 2010 using three dimensional arrays I have built in a Visual Basic module.
The project is to display a student timetable given the student code.
Using the data from various tables and queries I have built three arrays for classes, teachers and rooms, with the arguments being student number, day and period.
Eg teacher(50, 4 , 5) would be the teacher for student 50 on day 4 in period five.
room(25, 2 , 6) isthe room for student 25 on day 2 in period 6.
I now want the user to be able to enter a student code into a form and retrieve the weekly timetable for that student into a 5 x 7 grid. Each cell of the grid would contain a teacher id, room id and class id.
Any suggestions as to the sort of control and how I would fill it?
Also how can I save my calculated arrays?
The project is to display a student timetable given the student code.
Using the data from various tables and queries I have built three arrays for classes, teachers and rooms, with the arguments being student number, day and period.
Eg teacher(50, 4 , 5) would be the teacher for student 50 on day 4 in period five.
room(25, 2 , 6) isthe room for student 25 on day 2 in period 6.
I now want the user to be able to enter a student code into a form and retrieve the weekly timetable for that student into a 5 x 7 grid. Each cell of the grid would contain a teacher id, room id and class id.
Any suggestions as to the sort of control and how I would fill it?
Also how can I save my calculated arrays?
ASKER
Ok yes I should have said access VBA. Is there a better solution than Access?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well Visual Basic (the product) does have a grid control and that might be nore applicable to your display situation but I doubt if you will find anything simple that handles your storage requirement. The main server databases have multi-dimensional analysis tools but if you are not already familiar with them, I suspect that they require a lot of learning to use them.
I can appreciate that you will feel that you have structured your data in memory in a very tidy manner- I would agree with that - but I doubt that it is of much practical use and that you will find it simpler to deconstruct your arrays into more conventional two-dimensional records.
I will post a call for other experts to look at this Q.
I can appreciate that you will feel that you have structured your data in memory in a very tidy manner- I would agree with that - but I doubt that it is of much practical use and that you will find it simpler to deconstruct your arrays into more conventional two-dimensional records.
I will post a call for other experts to look at this Q.
ASKER
Thanks Fyed for the crosstab suggestion. It seems I need a separate query for each of teachers, rooms and classes.
I am now in the process of trying to combine that info on one form. Any ideas?
I am now in the process of trying to combine that info on one form. Any ideas?
If you can you post a sample database with your data structure and records for students, teachers, and classes, that would be great. The Classes table probably doesn't have any private info, but the students and teachers probably do, so don't include real names, SSNs, DOBs, address, phone number or that kind of thing (Teacher1 - Teacher200) (Student1 - Student200).
ASKER
The database is attached. Thanks for your time.
timetable---Copy--2-.accdb
timetable---Copy--2-.accdb
ASKER
Problem solved!
I concatenated the data for the class, teacher and room into one variable and Bob's your uncle.
Thanks Fyed for the start.
I concatenated the data for the class, teacher and room into one variable and Bob's your uncle.
Thanks Fyed for the start.
There is no simple answer to your question about controls. I think you are in for a lot of work in achieving a grid presentation. The only options you have are a datasheet (as a form or subform), or a listbox, or a bundle of individual textboxes.
And as for saving an array, I rather suspect that your source data might already be in the form that you would have to use to save the array contents.
There is no way to save the array as a 'unit'.
You would build records that have fields..
student ,day, period ,class, teacher, room.
And you would have to rebuild the arrays if you needed to use them.