Table Properties Validation Rules???

[Norman Fritag]

Hi there

Has someone used the table validation rules in Access 2k.
Are the validation rules bound for the current table or is the rules
effective as well for a related table?

Eg:
Table Columns: Clientid,Suburb,DateVisited,Visittype(yes/no, yes = initial
visit, No = follow-up visit)
All four columns are forming the Primary key.

I like for recording doctor visits to implement a rule which would inhibit
the initial visits occur on the same date and follow up visits occur on a
separate date.

What would the table property do in addition to the Primary key??

Where can I find some more information about setting the validation rules in
table Properties?

regards

Norman

 

[Tim Ferguson]

Table Columns: Clientid,Suburb,DateVisited,Visittype(yes/no, yes =
initial visit, No = follow-up visit)
All four columns are forming the Primary key.

Oh my Lord, why? How come the same Client can have several visits of the
same type on the same day, as long as they happen in different suburbs??
Or, to put it another way, how come one client can have two visits on the
same day in the same suburb as long as one is an initial one and the other
is a followup? And do you really mean to have clients having any number of
Initial visits as long as they all happen on different days?

I like for recording doctor visits to implement a rule which would
inhibit the initial visits occur on the same date and follow up visits
occur on a separate date.

Can a patient have different follow up visits on the same date? Can he have
more than one initial visit on the same day in different suburbs?

I don't think this is anything to do with ValidationRules, it's to do with
sorting out your business rules and getting the unique index right. In any
case, a Jet ValidationRule will only apply to values within the same record
-- for triggers that look up other tables or records you'll need a real
rdbms like SQL Server, MSDE, etc.


Hope that helps


Tim F

 

[Norman Fritag]

Tim,
Thanks for your feedback.

The answer to your question is no, none of the scenarios are actually
happening in reality, but for the person who enters the data. They receive
faxes, which some times send several times and they don't look or sort the
information, they just enter.
hence my question.
Thanks again

Norman

 

[Tim Ferguson]

The answer to your question is no, none of the scenarios are actually
happening in reality, but for the person who enters the data. They
receive faxes, which some times send several times and they don't look
or sort the information, they just enter.

I think the answer here is to examine the business processes, not to ruin
the data design. Even though I know very little about your business needs,
here are some random immediate thoughts:

1) how about the user interface actually intercepting the data entry -- so
that the first screen gets the patient id and the date, and then decides
whether to allow a new record or updating of an existing one...

2) If the system is able to tell whether something is an intial visit or a
follow up, this can be taken right out of the hands of the data enterers...

3) Surely Suburb is an attribute of the Patient, not of the Visit -- or do
people move around just to be examined?

4) I would guess that creating an artificial PK (for example, an AN) will
solve several difficulties, even if you still would want to impose a unique
index on (PatientID, DateOfVisit) or something.

That is probably enough for now. I do know that neither (a) the original
question and (b) the way you have your PK now, are the answer to any
business need.

All the best


Tim F