How do YOU validate your results?

[MSherfey]

Some of my queries return results with tens of thousands of records.....sometimes hundreds. I think the query was built correctly. The query makes logical sense to me, but keep in mind how inexperienced I am with Access.

What is the best way to validate the results of a query? How do you validate your data?

 

[CraigDolphin]

It depends how complex the query is. Simple queries without outer joins etc I have done so often that I am VERY confident in the result set.

But for more complex queries, I usually sub-sample specific results/values. And I especially look for situations where I know there could potentially be issues if I did it wrong (like dealing with situations where there is no matching record in a related table/tables etc).

At some point, you need to have faith in the process. That said, I believe there are some glitches that can occur because of bugs in the jet database engine itself. For example, http://allenbrowne.com/bug-10.html So, it does pay to cross check your query results with your own expectations.

 

[Brianwarnock]

At the age of 67 and retired after 40+ years in IT I'm old fashioned I DESIGN test data, in my day that skill was considered as important as any programming skill, I do realise that things have changed and as Criag says simple queries for the experienced can work directly on live data, they usually work or fail obviously, but I'm convinced that the number of IT projects that we read of failing is due to that lack of designed testing.

Brian

 

[CraigDolphin]

I should point out that I'm not an IT professional and I'm not saying my approach is the best way...it's just what I do.

Brian....I'm interested...any chance you'd outline something of the process you'd use in designing test data?

 

[Brianwarnock]

I should point out that I'm not an IT professional and I'm not saying my approach is the best way...it's just what I do.

Brian....I'm interested...any chance you'd outline something of the process you'd use in designing test data?

I don't want to duck this but after a few years out an off the cuff answer that doesn't start with cliches is a bit difficult.
I remember that when I started I was actually in Data Processing and then after a time the name was changed to Information Technology. I think the idea was that we were supposed to not just process the data but examine it from all angles so that we understood everything about it, and thus we were able to process it better and more completely.

But a simple example of test data design occured on a thread I replied to earlier this week to do with pay days, there was a requirement to count the number of pay days between dates, now the guy did not say whether the dates could be in the same month or not I programmed and tested that my solution would cope with this , lagbolts , which looks much neater, I believe will not, also all the test data should cover all date eventualities, the live data you throw at it may not.
Also the testing of the error situations is important, which means test data design starts with the system specification.

Most importantly the point about desgned test data is that your results are predicrtable and of a manageable size.

Brian

 

[MSherfey]

Brian, that sounds like a great idea. How would this work:

Take the amount of permutations of the data (some fields can have multiple values IE License v Maintenance, New v Renewal, etc) and have a record specifically for each.

Customer1-----New--------License-------$100
Customer1-----Renewal----License-------$110
Customer1-----New--------Maintenance--$101
Customer1-----Renewal----Maintenance--$111
Customer2--etc.

Of course, there would be a lot more test records since I have about 30 fields and about 15 of them have multiple (some up to5) options.

Is this too much or is it what you were thinking about? Also, any tips on getting the data created? By hand would take soooo much time. Finally, is there value to assigning a unique $ value based on the permutation or is that going too far?

I'm sorry for the tons of questions, but I'm very intrigued by your idea of test data.

 

[CraigDolphin]

So, does this mean that you design test data in such a way as to challenge the assumptions implicit in the data model?

 

[Brianwarnock]

May be not a good time to take thsi on after the wine with the evening meal, I have that feeling of digging a hole for myself, but knowing Craig is a decent guy lets try.

I designed test data to test all of the facts of the specification, which I presume is the same as the data model, in addition I designed data to test that I handled situations not covered by the data model, if that were possible, a simple example waould be a date not present or an end date earlier than a start date, points frequently not considered in specs, and maybe not in the current live data.

In most cases test data design is not difficult, the aim, and advantage of it , is that you can ensure that the data covers all possibilities, the results are predicted and therefore checkable, and the volume is manageable. Obviously this can mean more than one test.

Brian

 

[CraigDolphin]

Thanks Brian. I didn't mean to put you on the spot but I have learned that it's much smarter to benefit from the advice of people who have been there and done that, rather than undertaking to learn the same lesson via the school of hard knocks.

Admittedly, I seem to be more able to take the lesson to heart via the second route, but I am trying to change that behaviour!

Enjoy the wine!

 

[wiklendt]

May be not a good time to take thsi on after the wine with the evening meal, I have that feeling of digging a hole for myself, but knowing Craig is a decent guy lets try.

I designed test data to test all of the facts of the specification, which I presume is the same as the data model, in addition I designed data to test that I handled situations not covered by the data model, if that were possible, a simple example waould be a date not present or an end date earlier than a start date, points frequently not considered in specs, and maybe not in the current live data.

In most cases test data design is not difficult, the aim, and advantage of it , is that you can ensure that the data covers all possibilities, the results are predicted and therefore checkable, and the volume is manageable. Obviously this can mean more than one test.

Brian

blush i usually just hope to heck my queries are right! i only notice something funny when my brow furrows at a result, and i go "that's funny... is that supposed to be like that?" then i go back and check and lo and behold i've left out something vital... test data would be nice, but (for me at least, and for the data i have) it's hard to predict what all the instances might be.

never had to do any date stuff yet though. this is mostly IN and NOT IN table/query kinda stuff.