Missing Values

  • Thread starter Thread starter Terry Hornsby
  • Start date Start date
T

Terry Hornsby

Is it possible to write a query to find missing values from a table field?
For example, I have a range of cheques which SHOULD all follow in sequential
order (the range is pre-sorted). However, some may have been overlooked &
not input.

I am assuming that, other than code, a query solution may offer itself, but
am willing to look at any other options.


Many thanks,


Terry Hornsby.
 
Terry,

Will something like this do it?...

SELECT [ChequeNo]+1 AS Missing
FROM Cheques
WHERE (DMin("[ChequeNo]","Cheques","[ChequeNo]>'" & [ChequeNo] &
"'")=[ChequeNo]+1)=0

(assumes ChequeNo is text data type)

- Steve Schapel, Microsoft Access MVP
 
Hi,


Bring the table twice, the idea would be to find those "i" that have not
matching "i+1" in the other reference:


SELECT a.FieldName+1
FROM tableName as a LEFT JOIN tableName as b
ON a.FieldName+1 = b.FieldName
WHERE b.FieldName Is Null


Hoping it may help,
Vanderghast, Access MVP
 
Thank you to both Steve & Michel for two great suggestions. I won't be able
to try them out until my return to work on Monday, but they both look doable
& I'm intrigued by the two very different methods. My previous solution had
been to loop through the fields, performing a comparison in code, which
whilst it was fairly efficient, seemed to me to be going around the houses
somewhat. Besides which, I believe queries are optimised in Access thru the
Jet engine, whereas vba isn't.

The two methods suggested would seem to be far more sensible & lead me to
some other possibilties, too. I am fairly proficient in Excel & Word (to
some extent, although I am only just grasping Class objects!) Access seems
to work better with as minimal coding as possible. The examples you've given
will give me a springboard into sql & table relationships that are more
obvious to understand.

Thank you.

----------------------------------------------

Terry,

Will something like this do it?...

SELECT [ChequeNo]+1 AS Missing
FROM Cheques
WHERE (DMin("[ChequeNo]","Cheques","[ChequeNo]>'" & [ChequeNo] &
"'")=[ChequeNo]+1)=0

(assumes ChequeNo is text data type)

- Steve Schapel, Microsoft Access MVP

-----------------------------------------------
 
Back
Top