Editing a subform

[BobbyOH]

I work for a non profit referral service that places seniors in paid jobs.

I've developed a database that lists each applicant by category.

An applicant, tblApplicants.AppID, can be listed many times,
tblApplicantDetail.DetailID, by Category, tblCategories.CategoryID (like
Carpenter, Plumber, Painter etc because some people can do all of these
things.)

People (seldom the same person so no primary key here) call looking for a
Carpenter or Plumber etc. to do work in their homes. The primary key for
jobs listed is tblJobDetail.JobID. We give them 3 or 4 names, "referred",
and later try to find out if any of these applicants were "selected".

For each Job Detail, I'd like to have a subform that shows all the
applicants by category but also be able to have a check box so I can simply
put a check next to the names we "referred" and after go back to the Job
Detail and use a check box for the one that was "selected" and possibly
store that info in a table like tblApplicantsByJob for statistics.

Is this a possibility using subforms or am I way off base? Are there any
other suggestions or recommende websites?

Thanks...

Karen

 

[Michel Walsh]

Hi,


It seems you need a junction table,

JobID, ApplicantID, IsSelected (Boolean)


Use that table for your subform, each JobID having as many records (and
ApplicantID) as you referred. You can then "check" the third field, if the
applicant has been selected. Keep the same table, for your stats, no other
table needed... just add the criteria:

SELECT JobID, ApplicantID FROM junctionTable WHERE IsSelected

and that would be equivalent to the extra table you were trying to create.


Hoping it may help,
Vanderghast, Access MVP.

 

[BobbyOH]

Thanks, I'll give that a try.

Hi,


It seems you need a junction table,

JobID, ApplicantID, IsSelected (Boolean)


Use that table for your subform, each JobID having as many records (and
ApplicantID) as you referred. You can then "check" the third field, if the
applicant has been selected. Keep the same table, for your stats, no other
table needed... just add the criteria:

SELECT JobID, ApplicantID FROM junctionTable WHERE IsSelected

and that would be equivalent to the extra table you were trying to create.


Hoping it may help,
Vanderghast, Access MVP.