Help construct Report.

[djchapple]

I have been using Access 2003, then 2007 and now 2010 for some time but only at a very basic level.

I volunteer for a registered charity and I use the database to keep a record of all the volunteers at our nature reserve and to keep an ongoing record of all volunteering in the past, the present and the next two months or so.

The relevant part of the database has a line for each day and on that line are five columns.
1. record number (automatic)
2. day and date of month
3. general information (not relevant to my query)
4. morning - contains the name(s) of the one or two volunteers on duty that morning
5. afternoon - contains the name(s) of the one or two volunteers on duty that afternoon.

Within say the last year we need to get some idea as to who have been the active volunteers and how many sessions they are active.

So I need to
1. select a certain date range - I can do this easily using column 1.
2. select a single name to search column 4 or column 5 or both columns 4 and 5 together - not sure what is best or how to make a selection for two columns
3. count the number of appearances in the morning, afternoon or both morning and afternoon
4. create a report containing name of volunteer, list of dates and which session he/she volunteered and then at the end the number of sessions volunteered

I will then have to repeat this for all the volunteers on the list (about 70) so I am wondering whether I could automate the selection by names process and get one report covering all volunteers.

As several other volunteers use Access 2007 for other areas of the charity I would prefer to work with Access 2007.

As with all charities we are strapped for cash and we are not in a position to pay for professional help.

I do not expect anyone to solve this problem for me. What I am seeking is help on whether this is feasible, some general advice on layout and how I can set about gaing the necessary skills to create such a process.

Are there suitable online courses out there? I am willing to consider paying a reasonably small fee for such a course.

I appreciate that I am asking rather a lot but I will be happy to receive any helpful advice you can offer me.

 

[Trevor G]

Most of this can be done via a query.

First look to create new query in design view using the table where the data is stored. Next look to add the basic fields (you called them columns) into the query then save the query. Name it something like qryMasterList.

Next look to create an IIF statement to check if names exist in the columns and place them in 1 new column so you get a single column to check the list.

As far as search for an idividual name then perhaps use a search form so you can specify date range and then select a Volunteer.

Also if you want a complete list you can then create a Report that will either match the selected Volunteer or show everyone.

If you are willing to upload a sample of the database (or make one up as long as you know what you are looking at, then I can create what you need and show you how stage by stage).

 

[djchapple]

Thanks for the offer of help.

I have attached two files.

One is a very simple Access database with the table contsructed as in our volunteer database. The data protection act forbids me posting the actual database which contains much personal data.

The other file is a Word file showing the Report I would like to produce from the database tabe. The layoput of the report is not cast in stone but it does ahow the information i am trying to produce.

If you are able to produce something then I will be extremely relieved and very grateful.

David

 

[djchapple]

First look to create new query in design view using the table where the data is stored. Next look to add the basic fields (you called them columns) into the query then save the query. Name it something like qryMasterList.

.

I will look into the advice given in post above.

Sorry about the confusion in my mind over columns and fields. I grew up extensively using excel and I still think of the table of records as similar to an excel spread sheet.

I am trying to seriously improve my very limited knowledge of databases but I'm finding it hard to do it without help.

What I need is a really good course with expert tutorila help.

 

[Trevor G]

David will you only have 2 volunteers in the Field name and you are separating them with the & symbol?

You would need to create a query to extract everything before this symbol as an expression and then the other side.

If you can state if this is so....

If so then once this is sorted you can then run an append query so the names appear in an individual field listing 1 name after another as separate records, this would help for the report.

 

[djchapple]

David will you only have 2 volunteers in the Field name and you are separating them with the & symbol?

You would need to create a query to extract everything before this symbol as an expression and then the other side.

If you can state if this is so....

If so then once this is sorted you can then run an append query so the names appear in an individual field listing 1 name after another as separate records, this would help for the report.

Occasionally there will be three (one a trainee) and sometimes the record contains an "&" and sometimes an "and".

 

[Trevor G]

Take a look at the attached, there are several queries which will append the data into a table and then a macro which refreshes the table and then previews the report.

I am not at my desk for the next couple of days so it should give you a chance to look at it, then add some names and events etc and test it.