Different result same query

[Home]

I have a database that is connected to our server at work via OBDC. I have a
query that I run on a fairly large table. I the query is built on another
query and there are last of and sum of fields in the queries.. I get
different results in the number of records returned each time I run the
query.


SELECT dbo_tbl_claims.prov_id, dbo_tbl_claims.client_id,
Last(dbo_tbl_claims.srvc_dt) AS LastOfsrvc_dt,
Last(dbo_tbl_claims.srvc_code) AS LastOfsrvc_code,
Sum(dbo_tbl_claims.approved_amt) AS SumOfapproved_amt
FROM dbo_tbl_claims
GROUP BY dbo_tbl_claims.prov_id, dbo_tbl_claims.client_id
HAVING (((Last(dbo_tbl_claims.srvc_dt)) Between [forms]![community
support]![DS] And [forms]![community support]![De]) AND
((Last(dbo_tbl_claims.srvc_code))="H0036"))
ORDER BY Last(dbo_tbl_claims.srvc_dt);


Any help is appreciated

Craig

 

[Jerry Whittle]

Well it's always possible that someone else is adding or deleting records
between the times that you run the query.

It's also possible that the ODBC connection is timing out and only returning
a subset of the records at times.

Here's what I think is the real problem though:
HAVING (((Last(dbo_tbl_claims.srvc_dt))

The Last function is meaningless UNLESS the data is sorted. You do have an
ORDER BY clause however it also uses the Last function. Last just picks out
the last record. Unless you sort by the srvc_dt field, then any record could
be "last". Same goes for the First function.

If the srvc_dt field is an actual date/time datatype, I suggest using the
Max function. It will select the latest date/time no matter how the records
are sorted. Min does the same for the earliest record.

Another possibility is the the data in the the form's community support text
boxes are different each time it's run.