Subtotal Count

[veritoanimus]

So, I do a lot of Database programming, but normally I'm working with a web front-end. Because of this I've taken the approach of having the ASP.NET code to a lot of the work and never really got into more complex queries. At this point I have a project that's a little different though and I don't have the luxury of making anything external, so everything is in Access 2010.



I have a table that contains the following:

ID, Category, Name, DateAdded, ...(Other Data)


What I need is a query that will give me a listing that will output as:

Unique Category, Count of Items in Category, Earliest Date for Items in Category.

I was thinking I'd probably have to do this as a stored query that creates a temporary table, but I'm thinking that there has to be an easier way, and that it's probably been done enough times for different things that someone probably knows the query pretty well. This is being used as a work queue if that helps at all.

Anyone here ever have to do something like this before and know a nice way to do it?

[jzwp11]

Welcome to the forum!

All you would need to do is to create (and save) the query. When you need to see the data just run the query. Access will display the results in a basic datasheet view. There would be no need to make another table. You could also bind the query to a form. When you open the form, Access will automatically run the query and display the results in the form (you use the form to format the data the way you want).

It sounds like you will need an aggregate query

SELECT category, Count(category), Min(dateadded)
FROM yourtablename
GROUP BY category

[veritoanimus]

Welcome to the forum!

All you would need to do is to create (and save) the query. When you need to see the data just run the query. Access will display the results in a basic datasheet view. There would be no need to make another table. You could also bind the query to a form. When you open the form, Access will automatically run the query and display the results in the form (you use the form to format the data the way you want).

It sounds like you will need an aggregate query

SELECT category, Count(category), Min(dateadded)
FROM yourtablename
GROUP BY category

Thanks a ton, I'm glad I posted... that's a lot easier than I expected... I'll have to test it out.

[jzwp11]

You're welcome. Let us know if you encounter any problems in your testing.