Verify numbers are in sequence

[Purdue2479]

I am having to verify that for an ID group that the related column of numbers are in sequence. Below is a snapshot of the data I'm working with. I need to verify that the IVMTESTSN is not skipping a #. If anyone has a function I could use I would appreciate it. Thanks


IVMSGRP IVMTESTSN
010528 1
010528 2
010528 3
010528 4
010528 5
010528 6
010528 7
010528 8
010528 9
010528 10
010528 11
010528 12
010528 13
010528 14
010528 15
010528 16
010528 17
010528 18
010528 19
010528 20
010528 21
010528 22
010528 23
010528 24
010528 25
010528 26
010528 27
010528 28
010528 29
010528 30

 

[Zigzag]

What are you looking for?

1, the "numbers are in sequence" (These can sorted in the query by selecting Sort Ascending.)

or

2, no numbers are missing? (find the max number of "IVMTESTSN" and then compare it with the record count. If they are not the same there is a number missing, provided the "IVMTESTSN" always starts from 1)

Garry

 

[RuralGuy]

Do you know how to write code using a DAO.Recordset? What do you want done if a number is skipped?

 

[Purdue2479]

The max number would work, but I need to return the whole record set that is missing the number. I am not very efficient with VBA, so if you could suggest some code I could use to do this it would be greatly appreciated. Thanks.

 

[RuralGuy]

So this table contains *only* one IVMSGRP?

 

[Purdue2479]

No, there are multiple IVMSGRP

 

[MStCyr]

Hi Allan

I would like to add my voice to others and say "Congrats" on being awarded the Access MVP.

I've always enjoyed reading your responses.

Best regards

Maurice St-Cyr
Ottawa, Ontario -CANADA

 

[RuralGuy]

No, there are multiple IVMSGRP

So each IVMSGRP will need to be tested for an unbroken IVMTESTSN sequence. Maybe if you told us what you were going to do with the IVMSGRP check that have broken sequences, we would have a better understanding of how to offer suggestions. Right now you just say you need a recordset with the "violator" IVMSGRP records.

 

[RuralGuy]

Thanks for the comments Maurice, I enjoy your posts as well.

 

[Purdue2479]

I need the results to be returned in a query

 

[RuralGuy]

Hi Perdue,
You had mentioned the recordset at the beginning but what then? Are you creating some report? Will you be renumbering the IVMTESTSN sequence? Will you be adding in the missing record? Are you exporting the records? The point is it might make a difference as to the method used to create the recordset. Maybe everything could be done at the same time. Did you want the offending records put in a temporary table?

 

[Purdue2479]

Sorry, I'm a little thick headed today. I would want the records put into a temporary table.

 

[RuralGuy]

Did you want the records "moved" to the temporary table or just have "copies" of the records in the temporary table?

 

[ByteMyzer]

If I understand correctly, for each group (IVMSGRP) that is missing one or more numbers in sequence, you want to see the entire set of records for that group?

Example:

IVMSGRP	IVMTESTSN
010528	1
010528	2
010528	3
010528	4
010528	5
010528	6
010528	7
010528	8
010528	9
010528	10
010529	1
010529	3
010529	4
010529	5
010529	6
010529	7
010529	9

...from which you would expect to see:

IVMSGRP	IVMTESTSN
010529	1
010529	3
010529	4
010529	5
010529	6
010529	7
010529	9

...populated in a temporary table?

If this is the case, the following Make-Table query may meet your requirement (substitute the highlighted text with actual names):

SELECT T1.* INTO [b][i]MyTable_Temp[/i][/b]
FROM [b][i]MyTable[/i][/b] T1
INNER JOIN
 (SELECT DISTINCT T2.[b][i]IVMSGRP[/i][/b]
  FROM ([b][i]MyTable[/i][/b] T2
  LEFT JOIN
   (SELECT T3.[b][i]IVMSGRP[/i][/b], T3.[b][i]IVMTESTSN[/i][/b] - 1 AS [b][i]IVMTESTSN[/i][/b]
    FROM [b][i]MyTable[/i][/b] T3) T3
 ON T2.[b][i]IVMSGRP[/i][/b] = T3.[b][i]IVMSGRP[/i][/b]
 AND T2.[b][i]IVMTESTSN[/i][/b] = T3.[b][i]IVMTESTSN[/i][/b])
  LEFT JOIN
   (SELECT T4.[b][i]IVMSGRP[/i][/b], MAX(T4.[b][i]IVMTESTSN[/i][/b]) AS [b][i]IVMTESTSN[/i][/b]
    FROM [b][i]MyTable[/i][/b] T4
    GROUP BY T4.[b][i]IVMSGRP[/i][/b]) T4
 ON T2.[b][i]IVMSGRP[/i][/b] = T4.[b][i]IVMSGRP[/i][/b]
 AND T2.[b][i]IVMTESTSN[/i][/b] = T4.[b][i]IVMTESTSN[/i][/b]
  WHERE T3.[b][i]IVMSGRP[/i][/b] IS NULL
  AND T4.[b][i]IVMSGRP[/i][/b] IS NULL) T2 ON T1.[b][i]IVMSGRP[/i][/b]=T2.[b][i]IVMSGRP[/i][/b]
;

 

[RuralGuy]

I've got to learn how to use SQL better. If that works then that is beautiful! I was going to put something together with a recordset and transactions.

 

[Zigzag]

WOW!!!!!!

I've been watching this thread from the start wondering what the solution would be.
(still working through the post in my head by Bytemyzer but i get the idea)

Lovely

 

[Purdue2479]

ByteMyzer, your SQL works perfectly. I had also implemented a solution using two querys, but if I can get the job done in on that's even better. Rural Guy, I would still like to see your solution using VBA. I would need the records copied into the temp table. Thank each of you for your help.


Solution I came up with:

 SELECT RIVMTMA0_STDY_GRP_TST_MSTR.IVMSGRP, Count(RIVMTMA0_STDY_GRP_TST_MSTR.IVMSGRP) AS [IVMSGRP - # of Records], Max(RIVMTMA0_STDY_GRP_TST_MSTR.IVMTESTSN) AS [IVMTESTSN - Max #]
FROM RIVMTMA0_STDY_GRP_TST_MSTR
GROUP BY RIVMTMA0_STDY_GRP_TST_MSTR.IVMSGRP
HAVING (((Count([IVMSGRP])<>Max([IVMTESTSN]))=-1));

SELECT [qry1000_005_01-Base].IVMSGRP, RIVMTMA0_STDY_GRP_TST_MSTR.IVMTESTSN, RIVMTMA0_STDY_GRP_TST_MSTR.IVMTTYPE, RIVMTMA0_STDY_GRP_TST_MSTR.IVMRSLT
FROM [qry1000_005_01-Base] LEFT JOIN RIVMTMA0_STDY_GRP_TST_MSTR ON [qry1000_005_01-Base].IVMSGRP = RIVMTMA0_STDY_GRP_TST_MSTR.IVMSGRP
GROUP BY [qry1000_005_01-Base].IVMSGRP, RIVMTMA0_STDY_GRP_TST_MSTR.IVMTESTSN, RIVMTMA0_STDY_GRP_TST_MSTR.IVMTTYPE, RIVMTMA0_STDY_GRP_TST_MSTR.IVMRSLT;