Building a single form for multiple entries

[patriciashoe]

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.

[Stewart Ross]

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):

INSERT INTO teachergradecounts ( [year], buildingid, gradeid, teacher_count, student_enroll )
SELECT [which year?] AS TheYear, building.buildingid, grade.gradeid, 0 AS teacher_count, 0 AS student_enroll
FROM grade, building
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):

year buildingid gradeid teacher_count student_enroll
2007 B1 G1 0 0
2007 B1 G2 0 0
2007 B1 G3 0 0
2007 B1 G4 0 0
2007 B1 G5 0 0
2007 B1 G6 0 0
2007 B1 G7 0 0
2007 B2 G1 0 0
2007 B2 G2 0 0
2007 B2 G3 0 0
2007 B2 G4 0 0
2007 B2 G5 0 0
2007 B2 G6 0 0
2007 B2 G7 0 0
2007 B3 G1 0 0
2007 B3 G2 0 0
2007 B3 G3 0 0
2007 B3 G4 0 0
2007 B3 G5 0 0
2007 B3 G6 0 0
2007 B3 G7 0 0
...

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

[patriciashoe]

THanks Stuart. I will try it this weekend.

Patti

[patriciashoe]

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

[Stewart Ross]

...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:

INSERT INTO teachergradecounts ( [year], buildingid, gradeid, teacher_count, student_enroll )
SELECT [which year?] AS TheYear, building.buildingid, grade.gradeid, 0 AS teacher_count, 0 AS student_enroll
FROM grade, building
WHERE grade.school_level = [school level?] 
ORDER BY [which year?], building.buildingid, grade.gradeid;

-Stewart

[patriciashoe]

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

Patti