Dynamic grid of checkboxes?

[Jay in PA]

Hi,

Here's my challenge... Part of an application I'm developing will
support creating a schedule for interviews. I need to create a form
that will allow the user to see which interviewers are available that
day and which candidates need to be interviewed. The user will then
match interviewers to candidates. It's a many-to-many relationship, as
each candidate may be interviewed by multiple interviewers, and each
interviewer will interview multiple candidates on a given day.

With that said, I'm something of a newbie and have no idea how to
depict this in a form. What I'd *like* to do is have a dynamic grid of
checkboxes, with available interviewers in the columns and the day's
candidates in rows. I can see that a continuous form could take care
of creating a row for each candidate, but I don't know how to
dynamically set up the columns of checkboxes to represent the
interviewers.

Can anyone help me figure out how to do this? And if there's a better
way, I'd be happy to know what that might be.

Thanks to anyone who is willing to help!

Jay

 

[KARL DEWEY]

Have you considered an Option Group?

 

[Klatuu]

The first and most important thing to do to accomplish this it to correctly
normalize your data. Since you have a many to many between the Interviewers
and Candidates, you are going to need a junction table to resolve the many to
many. That would be a table that would contain a record for each
Interviewer/Candidate combination.

I am not sure whether your check boxes relate to the interview schedule or
the questions being asked, I can't be too specific, but in either case, this
table should also include the fields the check boxes will be bound to.

To get the data on the form, you can go either of both ways so that you have
an interviewer and show all the candidates for the interviewer or show all
the interviewers for a candidate. You would do this with a form/subform. If
the first case, the main form would be bound to the interviewer and the
subform would show the candidates or for the reverse, you can use the
candidate as the main form and the interviewers in the sub form.

You can do this with queries as your record sources to join the junction
table with the interviewer or candidate table.

 

[Jay in PA]

Dave,

Well, I do have the first step right in that there is a junction
table, but I did not have a record for every possible interviewer/
candidate combination--I just had records for every combination in
which an interview was scheduled.

If I am reading correctly, what you're suggesting sounds like I need
to update the junction table with records for every interviewer, for
every candidate. So when a new candidate comes into the system, I add
N records to the junction table, one for each interviewer. And then,
it sounds to me like I'd add a True/False field to indicate whether
there is an interview or not for that particular combination. Makes
sense.

That solves the question of how to represent the relationships in the
database. And I understand the use of a form/subform to represent the
interviewers and candidates. But I think I need a little more help
walking through the second half of your solution. Let's say I want the
interviewers in the columns, and the candidates in rows. The
interviewers would be in a subform for each candidate, correct? But
how do I bind the checkboxes in the subform so that the correct number
appear to represent the interviewers?

Forgive me if I'm being unclear, but I'm just completely stumped
beyond the conceptual idea of using a subform, and need a little more
help to see the path clearly.

Thanks for your help.

Jay


Why do I need to join the junction table to the interviewer or
candidate table, aside from pulling in more than just their IDs?

And how do I get the controls in the subform to

 

[Klatuu]

I did not do well at explaining. You have it right. There needs only to be
a record for each interview scheduled. You have that right.

I would not suggest you show all of both sides. If you are working by
interviewer, you need only to show the record for the interviewer in your
main form. In the subform, you would show all the candidates the interviewer
is going to interview. And if working by candidate, it would be the other
way around. You might want to add a filter that would filter the schedule by
a date or a date range, but I would concentrate on getting the basics done
first.

If you have more questions, please post back.

 

[Jay in PA]

Thanks for the further explanation.

My challenge is that it will be easiest for my users to visualize the
process if they can see at once who the interviewers and candidates
are, and who is being interviewed by whom. My thought is that an
interviewer x candidate array of checkboxes would be the right
interface to present the information.

Problem A is that I don't know how to set up the subform (representing
all possible interviewers for each candidate) so that it presents a
horizontal array of checkboxes representing a variable number of
interviewers.

Problem B is that, once I do have this all set up, I would need to
save it back to the junction table, and I need some guidance on that,
too.

Thanks again for your ongoing assistance.

Jay

 

[Klatuu]

Since you are on East Coast time, you may not get this until Monday. By
then, I will have had time to mull this over and perhaps come up with an idea
for you.

 

[Jay in PA]

Thanks so much, Dave--I really appreciate it! I'll look back in on
Monday!

 

[jacksonmacd]

Do your users need to update the data, or would it be sufficient for
them simply to visualize it? If it's the latter, then this approach
might work :

Create a cross-tab query of the junction table. You should specify the
fieldnames in the query definition so that the query always has
consistent names.

Create a form to display the checkboxes. Bind the form to the crosstab
query. Bind each of the checkboxes to a field in the crosstab query
(this is why you need to specify the fieldnames in the crosstab query
so that you KNOW what the fieldnames will be.)

Of course, since its a bound to a crosstab query, the recordset will
not be updateable.



If you need your users to update the query, it's a lot more
complicated. You could create a temporary table based on the crosstab
query, then bind the form to that temporary table. Your form would
need a "Save" button that would take the current contents of the
temporary table and populate them back into the junction table.
Messy.... (I've done something similar in the past, but it's a lot of
work).

AFAIK, there is no automatic way to perform this action (ie, take
normalized data, denormalize it for more convenient display, then
normalize it again)

 

[Peter Hibbs]

Jay,

What you are describing is something like a Spreadsheet layout (rows
and columns, etc). This can be easily done in Access by using a Flex
Grid control which shows the table data like a spreadsheet and also
(depending on how the data is stored in the tables) can be edited in
the grid and saved back into the tables.

However, this would require a fair amount of VBA code to implement but
if you think you can handle that, then have a look at this site for
some examples :-

http://www.rogersaccesslibrary.com/...?TID=511&SID=az7a325343b3cca94765abd2d3e178a8

HTH

Peter Hibbs.

 

[Jay in PA]

Hi,

Thanks Peter and jacksonmacd for your suggestions. I'm not averse to
using a flexgrid control, and I'm willing to do the VBA to get it
done, but the control doesn't seem to come with Access 2007--at least
not in my installation, and I don't know if that will create
compatibility issues for my clients.

The crosstab query sounds promising, even though, as noted, users will
not be able to update it directly. I guess the answer is that I need
to come up with a different interface for my users to accomplish their
goal. If you all have any ideas for such an interface, I'd appreciate
your thoughts.

Thanks!

Jay

 

[Peter Hibbs]

Jay,

You will need a copy of the Flex Grid control which you would then
need to install and register on your client's PC. If you download the
Flex Grid Demo file that I mentioned you will find documentation there
that explains how to do that using VBA code.

If you can create a CrossTab query which returns the data you need to
display then you can copy the query data directly into a Flex Grid
control with just a few lines of code. Have a look at the Cross Tab
Demo database at :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=1467163adzcf2ff6c866b65a8772d953

for an example. This method has the added advantage that, unlike a
standard crosstab query, you can add other rows and/or columns to the
grid to show row and/or column totals (or whatever). Also you can
dynamically re-program the control to show the correct number of rows
or columns when the number of interviewers/interviewees changes.

Also, to update the tables (probably the 'join' table in your case),
you could click in a cell and, using the row and column numbers,
identify which record in the join table is to be changed and use an
Update query to modify the relevant field in the required record. Then
just refresh the Flex Grid display to show the amended data.

Peter Hibbs.