Count query

[marckk]

I need to have a count query that just gives me the number of records in a table. I'll need to set date parameters but I don't need date data, just a total record count and I can't use "TOTAL" because then it counts certain dates?

Any help or direction would be appreciated!!

Thanks!

 

[Alansidman]

Try using this

http://www.techonthenet.com/access/functions/numeric/count.php

Alan

 

[marckk]

Thanks,
That's not what I'm looking for, if I use count in the "Group By" section for totals then I'll just be counting the various dates. I need a record count not a total

 

[Alansidman]

I'm not understanding your needs apparantly. Please use a specific example of what you are trying to do with some sample data and expected results.

Alan

 

[marckk]

here is my data: CollectorID235999532359995323599953235999532359995323599953235999532359995323599953235999532359995323599953
If I just bring in CollectorID I can use "count" in the totals section no problem, but if I need to limit by a certain date ">03/01/2012" it gives a breakdown of the CollectorID totals by date, I need an overall total not an itemized total.

Hope I'm being clear?

 

[MSAccessRookie]

Thanks,
That's not what I'm looking for, if I use count in the "Group By" section for totals then I'll just be counting the various dates. I need a record count not a total

I will assume that your Table has a Primary Key Field, and if you GROUP BY that Field, the COUNT will provide you with the total number of records.

 

[marckk]

I will assume that your Table has a Primary Key Field, and if you GROUP BY that Field, the COUNT will provide you with the total number of records.

no primary key

 

[Alansidman]

Perhaps two queries. First one to set the parameter for the date criteria. Save that query and use it as a record source for an aggregate query and group on the ControlID and count the ControlID. ie. You have the controlID in the QBE twice.

BTW: I didn't understand the ControlID data. It appears to be one long repeating number. Very confusing.

Alan

 

[marckk]

Perhaps two queries. First one to set the parameter for the date criteria. Save that query and use it as a record source for an aggregate query and group on the ControlID and count the ControlID. ie. You have the controlID in the QBE twice.

BTW: I didn't understand the ControlID data. It appears to be one long repeating number. Very confusing.

Alan

I'll try that, thanks!

 

[marckk]

Quick question though, can I set the date parameter within the criteria of the CollectorID count query?

 

[RenaG]

Hi Alan,

I added the count column to my query as you suggested in a previous post. My query consists of these fields:

StartDate   EventName                   Desc         CancerType   Count of CancerType
10/01/2011 Magic Valley Hospital       Screening     Colon             1
10/22/2011 Local College Health Fair   Health Fair   Skin              1
10/28/2011 Memorial Hospital           Screening     Colon             1

I need to show total for each Cancer Type. In this case it would be:
Colon 2
Skin 1

How do I do that?

TIA!
~RLG

 

[RenaG]

Hi Pat,

Thank you for your reply. I am not quite getting how to use your query. The query that generated the result I previously posted looks like this:

SELECT tblEvent.startDate, 
    tblEvent.EventName, 
    lookupTable.Desc, 
    lookupTable_1.Desc, 
    lookupTable_1.Group, 
    Count(lookupTable_1.Desc) AS CountOfDesc
FROM (tblEvent INNER JOIN 
   (lookupTable INNER JOIN tblEventFormat 
    ON lookupTable.luID = tblEventFormat.ActivityID) 
    ON tblEvent.EventID = tblEventFormat.EventID) 
    INNER JOIN (tblEventTracking 
    INNER JOIN lookupTable AS lookupTable_1 
    ON tblEventTracking.tdescription = lookupTable_1.luID) 
    ON tblEvent.EventID = tblEventTracking.EventID
GROUP BY tblEvent.startDate, 
    tblEvent.EventName, 
    lookupTable.Desc, 
    lookupTable_1.Desc, 
    lookupTable_1.Group
HAVING (((tblEvent.startDate)>=[Forms]![frmScreeningEventDateParm]![BegDate] And (tblEvent.startDate)<=[Forms]![frmScreeningEventDateParm]![EndDate]) AND 
    ((lookupTable.Desc)="Screening Event" Or (lookupTable.Desc)="Health Fair/Screenings") AND 
    ((lookupTable_1.Group)="Cancer"))
ORDER BY tblEvent.startDate;

When I tried making this query the input to your query and put it as a subreport in the report footer, it didn't show any fields to pull on to the report. When I tried to duplicate this query but modify it for the count like your showed, it gave me an error saying I couldn't Group By on an aggregate function (or something like that).

Am I going the wrong direction with this? Or just not getting my ducks in the right row?

I look forward to your answer.

TIA!
~RLG

 

[RenaG]

Hi Pat,

I am so sorry that I didn't not communicate well enough in my first post what I need to do. The sample output I posted is from the query I showed in the post previous to this one. That query needs to stay the way it is; it reports the data the user wants to see. I was asked if there was a way to show the total number of each of the cancers shown in the report. That is what I was trying to ask for in my original post.

I was thinking that I would need a query that will display in the report footer but I can't figure out how to create one that uses the original query as the input "table".

Did I do any better explaining what I need?

~RLG

 

[RenaG]

Yippee!!! It works. Thanks so much for all your help.

Gratefully,
~RLG

 

[SURVIVO01]

Use crosstab query
Make cancer type your row
and column your event
count on cancer type

 

[RenaG]

I had thought that a cross-tab was the way to go but what Pat suggested produces the result that I need.

Thanks for replying!

~RLG