Find missing number in a sequence per record

[escuro19]

Hello,

I have a table where each Volunteer start from Day 1 to n...(Sequence)for
example:

Volunteer | Day
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2

I am hoping if somone could help, i want to be able to loop through a
Volunteer's Day, to find missing sequence based on the volunteer. eg. the
user specify which Volunteer they want to see (using Textbox) then it will
bring up just that volunteer and their missing their sequence:

Volunteer | Day | Error
1 1 ok
1 2 ok
1 4 Error
1 5 ok

If possible output it in a datasheet (table).

Thankyou in advance

 

[Stefan Hoffmann]

hi,

I have a table where each Volunteer start from Day 1 to n...(Sequence)for
example:
I am hoping if somone could help, i want to be able to loop through a
Volunteer's Day, to find missing sequence based on the volunteer. eg. the
user specify which Volunteer they want to see (using Textbox) then it will
bring up just that volunteer and their missing their sequence:

Add up the sequence values and compare it to the expected value - use
the Gauss pairing addition schema: e.g. like that (untested)

SELECT [Volunteer],
SUM([Day]) AS [SequenceSum],
Iif(COUNT(*) Mod 2 = 0,
(COUNT(*) + 1) * (COUNT(*) / 2),
(COUNT(*) + 1) + (COUNT(*) + 1) * ((COUNT(*) - 1) / 2)) AS
[SeqenceExpected]
FROM [yourTable]
GROUP BY [Volunteer]
HAVING [SequenceSum] <> [SequenceExpected]


mfG
--> stefan <--

 

[mcescher]

Hello,

I have a table where each Volunteer start from Day 1 to n...(Sequence)for
example:

Volunteer | Day
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2

I am hoping if somone could help, i want to be able to loop through a
Volunteer's Day, to find missing sequence based on the volunteer. eg. the
user specify which Volunteer they want to see (using Textbox) then it will
bring up just that volunteer and their missing their sequence:

Volunteer | Day | Error
1 1 ok
1 2 ok
1 4 Error
1 5 ok

If possible output it in a datasheet (table).

Thankyou in advance

I couldn't get the second half of Stefan's formula to work (if odd
number of records), but the first half did work for either even or odd
number of records. So, I modified it slightly.

SELECT [Volunteer],
SUM([Day]) AS [SequenceSum],
(COUNT(*) + 1) * (COUNT(*) / 2) AS [SeqenceExpected],
MAX([Day]) - (SUM([Day]) -(COUNT(*) + 1) * (COUNT(*) / 2)) AS
[FirstError]
FROM [yourTable]
GROUP BY [Volunteer]
HAVING [SequenceSum] <> [SequenceExpected]

escuro19, "Day" is a reserved word, so you probably want to avoid
that.

Hope this helps,
Chris M.

 

[Stefan Hoffmann]

hi,

I couldn't get the second half of Stefan's formula to work (if odd
number of records), but the first half did work for either even or odd
number of records. So, I modified it slightly.

This was simply a brain dump... I had to leave something do for the OP


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi @all,

btw, the correct implementation is simple, hope you guys found it:

It is always (first number + last number) * count of numbers / 2


1 2 3 => (1+3) * 3 / 2 = 4 * 3 / 2 = 6

1 2 3 4 5 => (1+5) * 5 / 2 = 6 * 5 / 2 = 15

or in our case:

(1 + COUNT(*)) * COUNT(*) / 2




mfG
--> stefan <--