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.

 

[vbaInet]

You need to look into Grouped Sums in Queries.

The following link should provide some insight:

http://office.microsoft.com/en-us/access-help/sum-data-by-using-a-query-HA010096310.aspx

To group by Month, Year, Day etc, look into the following functions:

Month()
Day()
Year()
Format()
DateAdd()
DateSerial()
DateValue()

 

[Severin]

If your table was something like this:

ID, Name, SupportType, SupportDate

then a query could look like this:

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;

This will prompt you start and end date.

 

[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.

 

[Severin]

...and I have just been moderated for posting my email address!~)

 

[lk12]

...and I have just been moderated for posting my email address!~)

I will pm you the download link for my database.

 

[lk12]

I cant seem to find a way to private message :S

 

[Severin]

no.. it's a message board..

check my profile..

 

[lk12]

severin your sql code worked perfectly, thank's very much.

 

[Severin]

Glad to help!~)

 

[SaharaDawn]

lk12, I too am designing an access db for a homeless shelter. I have been gathering data to lay out the design before I create it. Any help would be greatly appreciated.

 

[vbaInet]

lk12, I too am designing an access db for a homeless shelter. I have been gathering data to lay out the design before I create it. Any help would be greatly appreciated.

Why don't you send him or her a Private Message? That way an e-mail notification gets sent too.