Counting the number of referrals in a report

[Tergiver]

I have a table called Agents with these fields:
ID (autonumbered)
Name (text)
Referred By (number)

Referred By is used to point to the ID# of the agent that referred this agent.

I have a select query called QueryReferred with the following column:
Field: Referred BY
Table: Agents
Criteria: [AgentID:]

This query will find all of the agents who were referred by the supplied
agent ID.

Now I want to generate a report that lists each agent and the number of
agents they have referred.

Next to their name on the report form, I have a text box to display the
count. My really clueless attempt at expression writing led me to believe the
following would work:
=Count( Queries!QueryReferred!AgentID=[ID] )

Seems logical to me, but the following things are NOT occuring as I was
expecting:
1) The query is not getting its parameter passed to it, it's prompting for
one.
2) The query is not running once for each iteration of that line on the
report, it is only running once (or at least it is only prompting once).
3) The Count() function is returning the number of records in the report,
not the number of records returned by the query.

Can anyone help me solve this?

 

[Dale Fye]

Try the following:

Select T1.ID, T1.Name, Count(T2.ID) as Referrals
FROM Agents T1
LEFT JOIN Agents T2
ON T1.ID = T2.[Referral ID]
GROUP BY T1.ID, T1.Name

HTH
Dale

 

[Tergiver]

I have no idea what all that is or where it goes, but I tried the following:

I copied it into notepad and replaced the line feeds with spaces so that it
could be pasted into the control source for the text box on the report.

A yellow bang appears next to the control on the report design view stating
that the control has an invalid control source.

Clicking on the yellow bang gives me a menu restating the invalid control
source, the 2nd line says "Expressions must begin with an equal sign".

Adding an '=' at the beginning of the control source line yields this dialog
when you attempt to enter it:

"The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses."

Leaving the '=' out so that there is no interfering dialog and running the
report yields this dialog:

Syntax error (missing operator) in query expression '[Select T1].[ID,
T1].[Name, Count(T2].[ID) as Referrals FROM Agents T1 LEFT JOIN Agents T2 ON
T1].[ID = T2].[[Referral ID] GROUP BY T1].[ID, T1].Name'.

Sorry, but I'm completely clueless here.

Try the following:

Select T1.ID, T1.Name, Count(T2.ID) as Referrals
FROM Agents T1
LEFT JOIN Agents T2
ON T1.ID = T2.[Referral ID]
GROUP BY T1.ID, T1.Name

HTH
Dale

I have a table called Agents with these fields:
ID (autonumbered)
Name (text)
Referred By (number)

Referred By is used to point to the ID# of the agent that referred this agent.

I have a select query called QueryReferred with the following column:
Field: Referred BY
Table: Agents
Criteria: [AgentID:]

This query will find all of the agents who were referred by the supplied
agent ID.

Now I want to generate a report that lists each agent and the number of
agents they have referred.

Next to their name on the report form, I have a text box to display the
count. My really clueless attempt at expression writing led me to believe the
following would work:
=Count( Queries!QueryReferred!AgentID=[ID] )

Seems logical to me, but the following things are NOT occuring as I was
expecting:
1) The query is not getting its parameter passed to it, it's prompting for
one.
2) The query is not running once for each iteration of that line on the
report, it is only running once (or at least it is only prompting once).
3) The Count() function is returning the number of records in the report,
not the number of records returned by the query.

Can anyone help me solve this?

 

[Dale Fye]

I went back and looked at your original post, and realize that you may not
understand the difference between a Form and a Report. A form is usually
used for displaying and editing data, but you can have forms where all you
want to do is display the data. A Report is usually data formatted to be
printed on a page. The query I wrote for you will generate a list of all
your agents and the number of agents they have referred. Once you get this
query running, you can use it in either a report, or in a continuous form to
display this information on screen.

1. Open a new query, select Design view.

2. When it pops up a window to select a table, click Close without
selecting a table.

