1. Registered User
Join Date
Nov 2002
Posts
59

Hi folks...

I have an Access database in such structure:

SELECT VerticalMarket.VerticalName, CallRecord.CompanyVertID, Count(CallRecord.NextCallDate) AS CountOfNextCallDate
FROM CallRecord INNER JOIN VerticalMarket ON CallRecord.CompanyVertID = VerticalMarket.VerticalID
WHERE (((CallRecord.NextCallDate) Between [StartDate] And [EndDate]) AND ((CallRecord.Inhouse)=-1))
GROUP BY VerticalMarket.VerticalName, CallRecord.CompanyVertID;

When this one works as a query it's bringing me how many calls made between that "StartDate and EndDate" for each group of Vertical Market and the ones that Inhouse = -1

VerticalName ----- CompanyVertID ------ CountOfNextCallDate
--------------- ----------------- ---------------------
AAAAAAAAA --------- AA --------- ------ 17
BBBBBBBBBB --------- BB --------- ------ 25
CCCCCCCCC --------- CC --------- ------ 13
DDDDDDDDD --------- DD --------- ------ 15

But I also wanna show other counts in same lines
For example without considering what is inhouse whether -1 (Count2) or not and all datas belongs to particular VertID (Count3)

so it'll be something like that
VerticalName CompanyVertID CountOfNextCallDate Count2 Count3
------------- ----------------- -------------------- ---------- --------
AAAAAAAAA AA 17 23 55
BBBBBBBBBB BB 25 35 42
CCCCCCCCC CC 13 17 29
DDDDDDDDD DD 15 25 38

And then I'll use it in my report as Record Source.

So what basically that table says.
- Total record number is 164 (Sum of Count3)
- and 55 of them belongs to AA, 42 of them belongs to BB so on...
- Count2 tells how many records AA has where inhouse = -1
- CountofNextCallDate tells how many data in definite time as AA has 23 records between definite date.

So 3 counts in 1 summary with different criterias. I hope it's clear enough.

Last edited by cuneyt; 10-19-05 at 02:40.

2. Registered User
Join Date
May 2005
Posts
119
you could use =sum(iif([inhouse]=-1,1,0)) to get your inhouse counts for each grouping.
Last edited by Krista327; 10-19-05 at 10:36.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•