Check for Increment

[Starke]

I have a query setup that has a result of the following

Account, TransDate RefCode

I have grouped by account, date

I need to check for each account (3 account total) that the ref code
last digit increments by 1. When the account changes, the number will
change so I need for it to reset, the numbers will still increment by
1 but when the account changes it may initially jump numbers.

If increments by more than 1 number I need to return the results, or
create a table so I know to investigate. Is this possible. sample
data below


Account RefCode
39236W 0743811-025
39236W 0743811-025

Thanks,

 

[Starke]

I have a query setup that has a result of the following

Account, TransDate RefCode

I have grouped by account, date

I need to check for each account (3 account total) that the ref code
last digit increments by 1. When the account changes, the number will
change so I need for it to reset, the numbers will still increment by
1 but when the account changes it may initially jump numbers.

If increments by more than 1 number I need to return the results, or
create a table so I know to investigate. Is this possible. sample
data below

Account RefCode
39236W 0743811-025
39236W 0743811-025

Thanks,

________________________________________________

Example should have been


Account RefCode
39236W 0743811-025
39236W 0743811-026


Thanks,

Dave

 

[Starke]

I have a query setup that has a result of the following

Account, TransDate RefCode

I have grouped by account, date

I need to check for each account (3 account total) that the ref code
last digit increments by 1. When the account changes, the number will
change so I need for it to reset, the numbers will still increment by
1 but when the account changes it may initially jump numbers.

If increments by more than 1 number I need to return the results, or
create a table so I know to investigate. Is this possible. sample
data below

Account RefCode
39236W 0743811-025
39236W 0743811-025

Thanks,

________________________________________________

Example should have been


Account RefCode
39236W 0743811-025
39236W 0743811-026


Thanks,

Dave

 

[BruceM]

How does the number increment? Are you using autonumber, code, or what?
What do you mean by "when the account changes"? Would you prefer to control
the incrementing so that it increments by 1, or would you rather edit the
records manually? I see that Account and TransDate are in the same query,
but are they in the same table, or are they in separate, related tables?
Where does the first part of RefCode come from? Is it part of the
incrementing, or does the incrementing occur just in the last three digits?

I know this is a lot of questions, but your question could be taken any
number of ways. Without some more information about what you are trying to
do and what you have attempted so far, it is difficult to offer specific
suggestions.

 

[Starke]

How does the number increment? Are you using autonumber, code, or what?
What do you mean by "when the account changes"? Would you prefer to control
the incrementing so that it increments by 1, or would you rather edit the
records manually? I see that Account and TransDate are in the same query,
but are they in the same table, or are they in separate, related tables?
Where does the first part of RefCode come from? Is it part of the
incrementing, or does the incrementing occur just in the last three digits?

I know this is a lot of questions, but your question could be taken any
number of ways. Without some more information about what you are trying to
do and what you have attempted so far, it is difficult to offer specific
suggestions.

Sorry, I see what you mean. I should have been more descriptive. Ok,
all fields are part of one table. Its on an as400. Im querying the
last 7 days of records. Basically the Ref Number is a number the 400
auto increments by one number higher each record if everything goes
ok. Once the last digit reaches 9 it goes back to 0 and increments
back up till 9 then reset back to 0 for each record that is
successfull (this is just a tracking table for a program on the 400).
So basically I just need to check each account number for all records
returned and make sure they count (last digit of ref #) 0-9 then start
over. If for some reason the last digit is as below

0
1
3
4
5
etc, I need to capture the record of 1 and 3 to show that 2 is
missing. Wow, As i type this I see how confusing it is.

Now for the account, I have 3 of them so this logic needs to take
place for each account by running the logic for each account number.

Does this make sense?

 

[BruceM]

Sorry, I see what you mean. I should have been more descriptive. Ok,
all fields are part of one table. Its on an as400. Im querying the
last 7 days of records. Basically the Ref Number is a number the 400
auto increments by one number higher each record if everything goes
ok. Once the last digit reaches 9 it goes back to 0 and increments
back up till 9 then reset back to 0 for each record that is
successfull (this is just a tracking table for a program on the 400).
So basically I just need to check each account number for all records
returned and make sure they count (last digit of ref #) 0-9 then start
over. If for some reason the last digit is as below

0
1
3
4
5
etc, I need to capture the record of 1 and 3 to show that 2 is
missing. Wow, As i type this I see how confusing it is.

Now for the account, I have 3 of them so this logic needs to take
place for each account by running the logic for each account number.

Does this make sense?

I don't know what you mean by as400, but I think I understand that you want
to check for gaps in the last digit of RefCode from one record to the next.
What isn't clear is whether anything else increments, or just the last
digit. In other words, this is the sequence:
39236W 0743811-028
39236W 0743811-029

Is the next number 0743811-020 or is it 0743811-030? I assume the latter,
but I can't be sure.

Also, what determines the order of the records? I suppose you are sorting
by Account first, but what what determines the sequence within the Account
grouping?

What do you mean when you say you need to capture the records on either side
of a missing sequence number? Do you just need to identify the missing
record, or is there more to it than that?

This article may provide some help about using a query to determine a value
from the previous record or the next record:
http://support.microsoft.com/kb/210504/en-us