Conditional Validation Rules

[ratdude010]

Hello,

I'm new to this forum, so I apologize if I'm posting the wrong area/location.

I'm currently attempting to create a Database for work. Essentially, I'm attempting to track employees PTO days (Sick, Personal, and vacation time). I was originally going to use the accounting software, but it doesn't allow for all those PTO fields and they need to be separated as they're treated differently.

On my current access table, I'm attempting to track accrued time and time used. My goal is to have a conditional validation rule, based upon the response in another column. I'm going to have a code column. I want certain codes to require a negative value, while other codes would require a positive value. For example, I want accrued vacation time to require a positive value and used vacation time to require a negative value in the days used/accrued column.



I could always create a separate table for used/accrued time, but it starts to get more cumbersome doing that - I'd rather keep it to one table.

Any advice on this would be much appreciated?

[ranman256]

I do the validation at form entry using this code.

If user gets no errors then they can leave the form. (usu at Save button click)
or you can put it in Form_Unload to make sure they cant leave until errors are fixed.

sub btnSave_Click()
if IsValidForm() then docmd.close
end sub


Public Function IsValidForm(Optional pbAll As Boolean)
Dim vMsg
vMsg = ""
Select Case True
   Case IsNull(txtName)
      vMsg = "Client Name is missing"
   Case IsNull(cboMach)
      vMsg = "Machine is missing"
   Case IsNull(cboLineLoc)
      vMsg = "Line Locations is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, kREQD
IsValidForm = vMsg = ""
End Function

[June7]

Not sure what the issue is. Certainly can have "conditional" validation. It's just an IIf() expression or If Then Else VBA. Have you made an attempt?

Actually, all numbers can be entered as positive then when calculating balance use a conditional expression to set value as negative if condition is met. Example:

Sum([used/accrued] * IIf([code] = "used", -1, 1))

BTW, advise not to use punctuation/special characters (underscore only exception) in object naming. So if you actually have a / character in "used/accrued", better would be UsedAccrued or Used_Accrued.

[ratdude010]

I added a field to try to simplify things. Instead of combining the Accrued and vacation time codes into one field, I separated them for simplicity. Now, I have a code for what the PTO item is (Vacation, Sick, Personal) and a separate Yes/No field for the accrued time.

I'm now looking to have a conditional field based upon the response to the Yes/No accrual field. My fields are now ID (Primary Key), Employee (lookup field to employee contact), Starting Date, Ending Date, Description of Activity, PTO CODES, Days, accrued (yes/no).

I tried the following validation code, but it fails to block incorrect values: "IIf([Accrued]="yes",[Days]>0,[Days]<0)"

I apologize for my lack of database knowledge. I'm just an accountant (I guess that's just a database of another kind), trying to get some sort of tracking system together for PTO. Might try to learn visual basic to try blocking the back end on my databases, but that's a topic for another day.

[Micron]

I interpret all that as an indication that you're using lookup fields in tables as well as creating multiple fields for the same attribute(s). Specifically,

but it doesn't allow for all those PTO fields

(lookup field to employee contact)

I'd wager these tables are not normalized based on certain clues in those posts. It also seems that you're working directly in tables and trying to do things in them that you just can't. If any of that is correct, IMO you're off to a bad start and might want to review db normalization in an effort to determine if that is the case.

[ratdude010]

The accounting software SAGE 50, only allows for Vacation and Personal Time. I need Personal, vacation, and sick time tracking.

So far, I have 5 tables. Employee contact information, Employment details, Paid time off adjustments, PTO Codes, Work Codes.

Employment details has a lookup field to the employee under employee contact information, same with paid time off adjustments. Employment details has a lookup reference to the work codes table. Paid time off has a reference/lookup to the PTO codes table.

I tried to map it below.
Employee Contact Info->Employment details<-Work Codes
Employee Contact Info (same as above)->Paid time off adjustments<-PTO Codes

Should I break apart the accrual and used PTO into separate tables? Last time I did that, I ran into problems by having an overly complicated database.

[June7]

IIf([Accrued]="yes",[Days]>0,[Days]<0) doesn't work because relational operator cannot be dynamic. Could do something like:

> IIf([somefield]=True, 50, 0)

Unfortunately, that syntax will not meet your requirement.

Probably need to use VBA behind form.

I agree with Micron comments.

[Micron]

Should I break apart the accrual and used PTO into separate tables?

I'm going to say no based on my understanding of your process, which probably fairly weak.
If you created relationships, consider pasting a pic in your next post. It might help a lot.
If you mean you have combos in table fields that do lookups, consider one source (likely you could find lots more to back this up)
http://access.mvps.org/access/lookupfields.htm

If you're not saying that, then the link doesn't apply but would be good to know about anyway. There's lots more one needs to know in order to avoid a lot of hair pulling, IMO.
I have more links that are good for beginners if you want them. The key thing for your issue, I think, is making sure your tables are designed right, otherwise you will continue to struggle after you solve this particular problem.

[ratdude010]

Here's a map of my database so far. I used the employee contacts as the root of everything. Hopefully my screenshot posts. Sorry the screenshot showed up twice. I can't figure out how to delete one.

