Counting records using SQL and writing to a table.

[TheTrainMan]

I have a table with data for the past year or so (new data added every day), each month I need to produce a graph based on this data for the past 13 periods (28 days each period). There are 3 cause codes A, B and C. I need to count how many instances of cause A, B and C there are in every 28 day period and then produce a graph from it. I want to create a table and populate it from VBA and then export to an excel spreadsheet linked to a powerpoint document.

How do I go through the data, count the number of instances of each casue and write this to a table?? Access help is decidedly unhelpful!!

thanks in advance

 

[crosmill]

Hey TrainMan,

What you need top do is, in VBA create a recordset for each variable A B C, the code will be the same just "...WHERE = 'A'..." etc.

You should look throught h help files under Recordset, you should find everything you need.


There are a few ways of counting the records, one you'll no doubt come accross is Recordset.Count. From my experience this doesn't work, it only ever gives me a -1 or 0 result. So I do it the manual way and loop throgh the Recordset and increment a variable. (EOF stands for end of file, and rs for Recordset)

While NOT rs.EOF
n = n + 1
rs.MoveNext
Wend


Hope this helps you, post back if you get stuck, there are quite a few pitfalls, but it's really not worth going into them unless you acctually hit them.

HTH

 

[TheTrainMan]

Whilst looking on microsoft's website about automation, I came across some code:

Private Sub Graph_Click()
Dim MyExcel As Object, TitleArray As Variant, DataArray As Variant

TitleArray = Array("Dogs", "Cats", "Penguins")
DataArray = Array(34, 53, 12)

Set MyExcel = CreateObject("Excel.application")
With MyExcel
.Workbooks.Add
.Range("A1:C1").Value = TitleArray
.Range("A2:C2").Value = DataArray
.Range("A1:C2").Select
.Visible = True
.charts.Add
End With

Set MyExcel = Nothing

End Sub

Which I'm sure I can mess about with a bit so I don't actually need to create a table, If I can loop through each period and get the count of each cause, then I can add that to an array and do similar to the above. The table I'd produce in Excel would always be the same shape so that's no worry, but I'm still not 100% on automation... how do I mess about with how the chart looks from within excel (the stuff I've read from MS website doesn't seem to be working!!)

Surely there must be an easier way then incrementing in a loop to count? I could do a query for each period and cause and then have another query with a simple sql count for each of the other queries... but that seems far too messy!!! How do I use count within VBA and varying the SQL string that returns the records that are counted??? Any ideas???

 

[crosmill]

You could create a query for each count that would give you the value and link that directly in excel?

 

[TheTrainMan]

Although I'm not on a tight deadline, I do need to get this done quite soon, and that was my original thought, to have (3x13) 39 queries and to output it to excel. Whilst this is quick and dirty, I aspire to beauty and elegance (well, apart from my girlfriend!) If in the next couple of weeks I can't sort out a *nice* solution, I'll probably be forced into doing something this nasty!!

 

[crosmill]

personally I like quick and drity *for a girlfriend* probably why I don't have one!

The only nice solution would be to code it, a combination the method I suggested and microsofts automation.

39 queries is really unworkable, it'll just make a mess of your db.

You could probalbly work a loop into the code I suggested so you don't have to write it 39 times, again incrementing a number to change the value, you could problly get away with just doing it three times.

For i = 0 TO 13
i = i + 1
...............

etc..

Say hi to your girfriend for me.

 

[TheTrainMan]

It's easy really...

-----
Private Sub btnReport_Click()

Dim PeriodArray(13) As String, MyCount As Integer, PeriodWrite As Integer
Dim EndDate As Date
Dim StDateAsDbl As Double, EndDateAsDbl As Double
Dim SQLStrHead As String, SQLStr As String
Dim dbs As Database
Dim rst As Recordset
Dim AArray(13) As Integer, BArray(13) As Integer, CArray(13) As Integer
Dim MyExcel As Object, MyChart As Object

'Set Up Period No. Array, 13 periods for financial year
'12 to 0 since referencing an array

PeriodWrite = Me!PeriodNo

For MyCount = 12 To 0 Step -1
PeriodArray(MyCount) = "P" & PeriodWrite
PeriodWrite = PeriodWrite - 1
If PeriodWrite = 0 Then
PeriodWrite = 13
End If
Next MyCount

