How to use Count Function for a set amount of Dates

[greeni91]

Hi There,

I am currently having a problem with my database. I have a table that uses Lookup fields, to check another table, which is linked to my reporting form. I am trying to do a query where I search for a value in my main table's lookup field and sort it by a select amount of dates (sort monthly) but when I use the count function to see how many of the values are in the table for the select month, I get back a long table with every date seperate and the value "1" for the count. I want to to see this data as e.g. "January - ECR = 17, NCR = 32, Both = 78". NCR, ECR and Both are the values from my lookup field (Both is where NCR and ECR are selected on the same record, not the sum of both). I can't seem to get my head around the problem here. I thought it may be my Dates because they are in the format "12/12/2008" and I want the results to say December. Any help here will be truly appreciated.

Thanks in Advance

/Sandy

[greeni91]

Problem Number 2:

I am trying to count the amount of reports per person in my database. I have a way of locking out fields in my form and when all 6 are locked I say that the report is closed. What I am trying to do is to count the amount of open records by the total amount of records that that person has in the form. I am having extreme difficulty in understanding the count function. I even have a copy of access 2003 for dummies at the work and this hasn't help much (on this case only).

Help much appreciated,

/Sandy

[ajalwaysus]

Please post your code, so we can see what you are doing.

-AJ

[greeni91]

Problem 1: I have set two parameters in this query called "date to" and "date from". I am not sure whether I need these or not as I am looking to change my dates from "1/1/09" - "31/1/09" = January, "1/2/09" - "28/2/09" = February, etc, and I am wanting to count the Number of NCR's for each month to get a look to see if there is a pattern to the amount we have had. My code is as follows:

1. PARAMETERS [Date From] DateTime, [Date To] DateTime;
2. SELECT [Problem Reporting Table June 2008].[Problem Report No], Count([Problem Reporting Table June 2008].[ECR/NCR]) AS [CountOfECR/NCR], [Problem Reporting Table June 2008].[Date Raised]
3. FROM [Problem Reporting Table June 2008]
4. GROUP BY [Problem Reporting Table June 2008].[Problem Report No], [Problem Reporting Table June 2008].[Date Raised]
5. HAVING (((Count([Problem Reporting Table June 2008].[ECR/NCR])) Like "2") AND (([Problem Reporting Table June 2008].[Date Raised])>=[Date From] And ([Problem Reporting Table June 2008].[Date Raised])<=[Date To]))
6. ORDER BY [Problem Reporting Table June 2008].[Problem Report No];

This was taken from my SQL view of my query as I thought it would be more helpful than describing the design view.

Thanks

/Sandy

[greeni91]

Problem 2: I can't even begin to think how to set up this query in the slightest so I am going to have to explain it a little better...

I am looking to make a query where it counts the total amount of Problem Reports by personnel, I want to do a count where I count the number of open actions over the total amount of Problem Reports this person has. I know I would need a parameter called [Name] to input the persons details but I don't know how to set up the count. To count the total number of open actions I need to use 6 fields from my table... If any one of these (or a few of these) yes/no boxes are unticked against that persons name I want it to say that the person has one open action against them. I really need a lot of help on this one and it will be really appreciated.

Thanks,

/Sandy