Help for a newbie please! Lots of help ;-(

[missbee]

I only have a few days and need to produce what feels like a monster questionnaire and database in Ms ACcess 2010 version. Lats time I used it was 2001 version so I need a lot of help please.

I want to produce a database to collect data on which subjects my students have studied in a day. So there are 20 subjects, and up to 20 subcategories for each (so 400 variables). There are 30 students so 430 variables.

I guessed the easiest way to do it is to create a database with a record for each day which would have 430 columns. On each day I would want to open a form and click on which students were involved and which subjects and subcategories they worked on. This woud then produce one record.

Where do I start please?

 

[missbee]

I have attached a file to show all the categories. How can I create records whereby I click on which ones I want to select and add a code to identify the teacher aids and date please? is it possible to have 400 variable columns or do I need to rethink how to do it?

 

[missbee]

Oops file attached

 

[JHB]

.. is it possible to have 400 variable columns or do I need to rethink how to do it?

If you mean a table with 400 fields then you need to rethink, because the limit is 255 fields in a table.
I see you have an other question in the "Form" group you really need to get away from the "Excel sheet" thinking.
First you need to create tables for:
- The students
- For the 20 subjects
- For the up to 20 subcategories
so the data can be used in combo boxes, list boxes or even check boxes to avoid misspellings.
Then you need to create tables where the daily registration is done:
A date table, and sub tables for (Students, the 20 subjects, the up to 20 subcategories).
How many table you need, depend on if a student can have more as one subject a day and if a subject can have more as one subcategories a day.
And of cause you need to build the necessaries relationship between the tables.
Then you can create the registration form(s).
How they are put together depend on how you do the registration.
If on daily bases, then the date is the main "control" if it weekly then the student is the "main" control, but it really also depend on how you get the information on, what a student has had for subject and subcategories at a date.
To help you further, we need more information on subjects (what they are called) and subcategories (what they are called) and the connection between them.
Also what data you need to keep track on - I see you have something like "Level" in your Excel sheet, what is that, where is it connected to?

 

[ozzi]

G'Day missbee,

i am only a beginner myself, so take thing i suggest with a grain o salt.

questions:
can a student participate in more than one module of a subject in one day eg. tiraha , L1scibio1, L1scibio2?

can a student participate in more than one Level in a subject in one day eg. Benedict, L1scimisc1, L2scimisc1?

can a student participate in more than one Level in different subjects in one day eg. Ryan, L1techwd1, L2techelc1?

can a student participate in more than one Level in different subjects and or different modules in one day eg. Marina, L1scifree2, L2ss5

what do yo want to do with the E1, E1 etc. Values?

the code for each record is date and what? will it always be BDV?

ozzi

 

[missbee]

Thank you!

I have now created a table for each subject - with the subcategories of the subject in the same sheet.

I see what you mean about getting out of Excel thinking ha ha!

I guess I am going to amend what I am trying to do - what I now need to now how to do is create a table and form with the requirement that I can use lookups to select any one of the codes within the categories, and which ever ones I select are then recorded into the record for that day.

I guess the table I want will almost be be produe, with a text unique identifier, and underneath it the list of codes for that day and student names. I can then create queries for the data selecting which subjects have occurred within those date periods?

 

[missbee]

Hi Ozzi

Thank you so much for replying! Sounds lie you are a lot more knowledged than me.

This is what is so complicated, in any one day 1 or all of the students could do lots of the different subject categories! In one record, I would need to select any of the students who took part in the project, and whether the project covered an E value (E1-E8 - yet another code!), and which subjcte areas i.e. ENlgish L1xxxx maths L1xxxx L2xxxx - ould be may be up to 20 codes in one project - plus up to 40 students!

Perhaps we could create one record per student, for each day but I don't even know how to do this!

So lost and feeling under so much time pressure ;-(

 

[namliam]

How about only making one Question table and having one column "QuestionNumber" that way you can have 10 questions, 100 questions or 1,000,000 questions

 

[missbee]

This is so hard! I used to be able to do all of this 5 years ago.. bah

 

[missbee]

Okay do I need to create queries to make the relation between the tables before the form?

 

[namliam]

you can make the query inside the form as a recordset...

 

[ozzi]

Hi Ozzi

Thank you so much for replying! Sounds lie you are a lot more knowledged than me.

This is what is so complicated, in any one day 1 or all of the students could do lots of the different subject categories! In one record, I would need to select any of the students who took part in the project, and whether the project covered an E value (E1-E8 - yet another code!), and which subjcte areas i.e. ENlgish L1xxxx maths L1xxxx L2xxxx - ould be may be up to 20 codes in one project - plus up to 40 students!

Perhaps we could create one record per student, for each day but I don't even know how to do this!

So lost and feeling under so much time pressure ;-(

sounds to me you might be better off using excel.


worksheet1=template
Column A student names
row 1 subjects

copy as worksheet2=Day one
add data
each cell where required, add subject level, subjectcode, & module no. ie.L1xxxx

rinse and repeat. for day two

in access i see several tables with many to many relationships. and a crosstab query to display the data. and the data entry form would be too complex for me to do in a few days.

sorry ozzi

 

[JHB]

Okay do I need to create queries to make the relation between the tables before the form?

You need to draw the relation ship in the "Relationship" window, it is under "Database Tools" ->"Relationship".

Perhaps we could create one record per student, for each day but I don't even know how to do this!

NO - the "Excel" thinking is taken over again.
You need at least 3 tables:
- A table for the daily registration of a "Student",
- a table for the daily registration of one or more of the "20 subjects" a student can participate per day
- and a table for the daily registration of one or more of the up to "20 subcategories" a student can participate per day, per subject,
And (maybe) a date table. Personally I would prefer a date table but the registration can be done without it!

Could you post your database, (zip it).