VBA- Do Until/ Do While Loops

  • Thread starter Thread starter Stephen Cox
  • Start date Start date
S

Stephen Cox

am working on a database where there is a requirement to identify specific
sequences of data and then count the instances of a specific sequence and
thereafter write that data to a table.

A typical example of the data being analysed is :


Date Team Result


24/10/2001 A L
28/10/2001 A L
31/10/2001 A W
01/12/2001 B D
02/12/2001 A L
03/12/2001 B D
In this instance Team A has a sequence of 2 L s which is then broken by a
W. Ideally the data that would be written to a separate table would be as
follows:

Date From Date To Team ResultType No of Sequential
Results

24/10/2001 28/10/2001 A L 2

31/10/2001 31/10/2001 A W 1

01/12/2001 03/12/2001 B D 2

I am trying to write a VBA Sub procedure to analyse the data and carry out
this process. The procedure is based upon Do Until Loops but is not working.
I am unable / do not know how to (1) finish the loop so that the first date
when the sequence is broken is returned as the Date To and (2) Subsequently
count and sum the instances contained in any sequence of results.

Can anybody help at all? Any assistance would be much appreciated.



Code so far is as follows:

Public Sub ResultSequence

Set dbs = CurrentDb
Set rst1 = CurrentDb.OpenRecordset("ExtractDataforSequentialResultAnalysis")

firststart = rst1.[Date]
firsttype = rst1.[AwayResult]
rst1.MoveNext
counter = 1

'MsgBox firststart
'MsgBox firsttype

Do Until rst1.EOF

If firsttype <> rst1.[AwayResult] Then
'write to table

writesequence rst1.[Date], rst1.[Away Team], rst1.[AwayResult],
counter

End If



rst1.MoveNext
Loop

End Sub



Sub writesequence(sfrom, team, resulttype, results)
Set addtotable = CurrentDb.OpenRecordset("ResultSequences")

With addtotable
..AddNew
![SequenceFrom] = sfrom
'![SequenceTo] = sto
![team] = team
![SequenceType] = resulttype
![NoOfResults] = results

..Update
End With




End Sub
 
I wouldn't bother to try and do it with recordsets and vba
manipulation of their contents. In this case it seems
easier to me to generate some append or make queries (the
sql therefrom) that do the job for you. Then run the sql in
your code.

Start roughing it out with Selects in the QBE grid and go
from there...

This is air code -
Calling the table Results, this will derive the data;

SELECT Results.Team, Min(Results.Date) AS MinOfDate,
Max(Results.Date) AS MaxOfDate, Results.Result,
Count(Results.Result) AS CountOfResult
FROM Results
GROUP BY Results.Team, Results.Result
ORDER BY Results.Team;

Now to make a new table called ResRange;

SELECT Results.Team, Min(Results.Date) AS MinOfDate,
Max(Results.Date) AS MaxOfDate, Results.Result,
Count(Results.Result) AS CountOfResult INTO ResRange
FROM Results
GROUP BY Results.Team, Results.Result
ORDER BY Results.Team;

Alternatively to append the rows;

INSERT INTO ResRange ( Team, MinOfDate, MaxOfDate, Result,
CountOfResult )
SELECT Results.Team, Min(Results.Date) AS MinOfDate,
Max(Results.Date) AS MaxOfDate, Results.Result,
Count(Results.Result) AS CountOfResult
FROM Results
GROUP BY Results.Team, Results.Result
ORDER BY Results.Team;

Once you've got the sql working then use the DoCmd.RunSQL in
vba to run the sql string.

It's not quite as much fun as recordsets... :-) but it
should do the job.

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

In Stephen Cox typed:
am working on a database where there is a requirement to
identify
specific sequences of data and then count the instances of
a specific
sequence and thereafter write that data to a table.

A typical example of the data being analysed is :


Date Team Result


24/10/2001 A L
28/10/2001 A L
31/10/2001 A W
01/12/2001 B D
02/12/2001 A L
03/12/2001 B D
In this instance Team A has a sequence of 2 L s which is
then
broken by a W. Ideally the data that would be written to a
separate
table would be as follows:

Date From Date To Team ResultType
No of
Sequential Results

24/10/2001 28/10/2001 A L
2

31/10/2001 31/10/2001 A W
1

01/12/2001 03/12/2001 B D
2

I am trying to write a VBA Sub procedure to analyse the
data and
carry out this process. The procedure is based upon Do
Until Loops
but is not working. I am unable / do not know how to (1)
finish the
loop so that the first date when the sequence is broken is
returned
as the Date To and (2) Subsequently count and sum the
instances
contained in any sequence of results.

Can anybody help at all? Any assistance would be much
appreciated.



Code so far is as follows:

Public Sub ResultSequence

Set dbs = CurrentDb
Set rst1 =
CurrentDb.OpenRecordset("ExtractDataforSequentialResultAnalysis")

firststart = rst1.[Date]
firsttype = rst1.[AwayResult]
rst1.MoveNext
counter = 1

'MsgBox firststart
'MsgBox firsttype

Do Until rst1.EOF

If firsttype <> rst1.[AwayResult] Then
'write to table

writesequence rst1.[Date], rst1.[Away Team],
rst1.[AwayResult],
counter

End If



rst1.MoveNext
Loop

End Sub



Sub writesequence(sfrom, team, resulttype, results)
Set addtotable =
CurrentDb.OpenRecordset("ResultSequences")

With addtotable
.AddNew
![SequenceFrom] = sfrom
'![SequenceTo] = sto
![team] = team
![SequenceType] = resulttype
![NoOfResults] = results

.Update
End With




End Sub
Click to expand...
 
Back
Top