Access query to count records which match another table

[Petrol]

I have two related tables:
[Walks], which gives details such as date, location etc of a particular event; and
[Walk_participation_history], which records the people who attended that event. Fields include WalkNumber and PersonID.

I have now added a field NumPersons to the Walks table, and want to run a one-off query to populate the historical Walks records with the number of people who attended each Walk. I've tried a query using DCount but can't get it to work.

Any help appreciated!

[PhilOfWalton]

Ah, Petrol, I remember you query from about a year ago.

The DCount is probably the way to go.

What specifically have you tried? To the best of my recollection, WalkNumber is a Text field, so have you remembered to use the the double quotes round it (I prefer to use Chr$(34))

Phil

[Petrol]

You have a good memory!
I built an Access query based on the two tables, joined on WalkNumber (which is indeed a text field), and included "DCount("PersonID","Walk_Participation_History","W alk_Participation_History.WalkNumber = Walks.WalkNumber" as an expression. I built it as a Select query intending to change it to an Update query once I got it working, but it fails with the message "Emmaus BS DB cannot find the name "Walks.WalkNumber" you entered in the expression" - though that is certainly the correct name of the field.
The actual SQL generated by the query is as follows:

SELECT Walk_participation_history.WalkNumber, Walks.WalkNumber, Walk_participation_history.Posn_ID, DCount("PersonID","Walk_Participation_History","Walk_Participation_History.WalkNumber = Walks.WalkNumber") AS Attendance
FROM Walk_participation_history INNER JOIN Walks ON Walk_participation_history.WalkNumber = Walks.WalkNumber
WHERE (((Walk_participation_history.Posn_ID)=0));
 

[PhilOfWalton]

As I suspected, because WalkNumber is a text field, you need inverted commas, so try

DCount("PersonID","Walk_Participation_History",
"WalkNumber = " & Chr$(34) & Walks.WalkNumber & Chr$(34))
 

Also I suspect that the last line should check for Null and 0, so use:-

WHERE Nz(Walk_participation_history.Posn_ID) = 0;
 

Phil

[Petrol]

Ah, that did it. Thank you so much.
WFYC is fortunate indeed.
Peter

[NeoPa]

Hi Petrol.

Although a GROUP BY query, as well as a query that uses a GROUP BY subquery, become non-updatable, you can manage this in a simple UPDATE query such as below :

UPDATE [tblWalk]
       INNER JOIN
       [tblWalkHist]
    ON [tblWalk].[WalkID]=[tblWalkHist].[WalkID]
SET    [tblWalk].[NoPpl]=[NoPpl]+1
WHERE  ([tblWalk].[NoPpl]=0)

I've used a WHERE clause to ensure you never recalculate a record that has already been calculated.

Domain Aggregate (such as DLookup(), DCount() etc.) functions are generally not recommended for use within SQL. There can be scenarios where they are a good solution, but they're few and far between.

NB. SQL standard for quotes is the single quote (') but both Jet & ACE support doubles (") too. For best portability and consistency with the standard I would always recommend the singles, but be reassured it shouldn't matter too much unless you want to port your SQL code elsewhere.

[Petrol]

Hmm, that's an interesting solution. The query I ran was just for one-off use to populate the historical Walks records with the numbers of people, and Phil's solution worked, so I won't need to run it again. However I'm always interested to learn new approaches, so thanks for the idea. I don't quite understand how it works, though: If the SET statement only increments NoPpl when NoPpl is zero, how can it ever get beyond 1?
Also, why are domain aggregate functions not recommended for use within SQL?
Thanks.

[NeoPa]

Petrol:
If the SET statement only increments NoPpl when NoPpl is zero, how can it ever get beyond 1?

It checks [NoPpl] at the start of the UPDATE query. Not every time a record is processed. It works.

Petrol:
Why are domain aggregate functions not recommended for use within SQL?

Because each Domain Aggregate function call has to initialise and de-initialise a recordset. This overhead can add up to a great deal of extra processing (and time of course).

In essence this can be trivial, but isn't a good habit (or way of thinking) to get into, as it can turn out to be important in some datasets. Smaller datasets can be processed without the extra overhead being noticeable, but larger ones can be hard to miss.

[Petrol]

Ah, thanks for the explanation.