Count Challenge

[Ripley]

I have a problem!

I have a table, tbl_Members.

tbl_Members contains many fields, two being SName and RefferedBy.

ReferredBy is stored in the table from a lookup from a form that looks up the SName field from tbl_Members.

What i want to do is create a query from this table with an extra field which counts the number of instances based on the records SName, in a field called NoReffers.

For example, i want a query that looks like:

qry_tbl_Members:

-----SName-----ReferredBy-----NoReffers---
-----Bloggs--------Hills-------------1-------
------Hills--------Barnard-----------2-------
-----Smith--------Bloggs----------- 0------
----Truman--------Hills-------------1------
---Williamson-----Truman-----------0------

I 've been looking into the Count() function, but cant seem to work it out!

Can someone help me?

 

[Dennisk]

Create a new query then turn it into a totals query View|Totals. One of the rows allows you to specify group by, count, sum, min, max,first, last, where, expression.

what you need is to count referrers and group by sName.

 

[Ripley]

Forgive me, im rather novice at this, but i cant seem to get it to work.

Can you go through it step by step?

 

[Ripley]

Please, can anyone help me?

 

[Brianwarnock]

From your sample I think that you need to GroupBy sname and refferedby with anew field in the query NoReffers: Count(*)

Brian

 

[Ripley]

No, im sorry this still dosent help me, anyone got any ideas?

 

[Brianwarnock]

I think that your question is not the one that we are trying to solve. Why? because your sample result shows

-----Smith--------Bloggs----------- 0------

how can you have a refferredby and yet no refferrals?

Brian

 

[Ripley]

the number of reffers field (NoReffers) is simply how many instances that the member has referred someone.

So no every member may have a number of reffers (they have no referred someone), but everyone will have been referred by someone, adding to their NoReferrs.

 

[Brianwarnock]

I thought so that is not the problem we were solving

Query1 groupby referredby NoRefferrs:Count(*)

query2 Join table sname to query1 Refferredby

select Sname and Refferredby from table and Noreffers from query1

run query2

Brian

 

[Ripley]

This seems to work, i can get a count of the values, but i cant seem to put a query together of all the fields from the table and then NoReffers from the query, it says it cant join the fields.

Any ideas?

 

[Brianwarnock]

cant think why you cannot join sname and refferredby, but have realised that it needs to be a left(or right) join to pull all the records from the table even if there is no referredby in the query1 result, which also means that you need to handle the null Norefers

numrefers:If(isnull(Norefers),0,norefers) in a new field rather than selecting norefers from the query.

Brian