3. In the query window, go to the Query option in the main menu, select SQL
Specific, then select Data Definition.

4. Copy the text of my query and paste it in the data definition, then run
the query.

HTH
Dale

I have no idea what all that is or where it goes, but I tried the
following:

I copied it into notepad and replaced the line feeds with spaces so that
it
could be pasted into the control source for the text box on the report.

A yellow bang appears next to the control on the report design view
stating
that the control has an invalid control source.

Clicking on the yellow bang gives me a menu restating the invalid control
source, the 2nd line says "Expressions must begin with an equal sign".

Adding an '=' at the beginning of the control source line yields this
dialog
when you attempt to enter it:

"The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses."

Leaving the '=' out so that there is no interfering dialog and running the
report yields this dialog:

Syntax error (missing operator) in query expression '[Select T1].[ID,
T1].[Name, Count(T2].[ID) as Referrals FROM Agents T1 LEFT JOIN Agents T2
ON
T1].[ID = T2].[[Referral ID] GROUP BY T1].[ID, T1].Name'.

Sorry, but I'm completely clueless here.

Try the following:

Select T1.ID, T1.Name, Count(T2.ID) as Referrals
FROM Agents T1
LEFT JOIN Agents T2
ON T1.ID = T2.[Referral ID]
GROUP BY T1.ID, T1.Name

HTH
Dale

I have a table called Agents with these fields:
ID (autonumbered)
Name (text)
Referred By (number)

Referred By is used to point to the ID# of the agent that referred this
agent.

I have a select query called QueryReferred with the following column:
Field: Referred BY
Table: Agents
Criteria: [AgentID:]

This query will find all of the agents who were referred by the
supplied
agent ID.

Now I want to generate a report that lists each agent and the number of
agents they have referred.

Next to their name on the report form, I have a text box to display the
count. My really clueless attempt at expression writing led me to
believe the
following would work:
=Count( Queries!QueryReferred!AgentID=[ID] )

Seems logical to me, but the following things are NOT occuring as I was
expecting:
1) The query is not getting its parameter passed to it, it's prompting
for
one.
2) The query is not running once for each iteration of that line on the
report, it is only running once (or at least it is only prompting
once).
3) The Count() function is returning the number of records in the
report,
not the number of records returned by the query.

Can anyone help me solve this?

 

[Tergiver]

Thank you! That's perfect =)

I went back and looked at your original post, and realize that you may not
understand the difference between a Form and a Report. A form is usually
used for displaying and editing data, but you can have forms where all you
want to do is display the data. A Report is usually data formatted to be
printed on a page. The query I wrote for you will generate a list of all
your agents and the number of agents they have referred. Once you get this
query running, you can use it in either a report, or in a continuous form to
display this information on screen.

1. Open a new query, select Design view.

2. When it pops up a window to select a table, click Close without
selecting a table.

3. In the query window, go to the Query option in the main menu, select SQL
Specific, then select Data Definition.

4. Copy the text of my query and paste it in the data definition, then run
the query.

HTH
Dale

I have no idea what all that is or where it goes, but I tried the
following:

I copied it into notepad and replaced the line feeds with spaces so that
it
could be pasted into the control source for the text box on the report.

A yellow bang appears next to the control on the report design view
stating
that the control has an invalid control source.

Clicking on the yellow bang gives me a menu restating the invalid control
source, the 2nd line says "Expressions must begin with an equal sign".

Adding an '=' at the beginning of the control source line yields this
dialog
when you attempt to enter it:

"The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses."

Leaving the '=' out so that there is no interfering dialog and running the
report yields this dialog:

Syntax error (missing operator) in query expression '[Select T1].[ID,
T1].[Name, Count(T2].[ID) as Referrals FROM Agents T1 LEFT JOIN Agents T2
ON
T1].[ID = T2].[[Referral ID] GROUP BY T1].[ID, T1].Name'.

