Need a report based on the valve of list boxes

[ka3pmw]

Access 2007 windows 7

I have the attached database with sample data.

I need to create some reports based on the number of times an item in the list box comes up.

The idea is that the report field for Class will show the number of Novice, Tech, HF Tech, General, Advanced, and Extra members there are, also the number of blanks in that list box.

Likewise the ARRL, ARES, RACES, and the other Y/N fields.

I also need to be able to create reports showing who has paid, who belongs to ARRL, ARES, RACES, Skywarn, MARS (Army, Navy, Air Force), VE, EOC, and 92-2.

Lastly I need to total the dues paid by All, Cash, and check.

What is the best way to go about this?

 

[ka3pmw]

This collection is located in the Roster Table. Each is its own separate list box. The possible selections are:

ARRL " ";Y;N
ARES " ";Y;N
RACES " ";Y;N
Skywarn " ";Y;N
MARS " ";Army;Navy;Air Force
VE " ";Y;N
EOC " ";Y;N
92-2 " ";Y;N

I need to total the values for all the Y's and the Army, Navy, and Air Force.

Paid amount, Cash, and Check are in the Dues table.

Did the sample database attach to my post OK?

 

[ka3pmw]

They are each individual fields in the Roster table. Each record in that table has these fields. They represent membership in a branch of Ham Radio. ARRL = American Radio Relay League. ARES = Amateur Radio Emergency Services. RACES = Radio Amateur Communications Emergency Service. Skywarn = Wearter Emergency Observer. MARS = Military Amateur Radio Service. VE = Volunteer Examiner. EOC = Emergency Operations Center. 92-2 = Mobile Command Truck Certification. Esch member can belong to any, all, or none of these.
If you got my example that has two records in it.

 

[ka3pmw]

There should be 3 different totals. One will be the total of everything, one will be the total paid by check, and the last will be the total paid in cash.

Those totals should be by year.

ex:

Year Pd. Cash Pd. Check Total Audit

The total should be a total of all payments. the audit is the total of the checks and cash. If everything balances the total and the audit will be the same number.

 

[ka3pmw]

I have the queries and reports finished for the License Class and MARS headings. All that's left are the financial and to count the number of Y's for the yes/no list boxes.

 

[ka3pmw]

I left MARS where it was. Problem is on the summary report where the totals are shown, I can't get them in.
My summary shows the total number of records for the member roster, ARRL, ARES, RACES, Skywarn, VE, EOC, and 92-2. I have these working perfectly.

I need to add the number of records in each of the three MARS reports, and the number of records in each of the six Class reports.

I have queries, forms, and reports for all of those fields, but I can't get them into the summary.

I know that I can use CountOf to do it but how and where is what is stopping me.

When I get that fixed I'll work on the final part, the accounting.

 

[ka3pmw]

Here it is. The one that I am working on is the Summary.

The Summary report hasn't had the labels fixed yet due to me being too tired to do it.

We did CQ Santa today for one of the Children's Rehab locations so I had to get up early.

We have to do it again tomorrow and Friday. I guess I'll be up at the North Pole with Santa tomorrow for about 70 kids.

 

[ka3pmw]

Unless my mind has gone completely, that was before I added the reports for all the fields. What I am working on is the Summary that counts the various fields and will eventually have the financial info in it.

 

[ka3pmw]

Apparently something happened. There should have been a whole lot more forms, Queries and reports in there than the video showed.

I made a fresh copy of the file and a fresh zip file called New Club Roster 1, and have attached it.

I would like to get the summary working before I start on the financial.

I haven't decided just how I want it to look yet. As you can see, I decided to do some detailed reports on the various fields to give us not only a final count of members that are participants in various phases, but info on who they are. This could be more important in an emergency if we need people that are qualified for certain things than just a plain summery.

Both are not only useful, but important.

My original idea was to just do a CountOf on each of the three MARS categories and each of the six license classes and put that in the Summery, but I can't get that to work.

I'll try to make the changes to the Dues form if I can stay awake long enough tonight.

 

[ka3pmw]

I tried the checkboxes and got them working on the form but I could not get them to total for the summary. I realized that I need something like checkboxes or Y/N field for each one, not a drop down. This is because a person can be an operator for two or all three.

I still can't remember how I got the other fields to work.

 

[ka3pmw]

Thanks to your suggestion I was able to get the MARS totals using the SUM in the total row of the query then multiply the answer by -1 in the Summary form. The thing I was missing was sum and I found it when I realized that I used Count to get the other values that were working. Daaaah!

I have decided to put the various license classes in a separate sub form and the financial in a 2nd sub form. I'll tackle that tomorrow.

 

[ka3pmw]

I have discovered a problem. It seems that the checkbox on NavyMARS has something in it on the Look Up form. It appears to be hindering the action of that field. Either checked or unchecked it is updating nothing. The other two boxes work fine.

To me it appears to be a small box in the checkbox with an X in it. I have checked everywhere that it is associated with and can find no reason for it.

Any ideas? .

 

[ka3pmw]

Never Mind, I found it. It was a missing control source for the checkbox.

Sorry

 

[ka3pmw]

I goofed. The MARS operators can only belong to one MARS branch. Therefore I am back to making only one selection for each operator. The license classes are the same situations.

Having said that, I have created a new table for mars. I am drawing a blank as to how to connect the two and get the sum of the fields correct.

I am concerned with only the MARS at this time. I will do whatever I do for it to the license class field later.

I have attached the most recent database sample.

Please point me in the right direction.

 

[ka3pmw]

Ok, I have redesigned the Database. I have put affiliates, dues, license, and MARS in separate tables.

I have attached my latest attempt.

I took out some of the information in the Roster table.

Now the lookup form is allowing me to select multiple items in the MARS and License sub forms.

The initial tables for those two were set as Yes/No data types then changed to option buttons to prevent multiple selections. The buttons were put in their respective option groups.

Now question, is access allowing multiple selections because the look up value in the table is set to check box, and if so, what do I need to change to correct this?

I don't mean to antagonize you, but this thing has to be in service on the first of the year and I don't have much time to put into it.

The only thing that I have working in this version is the Lookup and that is what has the problem.

 

[ka3pmw]

Thanks, I'll do a redesign. I'll probably have the sub forms in separate windows to make it easier for her.

 

[ka3pmw]

On the first demo, I need to have the boxes laid out horizontally instead of the vertical selections.

I need them to be in a line instead of a selection list. Affiliates can be one or all on that list. I'd like to check the ones that apply to the operator. The same for MARS.

Is that possible and how do I go about it?

I have not had a chance to look at your last file yet.

Thank You very much

 

[ka3pmw]

That would work for you or me but the gal that is going to be running this thing would be totally lost. I think I can work through the Affiliations with her, but the MARS choices are still letting you make multiple entries and you can only belong to one of the MARS groups (Air Force, Army, or Navy).

 

[ka3pmw]

OK, Will do. Thanks.

 

[ka3pmw]

Two Questions:

1. How do I prevent the * line from showing in the affiliate and MARS sub forms?

2. Where do I put the index for the MARS selection?

The class selection would work for MARS but I can't figure out how you did that one. I guess those strokes I had did more damage than I thought. I don't think I'll be doing any more of these.