Help on counting Dates

[Shift838]

I am trying to count how many times a date appears out of a query I created.

My query has the follwoing tables
Date Opened
Date Closed
Type

I want to count how many times a date appears in the date opened and date closed. I beleive I have do another query to do this. But I am very new to access and have no clue what I am doing. I will eventually put the results in a report. Any help would be great..

 

[Dave1367]

Hi,

There are a couple of different ways that you can do this, depending on what type of result you are expecting.

If you wanted to know the different dates in the date Opened column (I assume you meant a column/field which is in a table), you could use the following query (I tend to use the SQL View rather than the design view)

select [Date Opened], count (*) as occurrences
from <table name>
group by [Date Opened]

this will give you a table like:

Date Opened occurrences
12/01/2003 5
13/01/2003 10
14/01/2003 1

If you meant both the date opened and date closed at the same time then you would use:

select [Date Opened], [Date Closed], count (*) as occurrences
from <table name>
group by [Date Opened], [Date Closed]

The same sort of output would be given:

Date Opened Date Closed occurrences
12/01/2003 13/01/2003 2
12/01/2003 14/01/2003 3
13/01/2003 13/01/2003 10
14/01/2003 15/01/2003 1


If you just wanted a basic count of how many records had a date in the Date Opened field then use:

select count(*) from <table name>
where [Date Opened] is not null

And again if you meant both at the same time then add an extra clause:

select count(*) from <table name>
where [Date Opened] is not null
and [Date Closed] is not null

Hope one of those helps !!


Dave

 

[Shift838]

This helps but I am trying to access the data that I already filtered through a query. I get errors trying it that way because it seems to be trying to look for another database with my other query name.

 

[Shift838]

I was able to develop two queries to do what I need, but I would like one. I used some of the mentioned code in this thread and was able to create a query to pull the total date opened tickets and count them and give me a total of what they were in a different column. What I need to do is pull both date opened and date closed and have two additional colums in the query to show total opened and total closed on those dates. I also found out that when I run the date closed query that any table that does not have a date it shows up as blank. These tables are Tickets remaind opened. Is there a way to get this into its own column and out of the date closed column.