Sorry, but I'm completely clueless here.

Try the following:

Select T1.ID, T1.Name, Count(T2.ID) as Referrals
FROM Agents T1
LEFT JOIN Agents T2
ON T1.ID = T2.[Referral ID]
GROUP BY T1.ID, T1.Name

HTH
Dale

:

I have a table called Agents with these fields:
ID (autonumbered)
Name (text)
Referred By (number)

Referred By is used to point to the ID# of the agent that referred this
agent.

I have a select query called QueryReferred with the following column:
Field: Referred BY
Table: Agents
Criteria: [AgentID:]

This query will find all of the agents who were referred by the
supplied
agent ID.

Now I want to generate a report that lists each agent and the number of
agents they have referred.

Next to their name on the report form, I have a text box to display the
count. My really clueless attempt at expression writing led me to
believe the
following would work:
=Count( Queries!QueryReferred!AgentID=[ID] )

Seems logical to me, but the following things are NOT occuring as I was
expecting:
1) The query is not getting its parameter passed to it, it's prompting
for
one.
2) The query is not running once for each iteration of that line on the
report, it is only running once (or at least it is only prompting
once).
3) The Count() function is returning the number of records in the
report,
not the number of records returned by the query.

Can anyone help me solve this?

 

[Tergiver]

How do I use that query in a report to print the number of referrals?

I generate a report with the wizard to only output the name field from
Agents table. Then in the detail section (in design mode) next to the text
box for Name that the wizard created I added another text box.

I named the query Referrals and I put:
=Referrals!Referrals
into the control source of the new text box. Running the report gets me a
prompt from the query for Referrals.

I need a printed piece of paper listing the info generated by that query
(not my fault, some people like to see things on paper).

I went back and looked at your original post, and realize that you may not
understand the difference between a Form and a Report. A form is usually
used for displaying and editing data, but you can have forms where all you
want to do is display the data. A Report is usually data formatted to be
printed on a page. The query I wrote for you will generate a list of all
your agents and the number of agents they have referred. Once you get this
query running, you can use it in either a report, or in a continuous form to
display this information on screen.

1. Open a new query, select Design view.

2. When it pops up a window to select a table, click Close without
selecting a table.

3. In the query window, go to the Query option in the main menu, select SQL
Specific, then select Data Definition.

4. Copy the text of my query and paste it in the data definition, then run
the query.

HTH
Dale

I have no idea what all that is or where it goes, but I tried the
following:

I copied it into notepad and replaced the line feeds with spaces so that
it
could be pasted into the control source for the text box on the report.

A yellow bang appears next to the control on the report design view
stating
that the control has an invalid control source.

Clicking on the yellow bang gives me a menu restating the invalid control
source, the 2nd line says "Expressions must begin with an equal sign".

Adding an '=' at the beginning of the control source line yields this
dialog
when you attempt to enter it:

"The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses."

Leaving the '=' out so that there is no interfering dialog and running the
report yields this dialog:

Syntax error (missing operator) in query expression '[Select T1].[ID,
T1].[Name, Count(T2].[ID) as Referrals FROM Agents T1 LEFT JOIN Agents T2
ON
T1].[ID = T2].[[Referral ID] GROUP BY T1].[ID, T1].Name'.

Sorry, but I'm completely clueless here.

Try the following:

Select T1.ID, T1.Name, Count(T2.ID) as Referrals
FROM Agents T1
LEFT JOIN Agents T2
ON T1.ID = T2.[Referral ID]
GROUP BY T1.ID, T1.Name

HTH
Dale

:

I have a table called Agents with these fields:
ID (autonumbered)
Name (text)
Referred By (number)

Referred By is used to point to the ID# of the agent that referred this
agent.

I have a select query called QueryReferred with the following column:
Field: Referred BY
Table: Agents
Criteria: [AgentID:]

This query will find all of the agents who were referred by the
supplied
agent ID.

