How To Fill in form or table with button...

[bbarkman]

This is somewhat specific; I would like to create a record for a student,
click a button and have a subform or table fill in automatically with all
classes that student will be taking during their college career. I could do
this for each class, but there are 30+ classes that a student takes times
150+ students.

I already have the students in a table and the classes in another table. I
just can't determine the best way to connect the two for this purpose.

I have a database already running for student/class/grade/attendance
information. This is for a side task to help me show in a report
(ultimately) one student in all classes in the program, and on the same page,
which classes they have completed and which ones they have left.

My database is based on an old grade/attendance database template from the
office area of microsoft.com.

All suggestions will be greatly appreciated!

 

[Jeff Boyce]

You did say "all suggestions..." <g>

Instead of filling up a table with "student X class" records, use a query
that does this. You can compare your table of "attendence" records against
the query.

All possible combinations of rows from table A and table B is called a
Cartesian Product. To get it, add Table A and Table B to your query design
window, but DON'T join them. Pick a field from each (uhm, student, and
class?!) and run the query. If you have 150 students and 30 classes, your
query should return 4500 rows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bbarkman]

Thanks. I was able to create the query with names and all classes. I wound
up with around 5,000 records. I have one more field to add that I call
"complete". It is a check box I use for when a student passes a class.

At the moment, if I tag a student as completing a class, a check mark
appears for all records in the new query. If I join them, which you said not
to do, only the completed ones appear in the new query.

How can I run the new query to show all records regardless of whether a
class has been completed or not?

The new query (your way) has 3 fields in it: Last name, first name, and
class. Hopefully, there can be a 4th field (complete, yes/no).

Once again, thanks for your help.

 

[Jeff Boyce]

The purpose of the new (cartesian product) query was not to allow for
"completed", but to show all possible combinations. I understood you to say
that you had another table that recorded attendance. Joining that other
table plus the cartesian product query would let you build a new query to
show all classes not yet completed by students.

Or maybe I don't understand your data fully enough yet...

I was offering an idea that would let you avoid creating a bunch of empty
records in a table, since it sounds like you can tell who finished a course
via the attendance info.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bbarkman]

I do have a table that stores attendance info, but if I query that, the new
query will basically multiply by 10. I'd be up around 50,000 records. I
have another table that contains the check box I told you about; to just tell
me if they completed the class. That box is in a table known as "Students
and Classes". At the end of a quarter, I check the box for each student in a
class that passed it.

I have the cartesian product query running with the students and all classes
(about 5,000 records and it looks good). I tried to add the "completed"
field to that query and the result was over 10,000 records with every check
box filled in whether I filled it in or not. I figured some of them would
not be checked. (I even set the unique records property to "yes" on the
query.)

I know I'm not doing something, I just haven't found it yet. Maybe I should
be querying empty check boxes instead.

Hope this helps.

Regards, bbarkman

 

[Jeff Boyce]

It may be that I'm just not seeing the big picture yet.

Let me try stepping back & paraphrasing what I think I understand...

You have a all list of students.

You have a list of all classes the students would need to take.

You have a list of "attendance" (class sessions attended by students).

You want to know which classes the students have taken.

You want to know which classes the students have not taken.

Is this a reasonable summary?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bbarkman]

Yes.

In the meantime, I found a temporary solution. I used a make-table query to
run the cartesian product like you said. Then, I opened this new table in
design view and added a field called "complete". To test this, I took one
student transcript and checked off the classes that had been taken in the
table.

I then ran a second query to gather the new table. Finally, I created a
report based on the second query and got a one-page document which listed all
classes for that student with the completed ones checked off. The blank
boxes now indicate what the student still needs to take.

This probably is not the most efficient way to do this since I had to go
into the table and check the appropriate boxes. I figured I would have to
check them in a form anyway, so I haven't bothered with that yet. At any
rate, it's crude, but is works.

Now we have to do some data entry to "complete" past classes for students.
Once that is done, I can complete each student on a quarter by quarter basis.

Overall, what you are asking is correct:
We have a all list of students.
We have a list of all classes the students would need to take. (this is
where my original "complete" check box is located.)
We have a list of "attendance" (class sessions attended by students).
We want to know which classes the students have taken.
We want to know which classes the students have not taken.

I am interested to know your solution, as I still think mine is the hard way.

Thanks again for helping me!

 

[Jeff Boyce]

I don't have a "solution" per se, but a general direction...

If, as I suspect, your "attendence" table has a field for ClassID
(otherwise, how would you know which class was attended?!), you could use a
query against your attendence table to build a list of all the classes each
student attended (I'd use a unique query to get each class once).

Then I'd compare the cartesian query against the unique classes attended
query to see which ones were NOT attended.

Don't know if that will make anything easier for you.

Glad you found a solution ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bbarkman]

You have been extremely helpful. Thanks again!

I don't have a "solution" per se, but a general direction...

If, as I suspect, your "attendence" table has a field for ClassID
(otherwise, how would you know which class was attended?!), you could use a
query against your attendence table to build a list of all the classes each
student attended (I'd use a unique query to get each class once).

Then I'd compare the cartesian query against the unique classes attended
query to see which ones were NOT attended.

Don't know if that will make anything easier for you.

Glad you found a solution ...

Regards

Jeff Boyce
Microsoft Office/Access MVP