473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Count Records Query

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
4 7746
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: leegold2 | last post by:
Below is some code from a FULLTEXT search. My question is how do I get a count of the number of rows found by the search? $query="SELECT * FROM balloon_txt WHERE MATCH(access_no, recs_txt)...
3
by: C. Homey | last post by:
I have an Access 2000 database (42,000+ records) for which I need a count of records by state (AL=320, AK=92, NY=1,932, etc). Obviously, I don't want to (and probably can't...) do the count...
5
by: Terri | last post by:
I have a form with a multi-select combo. I dynamically build a SELECT statement, open a report, and set the recordsource to my dynamic SELECT statement. I count the records returned in the report...
7
by: Hugo L. | last post by:
Dear I have a table with a Text field named "Attest". In that field I can fill in 4 possibilities: A, B, C or D. In a report based om that table I want to count how many times I filled in...
6
by: Nicolae Fieraru | last post by:
Hi All, I have a query, Select Count(BoolField) from tblMyTable, Where BoolField = true. If I run the query by itself, it returns the number of true records I want to use the result of that...
6
by: John | last post by:
I've got a single table I need to query to return records that have no "related records." Table dataset example: 1. John, Biology 2. Dave, Math 3. Susan, Biology 4. Betty, Sociology The...
3
by: Hyphessobricon | last post by:
Hallo, Indeed, a count of a query with a group by function gives more records than there are and so for-next structures don't function. How is this to be mended. Anyone? Everyone in fact....
4
by: Anuradha | last post by:
Dear Experts How can I count records in a data reader? Thx Anuradha
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
12
by: petter | last post by:
Hi! I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don’t have any data for that month. I have an Access database where I want...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.