counting records

[blake7]

Hi, I set up a database for recording quality issues, the main input form
requires that you enter the details of the issue and enter the date the
issue occurred, is there any way you can count the number of records that
have been entered between 2 dates ie the first of the month and the last day
of the month ? I also have a tick box field on the form which the user ticks
when the issue has been closed, as well as counting the number of records i
would like to count the number of issues that are still open (unticked)
Thank You
Tony

 

[KARL DEWEY]

Try this ---
SELECT Count([YourTable].Primary_Key) AS Last_Month_Issues,
Sum(IIF([YourCheckBox]=0,1,0)) AS Open_Issues
FROM [YourTable]
WHERE ((([YourTable].[IssueDate]) Between Date()-Day(Date()) And
DateAdd("m",-1,Date()-Day(Date()))+1));

 

[Ken Sheridan]

Tony:

You can do it with a query which prompts for the start and end dates as
parameters. The following will give you the total number entered, the number
open and the number closed:

PARAMETERS
[Enter start date:] DATE TIME,
[Enter end date:] DATE TIME;
SELECT COUNT(*) AS [Total Entered],
SUM(IIF([Closed],0,1)) As [Total Open],
SUM(IIF([Closed],1,0)) As [Total Closed]
FROM [YourTable]
WHERE [YourDateField] BETWEEN
[Enter start date:] AND [Enter end date:];

Just paste the above into a new query in SQL view and change YourTable,
Closed and YourDateField to whatever your table and the two relevant fields
are called. When you run the query you'll be prompted for the start and end
date parameters.

One caveat: the above assumes that YourDateField always contains dates with
zero times of day. If the field might include dates with non-zero times of
day, e.g. if the Now() function has been used to insert the date/time Value,
then rows with dates on the last day of the range would be omitted from the
count. This is because a date entered as the parameter is actually the point
if time at midnight at the start of the day. If your field might include
such dates then you can make sure they are counted by defining the range
slightly differently. So if in doubt use this:

PARAMETERS
[Enter start date:] DATE TIME,
[Enter end date:] DATE TIME;
SELECT COUNT(*) AS [Total Entered],
SUM(IIF([Closed],0,1)) As [Total Open],
SUM(IIF([Closed],1,0)) As [Total Closed]
FROM [YourTable]
WHERE [YourDateField] >= [Enter start date:]
AND [YourDateField] < DATEADD("d",1,[Enter end date:]);

If you base a form and/or report on either of the above queries and when you
open the form or report you'll be prompted for the dates.

Ken Sheridan
Stafford, England

 

[blake7]

Hi Ken, thanks for your help, I have created the query and it runs fine
although the first thing it asks for is 'closed' ?? then asks for the start
date and the end date and runs ok, why is it asking for 'closed' ? , what
also do you mean by change "closed"
Thanks.Tony

Tony:

You can do it with a query which prompts for the start and end dates as
parameters. The following will give you the total number entered, the number
open and the number closed:

PARAMETERS
[Enter start date:] DATE TIME,
[Enter end date:] DATE TIME;
SELECT COUNT(*) AS [Total Entered],
SUM(IIF([Closed],0,1)) As [Total Open],
SUM(IIF([Closed],1,0)) As [Total Closed]
FROM [YourTable]
WHERE [YourDateField] BETWEEN
[Enter start date:] AND [Enter end date:];

Just paste the above into a new query in SQL view and change YourTable,
Closed and YourDateField to whatever your table and the two relevant fields
are called. When you run the query you'll be prompted for the start and end
date parameters.

One caveat: the above assumes that YourDateField always contains dates with
zero times of day. If the field might include dates with non-zero times of
day, e.g. if the Now() function has been used to insert the date/time Value,
then rows with dates on the last day of the range would be omitted from the
count. This is because a date entered as the parameter is actually the point
if time at midnight at the start of the day. If your field might include
such dates then you can make sure they are counted by defining the range
slightly differently. So if in doubt use this:

PARAMETERS
[Enter start date:] DATE TIME,
[Enter end date:] DATE TIME;
SELECT COUNT(*) AS [Total Entered],
SUM(IIF([Closed],0,1)) As [Total Open],
SUM(IIF([Closed],1,0)) As [Total Closed]
FROM [YourTable]
WHERE [YourDateField] >= [Enter start date:]
AND [YourDateField] < DATEADD("d",1,[Enter end date:]);

If you base a form and/or report on either of the above queries and when you
open the form or report you'll be prompted for the dates.

Ken Sheridan
Stafford, England

Hi, I set up a database for recording quality issues, the main input form
requires that you enter the details of the issue and enter the date the
issue occurred, is there any way you can count the number of records that
have been entered between 2 dates ie the first of the month and the last day
of the month ? I also have a tick box field on the form which the user ticks
when the issue has been closed, as well as counting the number of records i
would like to count the number of issues that are still open (unticked)
Thank You
Tony

 

[blake7]

Hi Ken, I have sorted it now it is running fine, i figured it out in the end
what you meant. once again thank you.
Regards. Tony

Tony:

You can do it with a query which prompts for the start and end dates as
parameters. The following will give you the total number entered, the number
open and the number closed:

PARAMETERS
[Enter start date:] DATE TIME,
[Enter end date:] DATE TIME;
SELECT COUNT(*) AS [Total Entered],
SUM(IIF([Closed],0,1)) As [Total Open],
SUM(IIF([Closed],1,0)) As [Total Closed]
FROM [YourTable]
WHERE [YourDateField] BETWEEN
[Enter start date:] AND [Enter end date:];

Just paste the above into a new query in SQL view and change YourTable,
Closed and YourDateField to whatever your table and the two relevant fields
are called. When you run the query you'll be prompted for the start and end
date parameters.

One caveat: the above assumes that YourDateField always contains dates with
zero times of day. If the field might include dates with non-zero times of
day, e.g. if the Now() function has been used to insert the date/time Value,
then rows with dates on the last day of the range would be omitted from the
count. This is because a date entered as the parameter is actually the point
if time at midnight at the start of the day. If your field might include
such dates then you can make sure they are counted by defining the range
slightly differently. So if in doubt use this:

PARAMETERS
[Enter start date:] DATE TIME,
[Enter end date:] DATE TIME;
SELECT COUNT(*) AS [Total Entered],
SUM(IIF([Closed],0,1)) As [Total Open],
SUM(IIF([Closed],1,0)) As [Total Closed]
FROM [YourTable]
WHERE [YourDateField] >= [Enter start date:]
AND [YourDateField] < DATEADD("d",1,[Enter end date:]);

If you base a form and/or report on either of the above queries and when you
open the form or report you'll be prompted for the dates.

Ken Sheridan
Stafford, England

Hi, I set up a database for recording quality issues, the main input form
requires that you enter the details of the issue and enter the date the
issue occurred, is there any way you can count the number of records that
have been entered between 2 dates ie the first of the month and the last day
of the month ? I also have a tick box field on the form which the user ticks
when the issue has been closed, as well as counting the number of records i
would like to count the number of issues that are still open (unticked)
Thank You
Tony