[b]Count of Fields in Query[/b]

[blindlemonray]

I have a slight problem with a query design. I am trying to count the number of items in a field when several criteria are met. Two fields I need to count are branch and refer. the criteria is :- Branch (is not null) and Refer (is not Null). But what i need is both of the above but on a YTD and between two dates so i end up with

Branch
Count of Branch
Count of Refer

I have been trying something like

SELECT tblOS_Data.txt_Branch, Count(tblOS_Data.txt_Branch) AS CountOftxt_Branch, Count(tblOS_Data.chk_referred) AS CountOfchk_referred
FROM tblOS_Data
WHERE (((tblOS_Data.txt_Branch) Is Not Null))
GROUP BY tblOS_Data.txt_Branch;

But obviously all i get is same count for branch and refer!

Any pointers appreciated?

 

[PHV]

but on a YTD and between two dates
No dates at all in the code you posted.
Can you elaborate ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blindlemonray]

Sorry PH.. i've just realised this thread acutally doesn't make sense... been up all night with my son (1 week old)!! need more coffee..

Well below is an example of code used to get a count of cases between two dates.

SELECT tblOS_Data.txt_Branch, Count(tblOS_Data.txt_Branch) AS CountOftxt_Branch INTO tbl_dash_week_leads
FROM tblOS_Data
WHERE (((tblOS_Data.dte_Case_Ownwer_ds) Between [Forms]![frm_report]![from] And [Forms]![frm_report]![to]))
GROUP BY tblOS_Data.txt_Branch
HAVING (((tblOS_Data.txt_Branch) Is Not Null));
[code/]

see, a case will be put onto the system and then that case can also be a referral. Hence, count of branch, count of referral. its for a report that shows headings

Branch
count of branch (week)
Counf of referral (week)
count of branch (ytd)
Count of referral (ytd)

Bear with me if this also makes no sense!!

 

[PHV]

How do tou know a case is a branch and/or a referral ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blindlemonray]

I have several fields... ones taken into account are:-

dte_Case_Ownwer_ds (date case is entered)
txt_Branch (gross lead)
chk_referred (gross lead that has become referral, this chk also has a datestamp)

The code above is only getting gross leads :

dte_Case_Ownwer_ds (date case is entered)
txt_Branch (gross lead)

Everytime I try and count referrals I end up with the same number against the branches in each column.

 

[PHV]

I try and count referrals
Sum(IIf(IsNull(chk_referred),0,1)) AS CountOfReferrals

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244