Evaluating field: previous against current values

[antihippy]

Hi there,

I'm having difficulty getting a query to work.

I have a query which returns a set of infringements. I'll explain: I work for an auditing company and if you fail at an item for more than two years you fail your audit.

The results set I have is a query based on multiple table joins so I won't immediately post the SQL (it's quite complex); instead I'll give you an example of what the results look like:

MemNumber AssesstmentID Version Infraction
123 725 1 2.1
123 726 2 2.1

In this small example this scheme member has had the same infraction for two consecutive years. How do I evaluate this results set to get a positive?

Version nmuber refers to the years version of the scheme rules.


1 = 2004 'version'
and
2 = 2005 'version'

I've a feeling that just getting to this stage has fried my brain.

 

[GingerR]

you could use dcount, put a calculated field in a query:

=iif(DCount("AssessmentID","AssessmentTableName","MemNumber = " & MemNumber & " and Version = " & Version - 1 & " and Infraction = " & Infraction)=1,"FAIL","PASS")

so the dcount will count 1 record if there's a record that matches MemNumber, Infraction and Version - 1. If all three match and one record is returned, your calculated field will return FAIL, otherwise it will return PASS. To filter out just FAILs, put FAIL in the criteria row.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[antihippy]

I decided not to go with Dcount. It seemed to take forever ...

What I did instead was to create two seperate queries, one for version one and the other for version two, then link them with a third table. From this last result set I took the membership numbers. Seems to work ok, although it's not the most elegant of solution.

Thanks agains GingerR.