Updating field with data from different queries

[Guest]

Part of my function has includes a local temp table that I need to update the
tripcount field with a count from three queries.

the local temp table has clientID and tripcount fields. tripcount field is
the sum of three types of trips the client may have taken in the past 60 days:
1. Trip completed from table1
2. Trips that the client didn't show up for from table2
3. Trips that the client canceled too late and therefore is penalized from
table3

I have to do a do loop through each clientid record and update the tripcount
with the sum of dcount of #1, #2, and #3. If there are 100 clients to go
through, it takes awhile to do the do loop.

Is there a better way to get a sum from the three queries for each client?

I use this info when printing out an letter (an access report) for each of
the clients. Is better to do a dcount on the letter itself for each client?
that would mean every time a user scrolls through the pages, the dcount would
run.

 

[Wolfgang Kais]

Hello "ngan".

Part of my function has includes a local temp table that I need to
update the tripcount field with a count from three queries.

the local temp table has clientID and tripcount fields. tripcount
field is the sum of three types of trips the client may have taken
in the past 60 days:
1. Trip completed from table1
2. Trips that the client didn't show up for from table2
3. Trips that the client canceled too late and therefore is
penalized from table3

I have to do a do loop through each clientid record and update the
tripcount with the sum of dcount of #1, #2, and #3.
If there are 100 clients to go through, it takes awhile to do the
do loop.

Is there a better way to get a sum from the three queries for each
client?

I use this info when printing out an letter (an access report) for
each of the clients. Is better to do a dcount on the letter itself
for each client? That would mean every time a user scrolls through
the pages, the dcount would run.

You could at first create 3 queries (query1, query2 and query3) that
count the records from the respective table grouped by clientid.
You then could create a query that left-joins the clients table to
all three of the above queries to obtain the counted values.
You could base the report on that query instead of the temp table.
If you still want to use the temp table, your code could open 4
recordsets (clients, query1, query2 and query3).
Loot through the client table and use ".FindFirst" to find the
counted values in the other recordsets for the current clientID.