My database has two main tables: a CLIENT table and VISIT table. Each client can have one or more "visit records". Most of the calculations that are done through my report involves information gathered from these 2 tables. The calculations are very simple (counter increment type), but I do need to go through every single client record, and every single visit record for each client, to get all the counter that are displayed in the report.
Here it is some sample code:
If (Loc > 0) Then
strSQL = "SELECT * FROM ActiveClient WHERE Location = " & Loc & " ORDER BY Id"
Else
strSQL = "SELECT * FROM ActiveClient ORDER BY Id"
End If
Set rsClient = dbs.OpenRecordset(strSQL)
'Rewind the recordset
rsClient.Requery
'Process one record at a time...
While Not (rsClient.EOF)
DoEvents
'Establish a recordset that includes all the visits for the current client and order
'the visits by date ascending order (from the 1st visit to the last).
strSQL = "SELECT * FROM ActiveVisit WHERE ClientId = " & rsClient.Fields("Id"

.Value
Set rsSource = dbs.OpenRecordset(strSQL)
'Rewind the recordset and check if it is empty. If it is, skip to next record (no visits to
'check).
rsSource.Requery
If rsSource.EOF Then
'MsgBox "Problem: a client was found with no recorded visit(s)."
GoTo Continue
End If
'If date of 1st visit falls in the year chosen, use the record
If (rsSource.Fields("Date"

.Value >= YearStartDate And rsSource.Fields("Date"

.Value <= YearEndDate And (rsSource.Fields("Abbreviation"

.Value <> "E"

) Then
'Add to Total No. of Women Screened.
Tot1 = Tot1 + 1
'If this client was approved for PEPW, add to the Total No. of
'Approvals for PEPW and to the Total No Referred to ES
If (rsClient.Fields("ApprvPEPW"

.Value = 1) Then
Tot2 = Tot2 + 1
Tot6 = Tot6 + 1
I hope that gives you an idea of the "mess" I created.
I am also wondering if the network we are on is aggravating the problem, because when I open it and run the report, it takes about 10 minutes (I am in the building next door to the server). When a user does the same, from 10 miles away, it takes an hour.
Do you think rebuilding the system in SQL Server, using VB as interface, and Crystal Reports would be a better choice?
Thank you so much for taking the time to help me!
TM