Find a missing field sequence.

[joeythelips]

Hi,

I have a field that stores file numbers that i have imported.
I want to write a query that returns all the missing file numbers. i.e if file number 18 is the last file number, i want to check that all numbers between 1 and 18 have been imported.
Does anyone know the best way to do this?

Joe

 

[SyedAqif]

The simplest way is to create a table having values from 1 to 18. Then write a query to match the records like:

Select Distinct MyField from TableFiles where MyField not in (Select IDNO from TableTemp) Order by MyField;

I hope this wil work for u

Cheers!
Aqif

 

[Guest_imported]

Hi,

the problem with creating the table having values from 1 to 18 is that i don't know what number the files will end at.

Joeythelips

 

[paddymac]

Hi Aqif,

Thanks for your help.
The problem though is that i will never know in advance what the last fileno is.
Can i not just do a query that returnsall the fileno's and then search for the missing ones?

Joe

 

[MichaelRed]

There is a (recent) thread re using the "Previous Record". It would show you 'how to' use values from two (sequential) records in a single Query. So, construct this using your "fileNumber" field. Depending on the details of HOW you construct the query, you will be able to 'see' the records which have no "previous entry" for the FileNumber field. Do an advanced search on "Previous Record" or "Previous Records" if you need additional help.

The core issue in doing this is to either use a subquery or a self join MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Paddyman]

Hi MichaelRed.

Joeythelips here.
I'm afraid i cannot find that thread "Previous Record(s)"
Do u know exactly where it is?

 

[MichaelRed]

thread702-90545

thread701-101152

There are others. These are not even necessarily the best. Use some imagination and vary the search patterm & criteria.



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over