Table Validation Rule with SQL

[wvandenberg]

I am wondering if it is possible to use a SELECT statement in a table validation rule. Alternatively, can or would it be better to use a custom defined function as a table rule?

This is the rule I would like to implement:

IIf([Replicate]=True,[RepSampleID]=(SELECT P.pkSampleID, c.pkSampleID, c.SampleName FROM tblSamples AS P INNER JOIN tblSamples AS c ON P.pkSampleID = c.RepSampleID WHERE (((c.pkSampleID)=[pkSampleID]) AND ((c.SampleName)=[SampleName]) AND ((c.fkpkSiteID)=[P].[fkpkSiteID]) AND ((Int([c].[StartDate]))=Int([P].[StartDate])))),[RepSampleID] Is Null)

Thanks in advance,
Wendy

 

[Hmadyson]

By implementing this rule you would be having duplicate data in your database, since this information is being held in 2 tables. So if you are worried about normalization of data, this is a no no.

I do not necessarily believe in normalization of data, but Access does not allow you to have something that will automatically update the data like this. If you know that people will only be loading data via a form, you can actually write this in VBA code and add it to the AfterUpdate of the fields that need to change in order for this value to change.

Now looking at your formula, I am surprised that it works, since you are selecting 3 fields but setting 1 field equal to it. You can only set one field equal to the value in a select statement if you know that it will only return one record and that it will only return one field.

Please let me know if I can provide more assistance.

 

[lespaul]

I do not necessarily believe in normalization of data

What DO you believe in then?

In some circumstances it is necessary to break the rules of normalization, but those circumstances need to be addressed on an individualized basis. For instance, in an accounting program it would be beneficial to store the beginning or ending balances for each account in order to ease reporting requirements. Should a calculated field normally be stored, no. It can be calculated on the fly in a query.

In a properly normalized database, extracting aggregate data is relatively easy. In a poorly designed database it becomes increasingly difficult to extract meaningful information.

I'm not just speaking from a theoretical standpoint. I work everyday with a database system that has no design or thought put into it at all.

[off topic]There was a very humorous thread I started several years ago about the "Platypus Database" that degenerated quickly. I called it that because it evolved into the mishmash of information that it STILL is today.[/off topic]

You may think, "whatever"...however, I work in a courthouse. Due to the bad design and the difficulties in dealing with information that has been changed and modified in the past 25 years, we have "issues" that affect people's lives. For instance, our court handles the arraignments for all felony arrests in the county. We don't have anything to do with the case after that, it's automatically transferred to the District Court and the Grand Jury has to decide whether to proceed with an indictment. If there is no indictment, then the charges are dropped. But our data shows that you were arrested for a felony and it's still lingering. The case is never "finished" in our court. People call because employers say they have an outstanding felony.

Now some of these problems are bad administrative practices, but some of the problems are because we can't change the way the data is processed and captured and it is processed and captured badly because there was no thought EVER to how the database should be designed.

There's duplicate data EVERYWHERE! We have your address in the defendant master file. But if you are sentenced to probation, you now have another record where your address is stored. So the next time you are seeing your PO you tell her your new address, so SHE has it and it's updated for all Probation notifications. But let's say a court date is changed and you're sent a new Notice of Hearing. Because you didn't go to Customer Service and change your address there TOO, your Notice goes to your old address. Now you get a warrant for your arrest for Failure to Appear. You've broken the conditions that were set in your sentencing. Now you may be going to jail...because of the court's bad database design.

In the next three years we will be completely restructuring the database and I plan on following all the rules of normalization. I expect that this will significantly increase the confidence in the statistics we have to report and decrease the amount of time required to extract the information because the queries will be much easier to develop.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Hmadyson]

Les, thank you for taking my quote out of context. In fact I started my posting by saying that she should not do what she is trying to due because of normalization. I think that there are some cases where normalization slows down your database, that is all. And I hope that wvandenberg has thought about that when she decided that she wants to do what she is saying since that would be denormalized.

 

[wvandenberg]

...since this information is being held in 2 tables

The data is actually all contained within one self-referencing table. My goal is to restrict the data that can be entered in the RepSampleID field (this field identifies the parent record) based on the other fields in the record.

The fields look like this:


pkSampleID fkpkSiteID SampleName StartDate fkpkSampleTypeID Replicate RepSampleID
6967 169 AW010 31-May-95 1 FALSE
6968 169 AW010 31-May-95 3 TRUE 6967
6969 169 AW010 31-May-95 3 TRUE 6967
6970 169 AW010 07-Aug-95 1 FALSE
6971 169 AW010 07-Aug-95 3 TRUE 6970
6972 169 AW010 07-Aug-95 3 TRUE 6970
6973 169 AW010 11-Oct-95 1 FALSE
6974 169 AW010 11-Oct-95 3 TRUE 6973
6975 169 AW010 11-Oct-95 3 TRUE 6973
6976 169 AW010 27-Feb-97 1 FALSE
6977 169 AW010 27-Feb-97 3 TRUE 6976
6978 169 AW010 27-Feb-97 3 TRUE 6976

So, for a replicate sample or child sample (SampleType=3) the RepSampleID or parent sample has to meet the following criteria:
the child [StartDate]= the parent [StartDate]
the child [fkpkSiteID]= the parent [fkpkSiteID]
the parent [Replicate]=False

Is it possible to implement these rules at the table level?

Wendy

 

[rac2]

I dont think you can achieve these rules with CONSTRAINTS.

I think you must fillin these values using VBA as Hmadyson suggested. Constraints can be used for rules that prevent rows being added with incorrect values; defaults can be used to fillin values when rows are added; but looking at values that are being added, comparing them to existing rows and calculating appropriate values to fillin the new row is done by triggers. In SQL Server the logic can be written in Transact-SQL and would be able to achieve the rules you describe.

In Access, I am guessing, a trigger is a block of VBA code that runs when some event happens. I dont know how to do that, but I think that will be the approach you will need to take.

So, can it be implemented at the table level? Yes, if you consider event handlers and procedural coding as table level tactics. No, if you limit the meaning of that idea to SQL constraints, formats, defaults, and so forth.

 

[wvandenberg]

Thanks very much for everyone's comments. I'll attack it from the form event procedure angle.

Wendy