Here's what I have going on.... I am a member of a fire department in Connecticut, USA. I was asked to computerize all of the call forms (forms filled out everytime the dept has a call), as well as event logs (for training, parades, etc...) and membership records. This was the easy part. I designed the tables and forms for all of the above. I was then asked to do something I don't know if it can be done. The way the call form is laid out, you have your basic information such as type of call, times, location etc..., and at the bottom is a series of Yes/No columns for each member of the department. When you run the form you simply place a check in the checkbox if that member was at the call. The first thing they would like to do is have a query which will cross check the member records with their total response. In a simplified sense, it would work like this ...

Open query and enter members last name, it corolates the people with that last name by sorting the first name using the 'member records' table. It then goes out to the 'call sheet' table and checks each name against the true/false value of their response to each call. So if I searched for myself, it would go and find me, then goto the 'call sheet' table and check the column which starts with my last name (last name='Dube', column name='Dube, J') in all the rows for a true value. Each time it finds it, it would add '1' to my total calls. So if there was 54 calls at the time of the query, and I went to 40 of them, it would give a query response reflecting that. Is this possible?

Thanks in advance.

A good model for what you're attempting to do is the Access database template StudentsAndClasses (click on the New database icon, then on Databases).

The application uses (table) Classes to record classes (similar to recording your calls), (table) Students to record individual student data, and a linking table (StudentsAndClasses) to record student class attendance. With very little effort, this could be adapted to handle Calls and Staff members.

Please take a look--think you'll find it helpful. Pay particular attention to the manner in which the relations are setup.

Bob

You are creating tables that look like spreadsheets. Relational databases are most definitely not spreadsheets and their tables are organized differently. Your present approach requires that many database objects be changed to accomodate personel changes. A properly designed database will NOT require this type of maintenance. All that will be required is to add a row to add a new person or update a person's StatusCD to remove him from active duty. No objects such as forms/reports/queries, etc. need to be changed. Adding/deactivating people is a data change. Incidents and people have a many-to-many relationship.

Take a look at my many-to-many sample db. You can download it from here (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany)

A good model for what you're attempting to do is the Access database template StudentsAndClasses (click on the New database icon, then on Databases).

The application uses (table) Classes to record classes (similar to recording your calls), (table) Students to record individual student data, and a linking table (StudentsAndClasses) to record student class attendance. With very little effort, this could be adapted to handle Calls and Staff members.

Please take a look--think you'll find it helpful. Pay particular attention to the manner in which the relations are setup.

Bob

I am using Access 2003, and don't see that option. I tried a search for online template and was unable to find that one. Where else can I get it?

I couldn't find the template on the web site but I happen to have downloaded it sometime in the past. Send me a PM with your email address and I'll send it to you. It is too large to post.