Best way to handle this query

[DeanConsulting]

I have a client that has the following tables:

tblMembers
tblCalls
tblAttendees

He has several members (fire fighters) in the member table. I would say around 100-150. He also has several calls (fire calls) in the calls table. I would also say around 500-1000. The attendees table contains a list of call id's and a list of member id's and a "y" or "n" to determine if that firefighter actually went on that call or not. (It is how they determine pay).

What needs to be done is that my client would like to select a member or ALL from a combo box and then select a starting and stopping date from calendar controls.

After he has selected this he would like a query to generate the number of calls each member went on times the members pay rate to arrive at a total amount of pay.

He displays the following information in a grid control: member name, pay rate, calls attended, total pay, percentage of calls attended, and total calls.

All of this is working but...

Things are really, really, really slow. He is using an Access 2.0 database and dao. I cannot get him to switch to ado. So, that is out of the question.

My question is this, how can I speed the query up? Right now, searching all those records, it takes about 25+ seconds. As the tables grow, well so does the search time.

The tables are indexed.

How can I speed this up without doing allot of modifications to his database tables??

Thanks in advance,
Noble


---------------------------------------
Noble D. Bell

http://www.noblesoftware.com

http://www.noblebell.com

 

[gmmastros]

First of all, DAO is actually faster than ADO when using an Access database (especially when that database is on the local hard drive), so DAO is not the problem.

My question to you is... How is the data pulled from the database? Are there repetitive calls to the db to get all the data necessary? By writing a better query, your performance may improve substantially.

If you share the query, you may get better advice.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DeanConsulting]

Actually it does what you mentioned. It does repetitive calls to the db. I did not know or understand how else to do it.

The idea is this:

1. Get a recordset from the calls table within the date range

2. Get a member from the member table

3. Check the attendees table for the call id, the member id, and if they attended (y/n)

4. If they attended then increment a counter for that member only

5. Do this for each member in the members table

6. When complete multiply the counters by all the member's pay rates to determine their pay

That is what I am doing right now and I am certain that the way that I am doing it is really slow and ackward.

I do not understand how write a query that will give me the results that I need from the above conditions.

the member name and pay rate are in the member table
the call id and the call dates are in the call table
the member name, call id, and attended (y/n) are in the attendees table.

Any thoughts??

Thanks,
Nb


---------------------------------------
Noble D. Bell

http://www.noblesoftware.com

http://www.noblebell.com

 

[gmmastros]

OK, this is Access and I'm a little rusty (I'm REALLY good with SQL Server).

tblMembers
tblCalls
tblAttendees

Select tblMembers.MemberName,
tblMembers.PayRate,
Sum(tblAtendees.MemberId) As CallsAttended
From tblMembers
Inner Join tblAtendees on tblMembers.MemberId = tblAttendees.MemberId
Inner Join tblCalls On tblAttendees.CallId = tblCalls.CallId
Where tblAttendees.WentOnCall = "y"
And tblCalls.CallDate Between StartDate And EndDate
Group By tblMembers.MemberName, tblMembers.PayRate

He displays the following information in a grid control: member name, pay rate, calls attended, total pay, percentage of calls attended, and total calls.

If the above query works properly, you can use it to calculate the rest of the information. You'll need to get the total number of calls in the date range.

Select Count(tblCalls.CallId) as NumberOfCalls
From tblCalls
Where CallDate Between <start date> and <end date>

so, the Membername, pay rate and calls attended will come from the first query. Multiplying the pay rate by calls attended will give you the total pay. Percentage of calls can be determined by dividing the calls attended by the total calls (from the 2nd query). And of course, the second query will give you the total calls.

Now, like I said, my Access syntax is a little rusty, so you may need to play around with the query a little. Also, I took some liberties with field names because you never stated what those field names are.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

It sounds like basically two operations: increment the counter, and get the pay. I see two entities or sets: members and attendees. Seems to me that you can join members and attendees, providing a where clause for who attended, and increment the counter for every member of the resulting set. You can do this with one query.

You don't define what you do with the pay that you've determined, whether you just need to see it or store it as well, so I won't speak to that. But, you can take the first query, create it in Access, and then call it as a stored procedure using DAO's QueryDefs collection.

HTH

Bob