Counting records in Table from a Report

[coach.32]

I have a Table that contains Headings/Criteria such as
Team * Type * Status * Priority* - The data could be
1753 - NEW - Medium - Low
1753 - ADD - Medium - High
1744 - NEW - Standard - ASAP - How can I count the records that match the required criteria (eg: 1753 - NEW - Medium - High) from a Report. If I had to create queries I would have to create over 200. I have tried using DCount but I am having trouble with it. Could anyone enlighten me as to how I may go about this? Your guidance would be greatly appreciated.

 

[PNGBill]

Are you wanting to have a control on your Report show the Count of records that meet the criteria for that report ?

DCount should work. Can you post the error message or explain how you used it.

You could use a query or even include the data in your report's Data source query / sql

Do you have a report that requires One Count only but each time for different criteria ? or are there a number of counts required on the one print of the report ?

 

[coach.32]

The report needs to show a number of counts on the one page. I originally had the report sourcing from a number of queries but there was just too many queries and I hadn't even completed all the criterion. The report is a daily listing of the work I have on hand, so the counts will change each day. Because I need multiple criteria, when I write the DCount formula does it look something as such: DCount("[Team]=1743", AND "[Type]=NEW", AND "[Status]=Medium") just as an example. The last time I tried to write the formula I didn't include AND.

 

[PNGBill]

Try and post your DCount. Use the hash symbol above to show it as code.

I understand Dcount will be slower then a query. Very handy where one or two form or report controls require some data that is not easily added to the record source but where it is in volume you may well find the data better handled by the record souce query / sql

Don't be afraid of large record source querie's / sql's.

A simple solution could be to have a number of queries do the counts and they are pulled into the record source query and you have all your report data available in the one sql / query.

In a form, you could use Sub Forms to handle some of this data.
I understand Reports can have Sub Reports, if they work the same as sub forms, then this may be a solution to your issue. (we mostly use Forms)

As far as amount of work... you wll be shocked at the amount of work required to have a good database perform but, once done, it does repay itself, until you think of the next enhancment project and then it all starts again

 

[coach.32]

This is the DCount I tried in the RecordSource of a Text box on the report # =Dcount("[Team_Id]","1743","[Acty_Cntct_Typ_Nm]='REG Compromised TFN' AND [Intractn_Sts]='Information Pending' AND [Acty_Priority]='3-Medium' AND [Acty_Cmplxty]='Low'") # I have obviously a lot to learn about Access but I do enjoy using it and seeing what it can produce. Your comment "A simple solution could be to have a number of queries do the counts and they are pulled into the record source query and you have all your report data available in the one sql / query" interests me, but I'm not sure how to do that. Thank-you for helping and being patient.

 

[PNGBill]

=Dcount("[Team_Id]","1743","[Acty_Cntct_Typ_Nm]='REG Compromised TFN' AND [Intractn_Sts]='Information Pending' AND [Acty_Priority]='3-Medium' AND [Acty_Cmplxty]='Low'")

 

[PNGBill]

clik on the hash key symbol next to the php symbol and then put the code between the keys that appear in your post.

 

[PNGBill]

You will find spaces in table and field names to be of no purpose except to make your code task more difficult then it sometimes already is.

Please list your table names and field names that relate to one instance of a count being required.

I will try and post an sql that will achieve this.

 

[coach.32]

Dcount("[Team]","1743","[Type]='REG Compromised TFN' AND [Status]='Information Pending' AND [Priority]='3-Medium' AND [Cmplxty]='Low'")

The name of the Table is - 'Holdings'
The Field names within the table are - 'Team' - 'Type' - 'Status' - 'Priority' - 'Cmplxty'.
The original report is imported from Excel and that has been generated from a Data Warehouse. Hence the spaces.