'Set the dates to search between

EndDate = Me!pEndDate
'convert to double for SQL searches
StDateAsDbl = CDbl(EndDate)
StDateAsDbl = StDateAsDbl - (13 * 28) + 1

'Set SQL string to search with

SQLStrHead = "SELECT * FROM qrytable1 where [Responsibility] = "

'Set Database

Set dbs = CurrentDb

For MyCount = 0 To 12
EndDateAsDbl = StDateAsDbl + 27

'Count records for CauseA

SQLStr = SQLStrHead & "'CauseA' AND [DblDate] Between " & StDateAsDbl & " And " & EndDateAsDbl
Set rst = dbs.OpenRecordset(SQLStr, dbOpenDynaset, dbReadOnly)
If (rst.BOF = True) And (rst.EOF = True) Then
AArray(MyCount) = 0
Else
rst.MoveLast
AArray(MyCount) = rst.RecordCount
End If

'Count records for CauseB

SQLStr = SQLStrHead & "'CauseB' AND [DblDate] Between " & StDateAsDbl & " And " & EndDateAsDbl
Set rst = dbs.OpenRecordset(SQLStr, dbOpenDynaset, dbReadOnly)
If (rst.BOF = True) And (rst.EOF = True) Then
BArray(MyCount) = 0
Else
rst.MoveLast
BArray(MyCount) = rst.RecordCount
End If

'Count records for Cause C

SQLStr = SQLStrHead & "'CauseC' AND [DblDate] Between " & StDateAsDbl & " And " & EndDateAsDbl
Set rst = dbs.OpenRecordset(SQLStr, dbOpenDynaset, dbReadOnly)
If (rst.BOF = True) And (rst.EOF = True) Then
CArray(MyCount) = 0
Else
rst.MoveLast
CArray(MyCount) = rst.RecordCount
End If

StDateAsDbl = StDateAsDbl + 28
Next MyCount


'Write data to excel and produce chart

Set MyExcel = CreateObject("Excel.Application")
With MyExcel
.Workbooks.Add
.Range("A1").Value = "Period No."
.Range("A2").Value = "Cause A"
.Range("A3").Value = "CauseB"
.Range("A4").Value = "CauseC"
.Range("B1:N1").Value = PeriodArray
.Range("B2:N2").Value = AArray
.Range("B3:N3").Value = BArray
.Range("B4:N4").Value = CArray
.Range("A1:N4").Select
Set MyChart = .charts.Add()
.Visible = True
End With


'Free up memory


Set dbs = Nothing
Set rst = Nothing

Set MyExcel = Nothing
Set MyChart = Nothing

End Sub
---

RecountCount only returns the value of records accessed so have to use .MoveLast to get it to have the correct value!

I had to change the dates to doubles since SQL in VBA only uses US dates... tried Format(MyTime, "mm:dd:yy"), but because of the system settings it completely ignores what I asked it to do and sets it with format dd:mm:yy!!! How bloody silly!!

I've also tried to program in a loop so that I don't need to write out extra blocks of code within the loop I've already got - i.e. if there becomes a Cause D, then I'd have to reprogramme at the moment... I've used SELECT DISTINCT responsibility and looped through to count each instance - but it doesn't work!! It does find each responsibility code (CauseA, CauseB,CauseC) and makes up a SQL string based on that, but obviously somewhere along the line it doesn't like it!!!

Hey, even though I'm new to this, I think I'm learning quite fast!!!

Speak soon

 

[crosmill]

rst.MoveLast
BArray(MyCount) = rst.RecordCount

That'll explain why it never works for me

being form Manchester......do you happen to know if Manchester Uni's venue is the Academy half way down Oxford Road. I'm going to a gig on Thursday and it's ages since I've been there.

 

[TheTrainMan]

That definately explains it!!!

And by the way, yes, the Academy is on Oxford Road, just a stone's throw from the Uni building, just up the road from the hospitals.

have fun!

 

[MrTibbs]

Countif conditionally

sum(iif(fldCharacter="C",1,0)) This counts the number of C's etc.

So n periods can be counted in 1 query but the repeated function calls overhead may be a problem.