Need idea on how to query?

[Daybreak]

Access 97, No VB. Have run out of brain power. How might you suggest I set the criteria in a query for this, or should this be done in the report. I am counting duration days on the &quot;entire&quot; length of a case as it passes through the different court levels, (rpt: with one line for each level of court the case has been through & it days count).<br>
<br>
Field to work with is [court]<br>
Note:<br>
I do NOT want the record if [court] does not change from level 1 to an upper lever.<br>
<br>
I want to ONLY pull records: IF the [court] changes from level 1, to level 2, 3, 4, ect.<br>
BUT,<br>
Then if [court] does change level 1, I need to go back and count the days at level 1. <br>
<br>
For testing purposes, in the [court] criteria I entered Not Like &quot;District&quot;, and received all cases that had changed from that level, but in counting purposes I need to also count the days & this level.<br>
<br>
Any and all help is gracefully accepted. Thank you!

 

[famousb]

ok, so when the level changes from level 1 to say level 2, does that write a new record for that case, or does it just overwrite part of the existing case (ie, level becomes 2 instead of 1)?<br>
and what field(s) tracks the dates in question (ie, start date, end date)?<br>
these factors influence how you would write the criteria<br>
<p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[Daybreak]

Thank a million for the response.<br>
<br>
Yes, everytime the court level changes a new line is written for the case number.<br>
<br>
A field in the query tracks the days at a court level:<br>
ex: Days: [closed_date]-[reviewed_date]<br>
<br>
Anxiously waiting to hear back.

 

[famousb]

so, if you get a new line with every change in court level, how is your &quot;Days&quot; field calculating it from court 1 to court X?<br>
<br>
is it currently just counting the days it was at that level, and then you are summing up all levels for a case number on your report?<br>
this is what i'm assuming.<br>
<br>
<p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[famousb]

so, theoretically, the only way you'd have multiple records with the same case number is if it moved from court 1 to court X, correct?<br>
<br>
you could do a &quot;Find Duplicates Query&quot; on the case number, this would automatically limit it to those that have multiple court levels. then you could have your summation fields built into that query, feeding the report.<br>
<br>
let me kow if that won't do what you're looking for. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[Daybreak]

Yes, a new line for every change in court level per &quot;case nbr&quot;<br>
ex:<br>
at court level &quot;supreme&quot; days calculated= closed date minus reviewed date <br>
Like<br>
1 pass thru records case #123 court=district closed dt 2/1/00 reviewed dt 1/1/00<br>
2 pass thru records case #123 court=circuit closed dt 2/29/00 reviewed dt 2/1/00<br>
3 pass thru records case #123 court=supreme closed dat 4/1/00 reviewed dt 2/2/00<br>
<br>
&quot;case nbr&quot; footer sums up all the levels for that case.<br>
<br>
Hope this helps you understand my problem. Thanks<br>
<br>

 

[Daybreak]

our mssgs crossed paths...I was receiving your &quot;find duplicates qry&quot; comment as I sent mine.<br>
<br>
so.....find duplicates qry.....might work.....have never set one up......so would I run this 'find duplicates qry' off my existing qry? And what might some of the criteria look like?<br>
<br>
I'm new at some of this. This company had another company build huge huge databases, which I am trying to design reports for. Thanks<br>
<br>

 

[famousb]

as for the Find Dups Query, you can simply go click on the &quot;New&quot; button in the query window, then select the Find Duplicates Query Wizard. it will waslk you through it.<br>
i suggest building this query off of the raw data, and then do your calculations in this new query (you'll see what i mean once you get into it, it's pretyt much the same as any other query).<br>
<br>
if you need more help let me know <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>