A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Store activities



 
 
Thread Tools Display Modes
  #1  
Old May 22nd, 2010, 08:09 AM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Store activities

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 a
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


  #2  
Old May 22nd, 2010, 01:09 PM posted to microsoft.public.access
Mark Andrews[_4_]
external usenet poster
 
Posts: 169
Default Store activities

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" wrote in message
...
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 a
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


  #3  
Old May 22nd, 2010, 06:32 PM posted to microsoft.public.access
Larry Linson
external usenet poster
 
Posts: 3,112
Default Store activities

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.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Lars Brownies" wrote in message
...
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 a
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




  #4  
Old May 23rd, 2010, 03:24 AM posted to microsoft.public.access
dan
external usenet poster
 
Posts: 1,408
Default Store activities

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" wrote:

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 a
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


.

  #5  
Old May 23rd, 2010, 01:11 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Store activities

"Larry Linson" schreef in bericht
...
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


  #6  
Old May 23rd, 2010, 01:12 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Store activities

Thanks.
Lars

"Mark Andrews" schreef in bericht
...
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" wrote in message
...
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 a
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


  #7  
Old May 23rd, 2010, 04:13 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Store activities

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

Lars Brownies wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

  #8  
Old May 23rd, 2010, 07:38 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Store activities

Good idea! Thanks,
Lars

"KenSheridan via AccessMonster.com" u51882@uwe schreef in bericht
news:a8704b524cad3@uwe...
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

Lars Brownies wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.