subform entry limited to (n) fields?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm building a school database to record weekly marks for my class. I have a
SpellingMArks table and its subfrm on a data entry form. In the table there
are 8 weeks of spelling marks eg fields for 8 marks weeks 1, 2, 3 etc. The
subfrm in datasheet view shows week1 - week8 and the numbers go in fine. When
you tab on the last week the subfrm starts a new line which is passed to the
spelling table as another(unneeded)mark for StudentID 1 with a new Spelling
MarkID to make it unique. I tried the Tab stop property and the cycle to
"current record" after reading a reply on this group. but to no avail.

I posted this a few days ago but have lost the thread on the Australian
site. Thanks to Bruce for his follow up query. Can someone please help.

PeterN
 
I'm building a school database to record weekly marks for my class. I have a
SpellingMArks table and its subfrm on a data entry form. In the table there
are 8 weeks of spelling marks eg fields for 8 marks weeks 1, 2, 3 etc.
Click to expand...

Then I'm sorry to say that your table is INCORRECTLY DESIGNED. Storing
data in fieldnames is not a good idea; for one thing you cannot sort
the spelling results chronologically, you won't be able to go from
term to term, many other problems. A better design would be to use a
one (student) to many (marks) relationship with fields StudentID,
Term, WeekNumber (or MarkDate, date/time) and Mark; add one *row* per
mark rather than one *field*.
The
subfrm in datasheet view shows week1 - week8 and the numbers go in fine. When
you tab on the last week the subfrm starts a new line which is passed to the
spelling table as another(unneeded)mark for StudentID 1 with a new Spelling
MarkID to make it unique. I tried the Tab stop property and the cycle to
"current record" after reading a reply on this group. but to no avail.
Click to expand...

The Cycle should have worked; are you certain that it's set on the
Subform (setting it on the mainform will not affect the subform)?

John W. Vinson[MVP]
 
Thanks John,
I re did the tables for each subject and now have subforms in single sheet
view with combo boxes for the term and week. Now I'm working on the queries
and the statistical calulations. I have a data entry form for each strand of
maths and Eng and the project is moving.
Many Thanks,
PeterN
 
I re did the tables for each subject
Click to expand...

Keep going. You should consider having only one Marks table, not one
table for each subject! Having data in tablenames is, if anything,
even worse than having data in fieldnames. Just add a Subject field to
the marks table.

John W. Vinson[MVP]
 
Yes John,
After several tutorial style books about Access and the Function X Tutorial
which I thought were excellent and trawling the web for education based apps
for Access and talking to you I'm starting to see how the data is organised
differently than a spreedsheet which I have been using for this sort of thing
for years. When I read your response yesterday I looked at the Students and
Classes database from A'97 and its seems to be doing what you suggest.

As it stands now Ive got a Student table (given)

SubjectTable - SubjectId, SubjectName,Strand( in Australia these are things
like reading, writing , etc in the subject of English)

Assignment Table - AssignmentId, Description(this is were Week# Spelling
could go??) and subjectID

and
ResultTable - ResultID, StudentID, AssignmentID and Score(Mark)

How does this look to you?

Also John do you know of any Education based books that would help me. Most
books I flip through now I find I know how to do the Access skills set to a
high level But developing my own application has been challenging.

Thanks again
PeterN
 
Yes John,
After several tutorial style books about Access and the Function X Tutorial
which I thought were excellent and trawling the web for education based apps
for Access and talking to you I'm starting to see how the data is organised
differently than a spreedsheet which I have been using for this sort of thing
for years. When I read your response yesterday I looked at the Students and
Classes database from A'97 and its seems to be doing what you suggest.
Click to expand...

Absolutely. Spreadsheets are NOT relational databases; and applying
spreadsheet logic to a database is pretty sure to get you a bad
design!
As it stands now Ive got a Student table (given)

SubjectTable - SubjectId, SubjectName,Strand( in Australia these are things
like reading, writing , etc in the subject of English)
reasonable

Assignment Table - AssignmentId, Description(this is were Week# Spelling
could go??) and subjectID
Click to expand...

I'd have separate fields for the week number (without using the
special character # in the fieldname) and the text description. I'd be
inclined to include a Date/Time AssignmentDate field though (in
addition to, or even instead of, the WeekNumber); Week 3 is ambiguous
if your database is to be useful during a second term.
and
ResultTable - ResultID, StudentID, AssignmentID and Score(Mark)

