Store activities

[Lars Brownies]

For monitoring recruitment actions per applicant I need to store about 50
actions per applicant. The activity table would look like:

ApplicantID*
ActivityID*
DateStart
DateEnd

My concerns are:
1. The table would have an enormous amout of records if I had let's say
30,000 applicants.
2. When an new applicant would be added I would have to automatically add
all the 50 action records, otherwise the user would have to pick them
manually. Also some jobs require other applicant actions than others.

A not normalized table would seem easier but would require a table design
change every time there was a new type of action, which is also not
desirable.

How would you deal with such a situation?

Thanks, Lars

 

[Mark Andrews]

That looks right. Perhaps more fields in the activity table (status of
activity, notes etc...).

The activity table is usually the table with the most records in a CRM
system.

 

[Larry Linson]

I do not see a valid reason for pre-populating the Activity Table with a
copy of every possible activity for each applicant, especially as you state
that not all applicants records will need all the activities.

When you add an activity record for an applicant, provide a ComboBox from
which the user can choose the appropriate activity. A ComboBox is ideal,
because with the default AutoExpand option, you can begin typing and it will
automatically scroll the drop-down list -- you don't have to scroll through
all the earlier options to reach the one you want.

You can include VBA code in the AfterUpdate event to validate that the newly
entered record does not duplicate an existing record for that applicant, if
that is appropriate. But, that code can, and should be, sufficiently
"intelligent" to allow the same activity at different dates or times, or in
combination with other distinguishing characteristics.

 

[Dan]

We have a db with a couple of large tables, but your situation may be
different, so this is just a possibility. Ours we split the db, repathed,
created ACCDE, the front end was copied to all the clients, the back end was
placed on our server. It did take some of the load off of one unit doing all
the work. Again your siutation may be different, but was similar to ours in
having large tables. Just a thought...


Dan

 

[Lars Brownies]

I do not see a valid reason for pre-populating the Activity Table with a
copy of every possible activity for each applicant, especially as you
state that not all applicants records will need all the activities.

Thanks. I thought of it more as a checklist to save users work.

When you add an activity record for an applicant, provide a ComboBox from
which the user can choose the appropriate activity. A ComboBox is ideal,
because with the default AutoExpand option, you can begin typing and it
will automatically scroll the drop-down list -- you don't have to scroll
through all the earlier options to reach the one you want.

The activities depend on the job they apply to. Maybe I can offer combo
values depending on the job.

Lars

 

[Lars Brownies]

Thanks.
Lars

That looks right. Perhaps more fields in the activity table (status of
activity, notes etc...).

The activity table is usually the table with the most records in a CRM
system.

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

 

[Lars Brownies]

Good idea! Thanks,
Lars

Better, I'd have thought, would be a multi-select list box in which you
can
select any number of activities to be assigned to an applicant
simultaneously.
The list box can be limited to the activities relevant to the selected
job,
but first you'd need a JobActivities table with columns JobID and
ActivityID
to model the many-to-many relationship between jobs and activities. The
list
box's RowSource property would then be something like:

SELECT Activities.ActivityID, Activity
FROM Activities INNER JOIN JobActivities
ON Activities.ActivityID = JobActivities.ActivityID
WHERE JobID = Form!cboJob
ORDER BY Activity;

where cboJob is a control in the form whose value is the JobID of the
selected job and Activities is a table of all Activities. The first
column
of the list box would be hidden by setting its ColumnWidths property to
something like 0cm;8cmIn cboJob's AfterUpdate event procedure requery the
list box with:

Me.lstActivities.Requery

You can then put some code in a 'Confirm' button's Click event procedure
to
loop through the list box's ItemsSelected collection and insert a row for
each selected activity into the table which models the relationship
between
applicants and activities by executing an SQL INSERT INTO statement. What
you do about the start and end dates is for you to decide; you might want
to
insert the same dates into each row at this stage, or you might want these
entered individually via a form at some later stage in the process.

You could if you wish include a 'Select All' button to assign all
activities
for the selected job with code like this:

Dim n As Integer

For n = 0 To Me.lstActivities.ListCount - 1
Me.lstActivities.Selected(n) = True
Next n

Similarly, to deselect all:

Dim n As Integer

For n = 0 To Me.lstActivities.ListCount - 1
Me.lstActivities.Selected(n) = False
Next n

Ken Sheridan
Stafford, England