Count for two different possible values in the same field

Thor

New member
Local time
Yesterday, 19:00
Joined
May 3, 2006
Messages
4
I'm trying to make a report that shows three fields: Date, Company, and Track. The 'track' field can only have two values, sales or technical. In this report I need to show for each month, and for each company, how many records exist for sales, and how many exist for technical.

So far, I can only manage to count all values for track, but I'd like to specify sales or technical. Any ideas?
 
You might try a query, something like :

Dcount([Company], Yourtable,[track] like sales*)
and
Dcount([Company], Yourtable,[track] like technical*)

Hth
 
I'm gonna admit it right now, I'm not very experienced with tweaking reports so be patient.

Rak, the problem with your queries is that they count all the records with track being 'sales' (or in the other case 'technical') in the entire table. I'm trying to show monthly totals.

I have my report grouped by Date, then Company, then Date again.

With prompting from the office assistant (so I'm not too sure of the reasoning behind these actions), I had created an invisible text box in the details section of the report, and set the properties to running sum by group, and control source =1, and named it RecordCount. Then in the Company footer, I created another text box that referenced RecordCount in the control source. This gave me totals for each company for each month. Now what I want to do is separate the totals into sales and technical.

I hope that's not too confusing of a synopsis.
 
Last edited:
Assuming your data looks like this:

ID Date Company Track
1 1/1/2006 "comp 1" Technical
2 2/1/2006 "comp 2" Sales
3 2/1/2006 "comp 3" Technical
4 2/1/2006 "comp 2" Sales
5 1/31/2006 "comp 2" Sales

Then either

a: you can use a crosstab query such as :

(My data is in a table called Table1)

TRANSFORM Count(Table1.ID) AS CountOfID
SELECT Format([Date],"yyyymm") AS [Month], Table1.Company
FROM Table1
GROUP BY Format([Date],"yyyymm"), Table1.Company
ORDER BY Format([Date],"yyyymm"), Table1.Company
PIVOT Table1.Track In ("Sales","Technical");

Which will get an output of:

Month Company Sales--- Technical
200601 comp 1 --------- 1
200601 comp 2 --1
200602 comp 2 --2
200602 comp 3 --------- 1

-- added as placeholders

Then you can create a form with 4 text boxes one for each column and it's easy to add a row total to see total tracks for a company in a month.

Or B:

You can use an ordinary select query such as :

SELECT Table1.Track, Format([Date],"yyyymm") AS [Month], Table1.Company, Count(Table1.ID) AS CountOfID
FROM Table1
GROUP BY Table1.Track, Format([Date],"yyyymm"), Table1.Company
ORDER BY Table1.Track, Format([Date],"yyyymm"), Table1.Company;

Which will get an output of :

Track Month Company CountOfID
Sales 200601 comp 2 1
Sales 200602 comp 2 2
Technical 200601 comp 1 1
Technical 200602 comp 3 1

NB you do need to ensure that you do not allow anything with a track that is not in "Technical or Sales" as they will be screened from the results of the crosstab.


hth

kevin
 
Last edited:
Two textboxes =Iif([Track]="Sales",1,0)) repeat for Technical, either use the RunningSum property for each box or two Sum(Iif( statements
 

Users who are viewing this thread

Back
Top Bottom