By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

Building a single form for multiple entries

P: 41
I have a table that contains data for schools. THe table structure is as follows:

buildingid
gradeid
teacher_count
student_enroll
year

My question concerns the form used to add data. Right now I have a continuous form that requires the user to populate data for each grade using a combo box. For example, a school may have 20 grade classifications that the user has to pull from a combo to add the data. Is is possible to build a form that would have the grades listed on the form? Example when the user opens the form for School A all the possible grades are listed and the user indicates the counts for each grade.
Mar 8 '08 #1
Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Assuming you have a table called Building which contains details of your buildings, a table called Grades which defines the grades you mention, and that the table you list is called TeacherGradeCounts, you could use an SQL Insert query like this (changing the table names if these are not the same as yours):
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO teachergradecounts ( [year], buildingid, gradeid, teacher_count, student_enroll )
  2. SELECT [which year?] AS TheYear, building.buildingid, grade.gradeid, 0 AS teacher_count, 0 AS student_enroll
  3. FROM grade, building
  4. ORDER BY [which year?], building.buildingid, grade.gradeid;
This query has a parameter [which year?] which pops up a request for the year. User enters the year and the query populates the teachergradecounts table as shown below (with dummy values for bulding ID and grade):
Expand|Select|Wrap|Line Numbers
  1. year buildingid gradeid teacher_count student_enroll
  2. 2007 B1 G1 0 0
  3. 2007 B1 G2 0 0
  4. 2007 B1 G3 0 0
  5. 2007 B1 G4 0 0
  6. 2007 B1 G5 0 0
  7. 2007 B1 G6 0 0
  8. 2007 B1 G7 0 0
  9. 2007 B2 G1 0 0
  10. 2007 B2 G2 0 0
  11. 2007 B2 G3 0 0
  12. 2007 B2 G4 0 0
  13. 2007 B2 G5 0 0
  14. 2007 B2 G6 0 0
  15. 2007 B2 G7 0 0
  16. 2007 B3 G1 0 0
  17. 2007 B3 G2 0 0
  18. 2007 B3 G3 0 0
  19. 2007 B3 G4 0 0
  20. 2007 B3 G5 0 0
  21. 2007 B3 G6 0 0
  22. 2007 B3 G7 0 0
  23. ...
You could store the query under a suitable name then run the query when needed from a command button on your data entry form to populate the table. If your table is defined with the correct keys you can only run it once for any one year. Trying to run it again (without deleting all the rows that were put in the last time) would be rejected by Access as a result of trying to duplicate existing key values.

-Stewart
Mar 8 '08 #2

P: 41
THanks Stuart. I will try it this weekend.

Patti
Mar 9 '08 #3

P: 41
Stuart:

One question... In the grade table I have a field called school_level which indicates classes that pertain to a specific type of school, i.e. elementary, middle, and high. Can you show me how to adjust your query to make sure that the proper grades are pulled by building and school_level type. Otherwise I will have all grades for each school. THanks again for all your help.

Patti
Mar 9 '08 #4

Expert Mod 2.5K+
P: 2,545
...I have a field called school_level which indicates classes that pertain to a specific type of school, i.e. elementary, middle, and high. Can you show me how to adjust your query to make sure that the proper grades are pulled by building and school_level type. Otherwise I will have all grades for each school. ...
Hi Patti. The most correct approach would be to add your school table to the query, but as I don't know what fields it has, or its relationship to the buildings table, the simplest approach would be to add a WHERE clause to select the correct type of school in response to another user-entered parameter, [school level?], like this:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO teachergradecounts ( [year], buildingid, gradeid, teacher_count, student_enroll )
  2. SELECT [which year?] AS TheYear, building.buildingid, grade.gradeid, 0 AS teacher_count, 0 AS student_enroll
  3. FROM grade, building
  4. WHERE grade.school_level = [school level?] 
  5. ORDER BY [which year?], building.buildingid, grade.gradeid;
-Stewart
Mar 9 '08 #5

P: 41
It worked beautifully! Thank you for the timely lesson with SQL.

Patti
Mar 9 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.