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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Rule to prohibit someone from working on a project or in a time sl



 
 
Thread Tools Display Modes
  #1  
Old October 22nd, 2009, 02:35 PM posted to microsoft.public.access.tablesdbdesign
Craig
external usenet poster
 
Posts: 506
Default Rule to prohibit someone from working on a project or in a time sl

Hi

I have a database table where you enter records of people with specific
tasks in certain time slots.
For example:
Record 1: Jim Smith Project A Tues 10-12pm
Record 2: Nancy Jones Project B Wed 2-4pm
Record 3: Nancy Jones Project B Fri 10-12
Record 4: Jim Smith Project A Fri 10-12

Can I create a rule that prohibits entering records of certain people at
specific time slots? For example, you cannot enter Jim Smith for Project A
on Monday at 10-12?

Thank you!

Craig
  #2  
Old October 22nd, 2009, 02:57 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Rule to prohibit someone from working on a project or in a time sl

"Craig" wrote in message
...
Hi

I have a database table where you enter records of people with specific
tasks in certain time slots.
For example:
Record 1: Jim Smith Project A Tues 10-12pm
Record 2: Nancy Jones Project B Wed 2-4pm
Record 3: Nancy Jones Project B Fri 10-12
Record 4: Jim Smith Project A Fri 10-12

Can I create a rule that prohibits entering records of certain people at
specific time slots? For example, you cannot enter Jim Smith for Project
A
on Monday at 10-12?

Thank you!

Craig


Not at table level, no, but you should be using queries and forms to
manipulate your data. If you use a form you can put some validation code in
its Before Update event to determine whether or not the data is saved.
Having said that, it might be worth considering using a combo box on a form,
the combo could be set to show only available times for the user to choose
from.

Any mileage in that for you?

Keith.
www.keithwilby.co.uk

  #3  
Old October 22nd, 2009, 03:57 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Rule to prohibit someone from working on a project or in a time sl

If you mean you do not want a duplicate of 'Jim Smith for Project A on Monday
at 10-12' then create an index in the table and set to unique.
--
Build a little, test a little.


"Craig" wrote:

Hi

I have a database table where you enter records of people with specific
tasks in certain time slots.
For example:
Record 1: Jim Smith Project A Tues 10-12pm
Record 2: Nancy Jones Project B Wed 2-4pm
Record 3: Nancy Jones Project B Fri 10-12
Record 4: Jim Smith Project A Fri 10-12

Can I create a rule that prohibits entering records of certain people at
specific time slots? For example, you cannot enter Jim Smith for Project A
on Monday at 10-12?

Thank you!

Craig

  #4  
Old October 22nd, 2009, 07:23 PM posted to microsoft.public.access.tablesdbdesign
Tedmi
external usenet poster
 
Posts: 141
Default Rule to prohibit someone from working on a project or in a time sl

From the data you provide, there is no way to determine that Jim Smith
Project A Mon 10-12 is disallowed. If that's what you really meant, you need
a table of disallowed times per user/project combo. Or did you mean that
times when users are already assigned are not available for new assignments?

In either case, I would recommend that you split the start and stop times
into separate fields, to handle this: if Jim Smith cannot be assinged on Mon
10-12, then presumably he also cannot be assigned Mon 9-11. If the two times
are in the same field, then 10-12 9-11 and the assignment will be
allowed.

As Keith Wilby says, this is done at the form level, in the AfterUpdate
event of the form that creates a new assignment.
Say your input form is named fAsgn and contains the text fields txtEmpID,
txtDayOfWeek, txtStartTime, txtStopTime, bound to fields in tblAsgn with the
same names, but without the txt prefix.
I'm assuming that you want to disallow any assignments whose times overlap
existing assignments for a given person, regardless of project.

Disclaimer - Untested Code!
Create a query like this:

SELECT EmpID FROM tblAsgn WHERE
Forms!fAsgn!txtEmpID=EmpID AND Forms!fAsgn!txtDayOfWeek=DayOfWeek AND
(Forms!fAsgn!txtStartTime = StartTime AND Forms!fAsgn!txtStartTimeStopTime
OR
Forms!fAsgn!txtStopTime StartTime AND Forms!fAsgn!txtStopTime=StopTime)

Say you name it qFindOverlap

In the fAsgn_AfterUpdate proc:

If DCount("EmpID", "qFindOverlap") 0 then
MsgBox "whatever"
Cancel=TRUE
End If

An alternative is to build the criteria (the WHERE clause without the word
WHERE) in a string (say sCrit) within your proc, and:
If DCount("EmpID", "tblAsgn", sCrit)) then...
However, this has two drawbacks - functions of this type run slower than
queries, and building the string in code requires some complex punctuation
to concatenate variables with literals.

Good luck!
-TedMi

"Craig" wrote in message
...
Hi

I have a database table where you enter records of people with specific
tasks in certain time slots.
For example:
Record 1: Jim Smith Project A Tues 10-12pm
Record 2: Nancy Jones Project B Wed 2-4pm
Record 3: Nancy Jones Project B Fri 10-12
Record 4: Jim Smith Project A Fri 10-12

Can I create a rule that prohibits entering records of certain people at
specific time slots? For example, you cannot enter Jim Smith for Project
A
on Monday at 10-12?

Thank you!

Craig



  #5  
Old October 22nd, 2009, 07:55 PM posted to microsoft.public.access.tablesdbdesign
CraigH
external usenet poster
 
Posts: 50
Default Rule to prohibit someone from working on a project or in a time sl

Hi,
Well Keith is correct you will be doing this through a form and not the
table - and it didn't look like is was only that you didn't want dupes for a
person. But there are questions that need to be asked before anything can be
considered.

The big problems is how do you pick the times - if it is something that is
standard the gets put in a table Like Tues 10-12, Mon 10-12, Mon 12-2 then
Karls method of non dups will work but I don't think that is correct - for a
meriod (sp) of reasons. i.e. what if you want 1-3, or 10-1.

The time should be a start time and and end time. But beyond that lets talk
about what you said "A rule that prohibits entering records of certain people"

Basically for that you wan't another table that has those conditions. You
will modify this to what your exact needs are (i.e. if you realy do have
slots laid out instead of real time)

PersonID ProjectID Days(use the day number) StartTime EndTime
John Smith A Mon (2) 10:00 AM 4:00 PM
John Smith Tues 8:00 AM
5:00 PM

John Smith can't be assigned to project A between 10 and 4 on mondays and no
projects on Tuesdays.

This would be compaired to the entry as Keith said in the before update
event when you put in the times.


"Craig" wrote:

Hi

I have a database table where you enter records of people with specific
tasks in certain time slots.
For example:
Record 1: Jim Smith Project A Tues 10-12pm
Record 2: Nancy Jones Project B Wed 2-4pm
Record 3: Nancy Jones Project B Fri 10-12
Record 4: Jim Smith Project A Fri 10-12

Can I create a rule that prohibits entering records of certain people at
specific time slots? For example, you cannot enter Jim Smith for Project A
on Monday at 10-12?

Thank you!

Craig

 




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.