How does this look to you?
Click to expand...

Getting there! Looking good actually.
Also John do you know of any Education based books that would help me. Most
books I flip through now I find I know how to do the Access skills set to a
high level But developing my own application has been challenging.
Click to expand...

Not right off hand. Yes, Access database development *is* challenging;
the learning curve is much steeper than that for Excel. There are many
good general books out there, but I don't know of any tuned
specifically to educational applications. Your countryman Allen Browne
(http://members.iinet.net.au/~allenbrowne/tips.html) has some good
links and suggestions, though.

John W. Vinson[MVP]
 
John, I'm at a Road block.
the tbls are designed as follows:

Student:
StudentID, FN, SN, DOB, etc

Subject Table:
SubjectID, Subject (spelling, reading, writing, etc)

Assessment Table:
AssessmentID, SubjectID, WeekNumber.
NB:cut back due to problems at present - I will use the date field.

Result Table:
ResultID, StudentID, AssessmentID, Score

I've played around with the relationships and I can get a query to send out
some good data. But I'm stuck on designing a form that will allow me to see
words not ID numbers for various inputs so that data can be stored. Ideally
each week I'll enter a range of marks for each student. I have this working
in another file but it has the wrong design in table structure. So now I'm
land locked and confused. I'll go back to examples like Northwind and others
tomorrow but any ideas would be appreciated. I'm really in the mud with this.
PeterN
 
Student:
StudentID, FN, SN, DOB, etc

Subject Table:
SubjectID, Subject (spelling, reading, writing, etc)

Assessment Table:
AssessmentID, SubjectID, WeekNumber.
NB:cut back due to problems at present - I will use the date field.

Result Table:
ResultID, StudentID, AssessmentID, Score

I've played around with the relationships and I can get a query to send out
some good data. But I'm stuck on designing a form that will allow me to see
words not ID numbers for various inputs so that data can be stored. Ideally
each week I'll enter a range of marks for each student. I have this working
in another file but it has the wrong design in table structure. So now I'm
land locked and confused. I'll go back to examples like Northwind and others
tomorrow but any ideas would be appreciated. I'm really in the mud with this.
PeterN
Click to expand...

The table structure looks fine. What you need to use is the Form tools
that Access provides, in particular Combo Boxes. The advantage of a
Combo is that it can *store* a numeric ID into the table, while it
*displays* a human-meaningful text value to the user. For example, you
could have a Form based on [Student] with a Subform based on [Result
Table]. Use StudentID as the Master/Child Link Field to automatically
keep it in synch; you won't even need to display the StudentID at all
on either the form or the subform, unless you wish to do so.

You can then create a Query joining [Assessment] (I wouldn't use Table
as part of a table name) to Subject by SubjectID. Try copying and
pasting this query into the SQL window of a new query:

SELECT AssessmentID, Subject, WeekNumber
FROM Assessment INNER JOIN Subject
ON Assessment.SubjectID = Subject.SubjectID
ORDER BY Subject, WeekNumber;

You can use this Query as the Row Source of a combo box on the Result
subform. Set the combo's properties to:

Control Source: AssessmentID
Row Source: <the name of the query you stored above>
Column Count: 3
Bound Column: 1
Column Widths: 0;1;.25

This will *show* the subject name and the week number, but will
*store* the AssessmentID.

John W. Vinson[MVP]
 
John,
I went back to the books to refine my database and I think I am starting
the understand how Access thinks.
The tables are now as follows:
StudentTable – StudentID, ETC

Assessment table – Ass ID, StudentID, Subjectname (linked to a lookup table
with Subject as its only field and Weeknumber.

Result table: ResultID, StudentID, Score.
I have played with various forms and subforms and I can move to a specific
student record and lookup names based on IDs.
For data entry purposes I really need to have a form with name details and
WeekNumber in the main form and a subform listing ALL subjects where a score
can be entered before moving to the next student record rather than a combo
box listing only one subject.
eg:

Name____ Week____
________________________
Subject___________Score
Spelling_______
Reading_______
Writing_______

Also I am having trouble getting queries to work out averages, they present
the scores and week numbers but that’s it. Any ideas.

Thanks again,
PeterN



John Vinson said:
Student:
StudentID, FN, SN, DOB, etc

Subject Table:
SubjectID, Subject (spelling, reading, writing, etc)

Assessment Table:
AssessmentID, SubjectID, WeekNumber.
NB:cut back due to problems at present - I will use the date field.

Result Table:
ResultID, StudentID, AssessmentID, Score

I've played around with the relationships and I can get a query to send out
some good data. But I'm stuck on designing a form that will allow me to see
words not ID numbers for various inputs so that data can be stored. Ideally
each week I'll enter a range of marks for each student. I have this working
in another file but it has the wrong design in table structure. So now I'm
land locked and confused. I'll go back to examples like Northwind and others
tomorrow but any ideas would be appreciated. I'm really in the mud with this.
PeterN
Click to expand...

The table structure looks fine. What you need to use is the Form tools
that Access provides, in particular Combo Boxes. The advantage of a
Combo is that it can *store* a numeric ID into the table, while it
*displays* a human-meaningful text value to the user. For example, you
could have a Form based on [Student] with a Subform based on [Result
Table]. Use StudentID as the Master/Child Link Field to automatically
keep it in synch; you won't even need to display the StudentID at all
on either the form or the subform, unless you wish to do so.

You can then create a Query joining [Assessment] (I wouldn't use Table
as part of a table name) to Subject by SubjectID. Try copying and
pasting this query into the SQL window of a new query:

SELECT AssessmentID, Subject, WeekNumber
FROM Assessment INNER JOIN Subject
ON Assessment.SubjectID = Subject.SubjectID
ORDER BY Subject, WeekNumber;

You can use this Query as the Row Source of a combo box on the Result
subform. Set the combo's properties to:

Control Source: AssessmentID
Row Source: <the name of the query you stored above>
Column Count: 3
Bound Column: 1
Column Widths: 0;1;.25

This will *show* the subject name and the week number, but will
*store* the AssessmentID.

John W. Vinson[MVP]
Click to expand...
 
John,
I went back to the books to refine my database and I think I am starting
the understand how Access thinks.
The tables are now as follows:
StudentTable – StudentID, ETC

Assessment table – Ass ID, StudentID, Subjectname (linked to a lookup table
with Subject as its only field and Weeknumber.

Result table: ResultID, StudentID, Score.
Click to expand...

Your result table won't work as designed: there is a score, and a
studentID, but no indication of what subject or which assessment the
score applies to! I'd suggest that you conflate these two tables:

Assessments
AssessmentID (I hope you're students aren't asses...<g>)
StudentID <link to Students>
SubjectName Text <link to Subjects>
WeekNumber
Score
I have played with various forms and subforms and I can move to a specific
student record and lookup names based on IDs.
For data entry purposes I really need to have a form with name details and
WeekNumber in the main form and a subform listing ALL subjects where a score
can be entered before moving to the next student record rather than a combo
box listing only one subject.
Click to expand...

You can use a *CONTINUOUS* subform showing multiple records; view the
Subform's properties and set its Default View property to Continuous.
If you want to get tricky, base it on this Query:

SELECT Subjects.SubjectName, Assessments.SubjectName,
Assessments.StudentID, Assessments.WeekNumber, Assessments.Score
FROM Subjects LEFT JOIN Assessments
ON Subjects.SubjectName = Assessments.SubjectName
ORDER BY Subjects.SubjectName;

Set the Subform's Master Link Field to

StudentID;txtWeekNumber

and the Child Link Field to

StudentID;WeekNumber

This will let you put an unbound textbox named txtWeekNumber on the
mainform and have it fill in the weeknumber; the LEFT JOIN will
display all the subjects in the subform even before you start filling
in results, and having the SubjectName from both tables will
automatically fill in the Assessments Subject field as soon as you
enter a score.
eg:

Name____ Week____
________________________
Subject___________Score
Spelling_______
Reading_______
Writing_______

Also I am having trouble getting queries to work out averages, they present
the scores and week numbers but that’s it. Any ideas.
Click to expand...

A Totals query will let you get averages. Post back for more info.

John W. Vinson[MVP]
 
John,
We must be getting close. The weeknumber linking works bwtween form and
subfrm and I had flashes of a mark being entered and a subject popping in top
the field box. Oddly enough I had combined the fields you suggested already
into an assessment table. I can keep playing with the subform and query to
get it working but the continueous setting in the subform only produced
discreet field sets each time a set of week, subject boxes were complete. The
score throwing up the subject is great maybe a datasheet view would do it?

I'm also getting the following message on score entry, I can't see why?,
"Jet database engine cannot find a record in <Subjects> table with key
matching fields Assessments.SubjectNames
 
Back
Top