Suggestion needed (Access 2000)

Topic

New Reply

Hello all,

Please help with a pointer in the right direction. I have something working, but I am sure there is a better (quicker) way to do this.

I have two tables, People and Event.
People contains an ID and a date range.
Events contains the ID, a date for an event, and about 30 flags regarding what happened at the event.

What I need to do is summarise what happened for each person on all 30 flags for the date range,
ie ID1, Date1 – Date2, 3 phone calls, 4 visits, 0 emails, etc etc

At the minute I am using recordsets to query the Event table and then rowcount properties for the number of times each of the 30 separate things happened.
This works fine, but for each person I am running 30 SELECT queries, which is obviously not speedy.

Can someone please suggest another way that I can do this? There must be one, and I don’t want to leave this as “its not broken, so don’t fix it”.
Thanks in advance for any suggestions.

j

Reply | Quote

Replies

It would seem possible to use one or more totals queries to gather the information you need, but perhaps a change in table structure would be more efficient.. Could attach a small sample of what you’re working with? See post 401925 for instructions on posting a stripped down copy of a database.

Reply | Quote

It would seem possible to use one or more totals queries to gather the information you need, but perhaps a change in table structure would be more efficient.. Could attach a small sample of what you’re working with? See post 401925 for instructions on posting a stripped down copy of a database.

Reply | Quote

What I did in a similiar situation was open a recordset and declare integer variables for each thing that needed counting. I then looped through the recordset, with a series of case select or if statements, adding one to the variable for each thing that needed counting. That can then be put into a form or report, or exported to excel for further analysis.

Part of the code

Dim rst As ADODB.Recordset

Dim intCountAleph As Integer
Dim intCountBet As Integer
Dim intCountGimel As Integer
Dim intCount112 As Integer
Dim intCount122 As Integer
Dim intCount221 As Integer

With rst

.MoveFirst

Do Until .EOF
Select Case .Fields(8).Value
Case 112
intCount112 = intCount112 + 1
Case 122
intCount122 = intCount122 + 1
Case 221
intCount221 = intCount221 + 1
End Select

If IsNull(.Fields(3)) = True Then
intCountNoBirth = intCountNoBirth + 1
Else

Select Case Age(.Fields(3), .Fields(5))
Case “

Reply | Quote

What I did in a similiar situation was open a recordset and declare integer variables for each thing that needed counting. I then looped through the recordset, with a series of case select or if statements, adding one to the variable for each thing that needed counting. That can then be put into a form or report, or exported to excel for further analysis.

Part of the code

Dim rst As ADODB.Recordset

Dim intCountAleph As Integer
Dim intCountBet As Integer
Dim intCountGimel As Integer
Dim intCount112 As Integer
Dim intCount122 As Integer
Dim intCount221 As Integer

With rst

.MoveFirst

Do Until .EOF
Select Case .Fields(8).Value
Case 112
intCount112 = intCount112 + 1
Case 122
intCount122 = intCount122 + 1
Case 221
intCount221 = intCount221 + 1
End Select

If IsNull(.Fields(3)) = True Then
intCountNoBirth = intCountNoBirth + 1
Else

Select Case Age(.Fields(3), .Fields(5))
Case “

Reply | Quote