finding and saving sequences 1

[DwayneS]

I have a table with check numbers from 15,000 to 150,000. I need to summarize them into consecutive sequences so they can be entered as consecutive blocks (for example: Cks 15,000 - 17,807 = $1,111,111 ; 17,811 - 17,903 = $307,605, etc. PS: Checks 17,807 - 17,811 were not issued).

If I use a simple query with the table related to itself and look for non-dups, I get a list of all numbers not used, but there has to be a better way to get this information. If someone has a minute to steer me to a proper approach I would appreciate it a lot.

Dwayne Streeter, CPA, CITP

http://www.cpe-interactive.com

 

[Golom]

Probably easiest to set up a table with your ranges

[tt]tblRanges
MinRange MaxRange
15000 17807
17811 17903
etc.
[/tt]
Then

Select R.MinRange & " - " & R.MaxRange As [Range]
     , SUM(A.CheckAmount) As [Total]

From   myTable A, tblRange R

Where  A.CheckNumber BETWEEN R.MinRange AND R.MaxRange

Group By R.MinRange & " - " & R.MaxRange

 

[DwayneS]

Not sure I understand this. I don't know the check ranges except by analyzing the check numbers in my table. How do I set up the TblRanges table.

I think I'm not good enough with vb to structure that code you are giving me.

I appreciate the help, and I'll bet you can get this done for me.

Dwayne Streeter, CPA, CITP

http://www.cpe-interactive.com

 

[Golom]

Maybe ...

When you analyze the check numbers in your table, what do you do exactly?

Its really an issue of taking that analysis process out of your head and turning it into code. What I gave you assumed that there was some pre-defined set of ranges that you wanted to use. If there isn't then the process to determine those ranges from the data needs to be spelled out.

 

[gol4]

if you only have the one gap then you could do a sub select to get the gaps start and end but I assume there are many gaps like that in the data so I suggest you use a function to put the values into a table

Dim rst as dao.recordset
dim strsql as string
inttempid as integer, intckstart as integer, intckend as integer
dim inttotal as integer

strsql ="select * from mytable order by checknumber"
set rst = currentdb.openrecordset(strsql)
with rst
inttemp= !checknumber 'sync counters"
intckstart = !checknumber
do until .eof
.movenext
if !checknumber = inttemp + 1 then 'number in sequence
inttotal = inttotal + !amount ' add amount to total
inttemp = inttemp+1 'increment counter
intckkend = !checknumber
else " new sequence put in a table or an array then reset counters
strsql = "insert into tblsequence (ckstart,ckend, amount) values(" & intckstart & "," & intckend & "," & inttotal & ")"
currentdb.execute strsql
inttemp= rst!checknumber 'sync counters again"
intckstart = rst!checknumber
inttotal = !amount
end if
loop
.close
end with
set rst= nothing

values will be in the tblsequencetbl

this code is freehanded and not tested so will need a lot of clean up. You will also need to have a table named tblsequence with the fields
ckstart,ckend, amounts
and change the code to match the fields you are pulling data from

 

[DwayneS]

I really appreciate Gol4's post. It was all exactly the help I needed. For someone who just typed this code without actually running it, it was nearly flawless.

I'll continue to take time to answer some of the questions on this board which aren't so complicated so you guys with such quality help can find time to do it.

I really, really appreciate this help.

Dwayne Streeter, CPA, CITP

http://www.cpe-interactive.com