Web Development

Ask something
Discussion Locked

Access 2000 Query to check incremental #

0 helpful
3 replies

by loydster 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to write a query that take one field, that should be a list of numbers that increase by 1. I need to be able to verify that all the numbers go up in sequence by one, or if they don't for a flag to be shown at the point where it is wrong.

Any ideas?

3 replies

by benwagg

For Access DB Numbers, containing field SeqNums, which contains records 1,2,3,4,5,6,7,9,10,11,15...

Add an AutoNumber field to assign sequential index numbers to your records. Access will then assign sequential numbers to your records, as in:
ID SeqNums
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 9
9 10
10 11
11 15

Then run this query:
SELECT Numbers.ID, Numbers.SeqNums, [SeqNums]-[ID] AS Diff
FROM Numbers
WHERE ((([SeqNum]-[ID])<>0));

Your results will show only the records where SeqNum minus ID (the autonumber index) is not equal to zero, indicating your sequential numbers that are "off." Each time the value of Diff changes, then that will indicate another skip.

ID SeqNums Diff
1 1 0
2 2 0
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
8 9 1
9 10 1
10 11 1
11 15 4

It's not foolproof, but it's a start. I suggest trying this in a _copy_ of your database first,to be sure not to inadvertently doing harm...

by loydster

Well, I'll give ya the points,:) but it didn't really help me unfortunetly...

It i think sent my mind in the right direction...Thanks for the reply... :)

This discussion is closed to new replies.