Query that divides dates and calculates based on months

[keeling1]

I have what seems to me to be a difficult query problem. I hope someone can help, as I'm a beginner.

The database I'm building is to help a company repair its machines. When a machine breaks, a 'tag' is entered in the database. Then, when the machine is fixed, the user re-enters the database and 'closes' the tag by checking a box. So I have a data table that posts for each tag whether it's closed or still open.

Now, what I want as the final product is to be able to track with a line graph how many tags were opened vs. how many were closed during a particular month. So the user enters (via a form) the beginning and end dates. For instance, 1 Jan. 2008 and 31 Dec. 2008. What I need the query to do is to divide that into months and track the data (open vs. closed tags) by month. It would then return a result for open tags vs. closed tags for January, February, etc. This would then become a single graph, showing the data points for each month.

There are several steps here, but the one that's giving me the most trouble at the beginning is how to get the query to divide the time period the user enters, and then do the calculations.

Thoughts on how to do this? Thanks in advance. Or is there another, better way to this this?

 

[Galaxiom]

Check out the DatePart function.

 

[DCrake]

You will need to create a crosstab query that groups by the month of the date fields and counts the number of records for each group. You do this for each start and end date fields.


Then you should have two queries which can then be used as the source for your graphs. remember to watch out for months that do not have any data in them.

David

 

[keeling1]

Thanks for your replies, DCrake and Galaxiom. I can see, I think, how and why to use the DatePart function. But I'm not sure why it will help me to use a crosstab query. I've never used one before, so I'm really not clear how they work. The online descriptions I've found aren't very helpful.

Why shouldn't I just do it this way?: In a normal Select query, I have the database select only the records that were opened before the End Date entered by the user. (This eliminates the irrelevant records.) Then, this opens two new select queries. Using DatePart, one query divides the time period entered by the user into months and calculates for each month how many tags were Open during it. The other divides the time into months and then calculates how many tags were Closed during it. Finally, the results from both queries are plotted on a single graph by month.

I haven't tried doing it this way because there are a few things I don't know how to do, like making a query based on the results from another query. But I also have the feeling that this won't work, since DCrake recommended using a crosstab query.

Can anyone give me some more advice? I really appreciate this help.