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

Count Records Query

P: n/a
Been at this for two days now...

I have a customer database with various fields, two of which are:

ContactID (an autonumber field) & ReferralID (long Integer)

When a customer refers someone new, the new customer gets the referee's
ContactID in their ReferralID field. So someone who refers 10 people
will have 10 instances of their ContactID scattered down the ReferralID
column.

I am basically trying to count the number of referrals that a customer
has by counting all the instances of ReferralID which equal their
ContactID.

I have tried Dcount, Queries, VBA and SQL to no avail...

It sounds simple, but I can't figure it out! I saw somewhere that it
may have to do with the autonumber field not being a standard long
integer, but that may be off...

Thanks...

Jun 4 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
In the Query Builder, drag down into the grid ContactID and ReferralID.
Then, on the menu, View | Totals. In the "Total:" line, chose Count under
ContactID and Group By under ReferralID. Run the Query.

Larry Linson
Microsoft Access MVP

<al******************@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
Been at this for two days now...

I have a customer database with various fields, two of which are:

ContactID (an autonumber field) & ReferralID (long Integer)

When a customer refers someone new, the new customer gets the referee's
ContactID in their ReferralID field. So someone who refers 10 people
will have 10 instances of their ContactID scattered down the ReferralID
column.

I am basically trying to count the number of referrals that a customer
has by counting all the instances of ReferralID which equal their
ContactID.

I have tried Dcount, Queries, VBA and SQL to no avail...

It sounds simple, but I can't figure it out! I saw somewhere that it
may have to do with the autonumber field not being a standard long
integer, but that may be off...

Thanks...

Jun 4 '06 #2

P: n/a
Thanks for the reply Larry.

I got that far, but the point I am stuck at is being able to display
the number of referrals on a form.

To clarify, I have a form with all the customer details (including a
textbox for the number of referrals they have had). I want a field on
the form to display the number of referrals that only the current
ContactID has (presumably from the output of the query).

Thanks again.
Larry Linson wrote:
In the Query Builder, drag down into the grid ContactID and ReferralID.
Then, on the menu, View | Totals. In the "Total:" line, chose Count under
ContactID and Group By under ReferralID. Run the Query.

Larry Linson
Microsoft Access MVP

<al******************@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
Been at this for two days now...

I have a customer database with various fields, two of which are:

ContactID (an autonumber field) & ReferralID (long Integer)

When a customer refers someone new, the new customer gets the referee's
ContactID in their ReferralID field. So someone who refers 10 people
will have 10 instances of their ContactID scattered down the ReferralID
column.

I am basically trying to count the number of referrals that a customer
has by counting all the instances of ReferralID which equal their
ContactID.

I have tried Dcount, Queries, VBA and SQL to no avail...

It sounds simple, but I can't figure it out! I saw somewhere that it
may have to do with the autonumber field not being a standard long
integer, but that may be off...

Thanks...


Jun 4 '06 #3

P: n/a
Got it!

for anyone else with the same trouble, I ran this:

UPDATE Contacts SET Referrals = DCount("*", "Contacts","[ReferrerName]
= " & [ContactID]);
al******************@gmail.com wrote:
Thanks for the reply Larry.

I got that far, but the point I am stuck at is being able to display
the number of referrals on a form.

To clarify, I have a form with all the customer details (including a
textbox for the number of referrals they have had). I want a field on
the form to display the number of referrals that only the current
ContactID has (presumably from the output of the query).

Thanks again.
Larry Linson wrote:
In the Query Builder, drag down into the grid ContactID and ReferralID.
Then, on the menu, View | Totals. In the "Total:" line, chose Count under
ContactID and Group By under ReferralID. Run the Query.

Larry Linson
Microsoft Access MVP

<al******************@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
Been at this for two days now...

I have a customer database with various fields, two of which are:

ContactID (an autonumber field) & ReferralID (long Integer)

When a customer refers someone new, the new customer gets the referee's
ContactID in their ReferralID field. So someone who refers 10 people
will have 10 instances of their ContactID scattered down the ReferralID
column.

I am basically trying to count the number of referrals that a customer
has by counting all the instances of ReferralID which equal their
ContactID.

I have tried Dcount, Queries, VBA and SQL to no avail...

It sounds simple, but I can't figure it out! I saw somewhere that it
may have to do with the autonumber field not being a standard long
integer, but that may be off...

Thanks...


Jun 4 '06 #4

P: n/a
On 4 Jun 2006 13:51:38 -0700, al******************@gmail.com wrote:
Got it!

for anyone else with the same trouble, I ran this:

UPDATE Contacts SET Referrals = DCount("*", "Contacts","[ReferrerName]
= " & [ContactID]);

Thanks for asking the question and posting your follow-up. I have a
similar need and you've saved me a lot of head-scratching. Thanks of
course to the experts too.
Jun 5 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.