By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,106 Members | 2,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,106 IT Pros & Developers. It's quick & easy.

Access query to count records which match another table

P: 91
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!
May 29 '17 #1

✓ answered by PhilOfWalton

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

Expand|Select|Wrap|Line Numbers
  1. DCount("PersonID","Walk_Participation_History",
  2. "WalkNumber = " & Chr$(34) & Walks.WalkNumber & Chr$(34))
  3.  
Also I suspect that the last line should check for Null and 0, so use:-

Expand|Select|Wrap|Line Numbers
  1. WHERE Nz(Walk_participation_history.Posn_ID) = 0;
  2.  
Phil

Share this Question
Share on Google+
8 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
May 29 '17 #2

P: 91
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Walk_participation_history INNER JOIN Walks ON Walk_participation_history.WalkNumber = Walks.WalkNumber
  3. WHERE (((Walk_participation_history.Posn_ID)=0));
  4.  
May 29 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
As I suspected, because WalkNumber is a text field, you need inverted commas, so try

Expand|Select|Wrap|Line Numbers
  1. DCount("PersonID","Walk_Participation_History",
  2. "WalkNumber = " & Chr$(34) & Walks.WalkNumber & Chr$(34))
  3.  
Also I suspect that the last line should check for Null and 0, so use:-

Expand|Select|Wrap|Line Numbers
  1. WHERE Nz(Walk_participation_history.Posn_ID) = 0;
  2.  
Phil
May 29 '17 #4

P: 91
Ah, that did it. Thank you so much.
WFYC is fortunate indeed.
Peter
May 29 '17 #5

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblWalk]
  2.        INNER JOIN
  3.        [tblWalkHist]
  4.     ON [tblWalk].[WalkID]=[tblWalkHist].[WalkID]
  5. SET    [tblWalk].[NoPpl]=[NoPpl]+1
  6. 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.
May 30 '17 #6

P: 91
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.
May 30 '17 #7

NeoPa
Expert Mod 15k+
P: 31,494
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.
May 30 '17 #8

P: 91
Ah, thanks for the explanation.
May 31 '17 #9

Post your reply

Sign in to post your reply or Sign up for a free account.