Parse Data by Month

[dccjr]

I am wondering if there is an easy way to parse data from a table base on month, then count the records for each month. I will use this to create a trend chart. Any suggestions?

[alansidman]

To respond with a valid answer, we will need to understand the structure and makeup of the table. It certainly is possible to do what you want, but without details of your db, no further information can be expressed. We would only be guessing at a solution.

[lugnutmonkey]

I have done something similar, I used a totals query and on the field which contained the date(in my case it was called [Date/Time]) I used the expression

Months: MonthName(Month([Date/Time]),0)

I hope this helps

[dccjr]

I am generating a report based on a given date to present. The user will select a date (date picker on a text box). I then need to count all of the records that have a DefectDate from (and including) that date to the present. I dont actually need the records, just a count for each category/group as selected in a previous combo box. For example, the user may decide to run this report using the Category field. I need to count the number oif records in each category and use that information in a bar chart.

[alansidman]

In a query drag your date field to the grid twice. In the criteria for the first column with the date type
>= Forms!yourformName.yourcomboboxName

click on the Sigma in the ribbon to change the query from a select query to an aggregate query. In the second column holding the Date field, change the Group by to Count.

Run the query but because you are referencing a form for the criteria, make sure the form is also open or the query will fail.

[dccjr]

Sadly, I have never used the query builder in Access. I prefer to write the SQL in VBA. Can you give an example of how this would be done?

[alansidman]

If you look at this page, there are several videos on Queries, all using the QBE.

http://www.datapigtechnologies.com/AccessMain.htm

Alan