Counting selections in a list box - Help for Homeless Shelter

[lk12]

Hi guys I am new here, I am a university student helping out in a homeless shelter that has lots of admin work. Currently the Excel system they use is slow and laborious, as well as full of errors, and I am creating a basic Access system to help.



One main laborious task is to count the amount of records that relate to a specific type of support. eg. how many people were given a food package, how many people were referred to a hostel etc. I have created a list box that will store around 30 different types of support, and what I am hoping to do is find a way of automatically counting how many different types of support are logged each month.

The only way I can think of doing this is by running a query for each type of support, for every month, for every year, but this would mean I would have to create around 360 queries a year!

Could someone please help?
Thanks.

[pbaldy]

It would help to know your table structure. I'm thinking one parameter query.

[lk12]

Thanks for the reply, I have thought of one way, maybe you could have a look and suggest some help.

I have created a form named 'Calculate Totals'. There are 2 datepicker fields name 'Start Date' and 'End Date', which are used as criteria and allow the user to specify the date range.

Each type of support will have its own query, with itself as the 'Type of Support' criteria, and the date range of between 'Start Date' and 'End Date' as criteria.

I have created the layout form but its not fully functioning, I somehow want the 'Calculate' button to run a macro that will run all the queries, count how many records are returned in each query, and put this value into the relvant text box on the form.

I can't attach my database as its too big, and can't post a link as i'm new.

[pbaldy]

Why wouldn't you also have type of support as a criteria, rather than have a query for each type? That will prove difficult to work with.

[lk12]

Why wouldn't you also have type of support as a criteria, rather than have a query for each type? That will prove difficult to work with.

Sorry i explained it a bit bad, each type of support does have a type of support for criteria.
There are around 30 types of support, so 30 queries. Each query uses the date range submitted by the user as criteria for the 'Date' range in the query.
Also it will use itself as criteria for the 'Type of Support' field, so for example it will have 'food donation' for criteria in the 'Type of Support' field if the query is searching for food donation records.

I have pm'ed you the link to my database, it is only a very basic barebone system, i plan to design it once it is fully functioning.
Thanks.

[lk12]

The reason I created queries for each type of support is that I would like seperate queries to produce seperate values, that will all display at the same time on the form, saving time of having to run each query for the 30 different types of support.

[pbaldy]

I noticed you cross posted this elsewhere, so I won't spend much time on it. I'd use the totals query given to you on another site to display all the values. You don't want to have 30 queries.

[pbaldy]

For anyone else following or looking for a solution, OP reported elsewhere that this query solved the problem:

SELECT TableName.SupportType, Count(TableName.ID) AS SupportCount
FROM TableName
WHERE (((TableName.SupportDate) Between [Enter start date:] And [Enter end date:]))
GROUP BY TableName.SupportType;

Which is the totals query mentioned above.