Insert from Table A to Table B with criteria

[jschping]

Hi,

I need to create a schedule for teachers to proctor finals.

I have 2 tables: Table A has the classrooms that need proctors and the times
of the test. Table B has a list of teachers and the times that they work in
the school.

I need to write a program to fill Table A, using the teachers in Table B.
But for each row in Table A, I need to check: 1) does the teacher work during
the time period of the test, 2) are they already slated to proctor in a
different room at that time.

This isn't a difficult algorithm, I think. I just need to iterate through
each row of Table A, and for each row go through all the rows of Table B,
checking the criteria. If they are fine, then insert that teacher's name, and
go to the next row of Table A.

I just don't have a clue how to code it in VB.

Please help!

Thanks so much!

 

[Jeff Boyce]

Another approach, perhaps more 'relational', would be to create a third
table for "assignments".

Then, you could use your routine to create records in that third table,
assigning a teacher to a classroom.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[jschping]

Perhaps, but I want to place it in the original table because then I can
easily export it to an Excel spreadsheet that other staff can understand.

So how could I do this procedure? Can someone please help me with the code?

Thanks!

 

[Jeff Boyce]

Sorry, but forcing a table to look like a spreadsheet is not good use of the
tool. The solution you've decided on is a little like trying to use a
chainsaw to drive nails. Can it be done? Probably. Is it a good idea?
Nope!

If you need a set of data to export to Excel, use a query to gather that
data. You can join the three tables together in a query and select the
fields you wish to export.

If you want to use Access the way it was designed, you need to use
well-normalized data, not 'sheet data.

Best of luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[jschping]

OK. I hear you. So how would I code it to take the two tables and create the
third?

I tried to create a module and add a subroutine but I get the error of
"Invalid use of property" and the yellow line is on my declaration. I can't
even get off the ground!

Here's what I have so far. As you can see, I am a novice at VB programming:

Option Compare Database



Public Sub Make_schedule()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Schedules.Date, Schedules.First,
Schedules.Last FROM Schedules")

With rs
Do Until .EOF
If !First = "" And !Last = "" Then
Set !First = "Mark"
Set !Last = "Mustard"
End If
..MoveNext
Loop
..Close
End With

End Sub


Thanks!!

 

[Jeff Boyce]

I'll defer to the VBA experts on the code, but will point out that you may
be able to use queries to help yourself with a portion of the task.

If you ran a query that showed 'uncommitted' teachers and their availability
time, you could use that as a source for making assignments. And a query
that shows all 'empty' proctoring times/places.

If I were to cobble up some pseudo-code describing the process, it would
look something like (untested):

1. find all uncommitted teachers and their availability times {in code,
you'd make a recordset of the query's results}
2. take the first one and create a new assignment record (but only if
that teacher's time matches a needed/proctor time)
3. take the next uncommitted teacher/time and create an assignment record
(but only if ...)
4. repeat until the end of the list of uncommitted teachers
5. start at the top, but only if there are any remaining
needed/proctoring times, and only if there are any uncommitted teachers.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[roccogrand]

As Jeff suggests, you really need a third table (at least). If you don't
normalize your database, you'll have problems later as it grows in tens and
tens of tables, queries, reports and forms. Been there.

To see how an educational app can be designed, take a look at Ryan Stephens
and Ronald Plew's "Database Design" from SAMS Publishing.

I use the Update Query in similar situations as yours. I also suggest that
you review the capabilities of Make Table and Append Query. I have found
them to be extremely powerful tools.

HTH

David