Now I want to generate a report that lists each agent and the number of
agents they have referred.

Next to their name on the report form, I have a text box to display the
count. My really clueless attempt at expression writing led me to
believe the
following would work:
=Count( Queries!QueryReferred!AgentID=[ID] )

Seems logical to me, but the following things are NOT occuring as I was
expecting:
1) The query is not getting its parameter passed to it, it's prompting
for
one.
2) The query is not running once for each iteration of that line on the
report, it is only running once (or at least it is only prompting
once).
3) The Count() function is returning the number of records in the
report,
not the number of records returned by the query.

Can anyone help me solve this?

 

[Tergiver]

Never mind, I'm an idiot. Thanks again for all your help.

How do I use that query in a report to print the number of referrals?

I generate a report with the wizard to only output the name field from
Agents table. Then in the detail section (in design mode) next to the text
box for Name that the wizard created I added another text box.

I named the query Referrals and I put:
=Referrals!Referrals
into the control source of the new text box. Running the report gets me a
prompt from the query for Referrals.

I need a printed piece of paper listing the info generated by that query
(not my fault, some people like to see things on paper).

I went back and looked at your original post, and realize that you may not
understand the difference between a Form and a Report. A form is usually
used for displaying and editing data, but you can have forms where all you
want to do is display the data. A Report is usually data formatted to be
printed on a page. The query I wrote for you will generate a list of all
your agents and the number of agents they have referred. Once you get this
query running, you can use it in either a report, or in a continuous form to
display this information on screen.

1. Open a new query, select Design view.

2. When it pops up a window to select a table, click Close without
selecting a table.

3. In the query window, go to the Query option in the main menu, select SQL
Specific, then select Data Definition.

4. Copy the text of my query and paste it in the data definition, then run
the query.

HTH
Dale

I have no idea what all that is or where it goes, but I tried the
following:

I copied it into notepad and replaced the line feeds with spaces so that
it
could be pasted into the control source for the text box on the report.

A yellow bang appears next to the control on the report design view
stating
that the control has an invalid control source.

Clicking on the yellow bang gives me a menu restating the invalid control
source, the 2nd line says "Expressions must begin with an equal sign".

Adding an '=' at the beginning of the control source line yields this
dialog
when you attempt to enter it:

"The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses."

Leaving the '=' out so that there is no interfering dialog and running the
report yields this dialog:

Syntax error (missing operator) in query expression '[Select T1].[ID,
T1].[Name, Count(T2].[ID) as Referrals FROM Agents T1 LEFT JOIN Agents T2
ON
T1].[ID = T2].[[Referral ID] GROUP BY T1].[ID, T1].Name'.

Sorry, but I'm completely clueless here.

:

Try the following:

Select T1.ID, T1.Name, Count(T2.ID) as Referrals
FROM Agents T1
LEFT JOIN Agents T2
ON T1.ID = T2.[Referral ID]
GROUP BY T1.ID, T1.Name

HTH
Dale

:

I have a table called Agents with these fields:
ID (autonumbered)
Name (text)
Referred By (number)

Referred By is used to point to the ID# of the agent that referred this
agent.

I have a select query called QueryReferred with the following column:
Field: Referred BY
Table: Agents
Criteria: [AgentID:]

This query will find all of the agents who were referred by the
supplied
agent ID.

Now I want to generate a report that lists each agent and the number of
agents they have referred.

Next to their name on the report form, I have a text box to display the
count. My really clueless attempt at expression writing led me to
believe the
following would work:
=Count( Queries!QueryReferred!AgentID=[ID] )

Seems logical to me, but the following things are NOT occuring as I was
expecting:
1) The query is not getting its parameter passed to it, it's prompting
for
one.
2) The query is not running once for each iteration of that line on the
report, it is only running once (or at least it is only prompting
once).
3) The Count() function is returning the number of records in the
report,
not the number of records returned by the query.

Can anyone help me solve this?