[Micron]

Thanks for posting that. Helps with design insofar as anyone can understand the process, but doesn't reveal if you're using lookup fields in tables.

some design tips that will make your job easier:
- no spaces in any object names (fields are objects too)
- no special characters either, save for underscore (you have /)
- ID makes for an ambiguous name when you need to know which one it is; better is ptoaID; better yet is ptoaIDfk and ptoaIDpk or something similar
- don't use reserved words (e.g. Description) which isn't very descriptive, oddly enough

I'm used to corporate db's that held employee info and never needed their contact details for any sort of departmental process. Perhaps you do, but if not, suggest trimming to the bare essentials such as names and employee numbers. If there is an empl table in a corporate db you can link to, might use that instead.

If Days is the difference between start and end date fields, that makes it calculated data, which 99.9% of the time you should not store. You calculate in forms and reports. If you alter one of the values intrinsic to the calculation, your stored calc is wrong and now have to worry about that.

I don't see the need for table Employee details. Each field seems to be an attribute of the entity (employee). Better than Active (yes/no) is an InactiveDate field. If it's null, they're active; if it's not they're inactive, plus you have the date that took effect.

If you need any history for employee (e.g. work code is "this" but before it was "that" then that design won't support it.

That's all from me for now. I'd have to review the first posts to see what else to glean from it.
TO ALL ELSE: don't stop chiming in on my account. The more input the better.

EDIT
pk and fk mentioned above refers to primary key and foreign key. If those terms are unfamiliar, I strongly encourage you to review db normalization if you haven't already.
Also, just noticed that accrued might be a calculation as well. Seems to me that the way to calculate a particular type of pto would be a TOTALS query that groups by pto type. Each occurrence of a pto type for anyone would be a separate record in the table. At first I thought you had a field for every type, but it seems that is not the case.

[ratdude010]

Thanks for the very helpful reply Micron - the response was extremely helpful

I suppose based on the reply that I should in fact split my PTO adjustment table. I should have one table for time employees take off and another for PTO adjustments?

The problem is that an employee taking time off would have a definite beginning and ending to the period of time they take off, which would allow for a calculation. An adjustment, however, wouldn't have a specific time period/calculation. For example, an employee takes from May 3rd to May 6th off, that can be calculated (3 days). An adjustment that accrues employee pay for the year (ie. adds it) wouldn't have a definitive time period, if that makes sense.

[Micron]

I should have one table for time employees take off and another for PTO adjustments?

business process isn't clear, but I doubt it. However, I don't understand the distinction being made. Whether you accrue or use, would it not be an adjustment to time owing? Or is this not about time owing?

I want accrued vacation time to require a positive value and used vacation time to require a negative value

That's one way, but you should control this with forms and it still isn't clear if this is about entering data directly in tables. Another way is to do the math on (sum WHERE PTO = A) minus (sum WHERE PTO = B) where all values are positive numbers.
If that doesn't help, I think you need to clarify your process in simple terms; best to not use jargon that likely won't be understood. Perhaps like
"salary employee accumulates time owing by working over time. Adjustments are made based on using this accumulated time for "personal" or other reasons that are in a table that defines the reasons."

[ratdude010]

My end goal is to be able to track how much PTO time employees have left/used and on what date. So if Tyler gets 10 days accrued at the beginning of the year and uses 3 days in May, he will have 7 days left to use later in the year.

When I add days to Tyler's balance at the beginning of the year, I don't want to specify a date range. For example, lets say Tyler gets 10 days added to his PTO balance at the beginning of the year. I can't use a calculated date range. In order to get the calculated field to work, I would need to specify the first date coming after the second date to get a positive balance (ie. January 11th to January 1rst).

Determining employee time off is easy. Ie. I take a vacation from May 3 to May 5th. The PTO accrual doesn't pertain to a specific date. Also, to add 10 days of PTO, I would have to have a calculated field of something like January 10 to January 1rst to get the opposite of PTO used.

Hope this clarified the problem with only using a calculated field for the database. Not sure how else I could create it, other than splitting it up.

[mike60smart]

Hi

If you create a Form based on the Employee table with a field for PTO Entitlement

Then have a Subform for recording Days Leave taken, then it is quite easy to show Total days used and the Balance of Days Remaining.

[Micron]

lets say Tyler gets 10 days added to his PTO balance at the beginning of the year.

If there's no expiry, there's no need to carry over - unless you're doing something you shouldn't (or need to but we don't understand it). If it never expires, it is the difference between accruals and 'disbursements' for anyone since the beginning to the end of their involvement in the 'program'.

I would need to specify the first date coming after the second date to get a positive balance

I think you would not. You can use ABS function (I think that has already been mentioned here?). Regardless, if there's no need to carry over, this problem goes away?

Hope this clarified the problem with only using a calculated field for the database. Not sure how else I could create it, other than splitting it up.

Not for me, but then that's on me, maybe.
If all you want is validation of input data, that's what forms do. Still not clear if you're using forms for data input/viewing or not (asked in post 5 - perhaps I missed the answer).