By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Query and Report Questions

P: n/a
I am using MS Access 2003 and am in the process of creating a database
that contains contact information and attendance data for a group of
volunteers. I have created a table that contains all of the fields for
contact data and set the Full_Name as the primary index in that table.
I have also set up a table which contains Yes/No fields for each month
of the year in which to mark attendance. This table also includes the
Full_Name field for each volunteer.

I am trying to come up with a way to have one form or report or query
prompt me for the month for which I want to run an absentee or attendee
list and then use this answer to create a report on who was or who
wasn't there for a particular month. It would be nice to be able to
select more than one month as well. I have played with several
arguments in queries as well as user inputs but have not had any luck
to this point.
Any help would be greatly appreciated.

Thanks,

Bryan Braley

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hello Bryan,

I'm no Access expert, but since I posted a question this morning and
would like some answers, I thought I'd reciprocate first by trying to
help someone else.

....but I'm not sure you're going about your problem in the best way.
There are many resources available on the web for attendance
applications -- just search for "Access Attendance". In fact, I saw
from a cursory search that were a number specifically for religious
organizations (as I can only infer from your e-mail address). Maybe
take a look at those programs first, or any of the freely available
material and review them against your needs.

If you are set on the method you have chosen, then here's how I
attacked your problem using the most simplestic route I could think of:
1. Create a query to consolidate your contact data with their
attendance information.
2. Create a new field in the query called "MonthsAttended" which
concatenates the names of the months based on attendance (ie:
MonthsAttended: IIf([Jan],"Jan","") & IIf([Feb],"Feb","") &
IIf([Mar],"Mar",""))
3. Create another query "qryAttendFilter" for example, which includes
the MonthsAttended field and has a parameter "ShowMonth". Then create
another field "InMonth" which flags whether or not the parameter is in
the concatenated string (ie: InMonth:
(InStr([MonthsAttended],[ShowMonth])>0)).
4. Now you're ready to create your reports. Using "qryAttendFilter" as
a source, create one for attendees, and enable the filter and use the
expression "([InMonth]=True)" to filter the query for those volunteers
who attended the month described in the parameterized variable.
5. So now open up your report. It will ask you for the parameter
"ShowMonth", and you can enter the name of a single month. If the
month you entered can be found in the concatenated string
"MonthsAttended" then that volunteer will be shown.

The non-attendee report can be created in the same way but using
([InMonth]=False) instead.

Again, I stress that A) I am not an expert; B) you can probably design
your data tables in a better way; and C) there are more complicated
solutions to do what you're asking from your data...
Hope that helps.

ej

Nov 13 '05 #2

P: n/a
Hi Bryan

Where do I start.

OK Your table of volunteers should have a VolunteerID as a primary key. This
is an AutoNumber and needless to say unique. By using a name as the primary
key, the machine theoretically will take a long time to create and sort
indexes. What happens when you have 2 John Smiths (People, not beers) You
can then have as much other information strictly applying to the volunteer
as a person in that table. Consider address, phone, email, First Name, Last
name etc.

Create your second table called "Dates" with just 3 fields, DateID again an
AutoNumber, VolunteerID, this time a Long Integer and DateIn as a Date field
Set up a relationship between these to tables linking the VolunteerID
You then have form based on the Volunteer information. and a Subform based
on the date information.
You should then be able to extract anything you want such as how many times
did he attend in March etc

"braley" <br***@kidspastor.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
I am using MS Access 2003 and am in the process of creating a database
that contains contact information and attendance data for a group of
volunteers. I have created a table that contains all of the fields for
contact data and set the Full_Name as the primary index in that table.
I have also set up a table which contains Yes/No fields for each month
of the year in which to mark attendance. This table also includes the
Full_Name field for each volunteer.

I am trying to come up with a way to have one form or report or query
prompt me for the month for which I want to run an absentee or attendee
list and then use this answer to create a report on who was or who
wasn't there for a particular month. It would be nice to be able to
select more than one month as well. I have played with several
arguments in queries as well as user inputs but have not had any luck
to this point.
Any help would be greatly appreciated.

Thanks,

Bryan Braley

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.