Form for entering grades for children (1 Viewer)

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
Hi everyone,
I am making a database for a children's counseling group and have made a form for case entries or case updates. I am stuck on making a form and table that allows multiple entries(classes) per child(unique ID) wanted to know if this is possible since this part of a larger project that I have been working on for the past week.
 

isladogs

MVP / VIP
Local time
Today, 18:44
Joined
Jan 14, 2017
Messages
18,186
Use two tables for this (possibly more) with fields similar to this

1. TblChild
ChildID - unique PK field
LastName
FirstName etc

2. TblCase
CaseID -unique PK field
ChildID - foreign key (FK)
CaseDate
CsseDetails
CaseAuthor etc

Now use a form and inform arrangement with ChildID as the master/child linking field
This will allow a one to many relationship.
The main form (single) contains the child info
The subform has the case info and can have multiple entries
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:44
Joined
May 7, 2009
Messages
19,169
maybe this simple example can give you an idea on how to do it.
 

Attachments

  • Grading.zip
    31.6 KB · Views: 50

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
Sorry for the vague approach. I am going to try the suggestions. Thank you all for the help.
 

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
I am looking at the grading table and how did you allow for duplicates or set ChildID as a foreign key. The end result is what I need. Also, how would I properly create a search box for returning the ChildID for grade updates? I have attached the worked I have done. I would like to use the Child's Name and Number box to set the ChildID for the other tables. Am I even on the right track?
 

Attachments

  • Case Updates.zip
    1.2 MB · Views: 42

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
Also, is it possible to apply a filter on the entire form and subforms using the search button in the header?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:44
Joined
May 7, 2009
Messages
19,169
I have to be honest, its way off course.
what do you mean by Name Plus?
is the form for 1 household?
does those grades in general not to specific child?
 

isladogs

MVP / VIP
Local time
Today, 18:44
Joined
Jan 14, 2017
Messages
18,186
I have to agree.
There are many issues with your table structure including
1. It is a bad idea to have spaces or special characters such as - or # in table/form names or field names
2. You have used ID as the PK field in each table which will cause confusion
3. You have 3 grade tables for different age groups two of which have identical fields. Merge these and add an extra field to identify age group if needed
4. Using subdatasheets will dramatically slow down your database
5. Your Name Plus table has 5 age fields. If these are actual age in years this shouldn't be stored as it changes. Use DOB instead and calculate age in a query
If on the other hand these represent age ranges such as 7-11 then you should have one age field and a separate text field as an identifier
6. Another table has 4 fields for child name and number. Same issue as above.

I'll stop there. Sorry to be critical but if you stick with this design your database will be a major pain to work with. Suggest you read up on database design including normalisation of data

Good luck
 

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
Thanks for the critiques and support, I felt like I was creating a monster and not something efficient. The form is per household which may have multiple children. I got rid of the subdatasheets and merged two tables. I tried to follow tutorials on how to do relationships for the grades and classes. Seems a bit complicated but if it works then I'll learn to love it. I'll upload my update once I finish.
 

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
Ok, I attempted to remake the grades form with the condensed tables but now it wont show up in form view or layout view :confused:. I did nothing to the relationships but move the boxes up and down to make them appear somewhat neat. I am taking a class on database concepts but its week 5 out of 8 and we just started making SQL scripts of tables. Is it ok if I use this thread for this project?
 

Attachments

  • Case Updates Database.zip
    1.1 MB · Views: 45

Gasman

Enthusiastic Amateur
Local time
Today, 18:44
Joined
Sep 21, 2011
Messages
14,046
If we are talking about form Grades for Classes K-12.

That is because the query for the form record source is not producing any records.?

That appears to be due to the fact that there are no records in the tables.?

Get that working first.

Ok, I attempted to remake the grades form with the condensed tables but now it wont show up in form view or layout view :confused:. I did nothing to the relationships but move the boxes up and down to make them appear somewhat neat. I am taking a class on database concepts but its week 5 out of 8 and we just started making SQL scripts of tables. Is it ok if I use this thread for this project?
 

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
Ok, I searched all over and it seems I have to build the classes form without so many tables.
 

isladogs

MVP / VIP
Local time
Today, 18:44
Joined
Jan 14, 2017
Messages
18,186
You are welcome to look at the DEMO version of my School Data Analyser app which includes grades as well as many other features.
Its a large commercial app but the DEMO is a free working version. For obvious reasons, its locked down and is an ACCDE file so you can't view the code.
However it may help you refine your ideas.
If its any possible use to you, the demo can be downloaded from http://www.mendipdatasystems.co.uk/school-data-analyser/4584605482

As for your form, if you get no records, perhaps you should use an outer join rather than an inner join between one or more tables. If you have too many tables the form is likely to be read only. Normally use a form for the main table data with one or more subforms for the other table(s)
 

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
Awesome, I got the form to work as two different subforms but I need to put functional buttons now that control the form as a whole piece like navigation controls.
 

isladogs

MVP / VIP
Local time
Today, 18:44
Joined
Jan 14, 2017
Messages
18,186
Glad to hear you're making progress. What changes did you make to do so?
If you have linked your subforms to the main form, navigating on the main form should automatically apply to the subforms as well.

So put your nav buttons on the main form. I usually have four buttons First, Prev, Next and Last. Occasionally I add two more allowing me to jump 5 records forward or back.

Good luck with the next stage
 

J99K

Registered User.
Local time
Today, 11:44
Joined
Jul 13, 2019
Messages
11
I broke the table into two tables and used FamilyNumber as the foreign key. I wish I could have a drop down list of the classes to see the grades of each.
 

Users who are viewing this thread

Top Bottom