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?

 

[Uncle Gizmo]

What name do you give to this collection?

ARRL, ARES, RACES, Skywarn, MARS (Army, Navy, Air Force), VE, EOC, and 92-2

 

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

 

[Uncle Gizmo]

What name do you give to this collection?

ARRL, ARES, RACES, Skywarn, MARS (Army, Navy, Air Force), VE, EOC, and 92-2

Do these items belong to one set, one collection? If so what would you term that set/collection? What name describes it?

 

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

 

[Uncle Gizmo]

Thank you. I think the group could be described as "Amateur Radio Clubs".

 

[Uncle Gizmo]

With regard to the "amateur radio club" fields, then the best way of going about it will be to create a set of new tables by following the instructions in this thread here:-

Excel in Access

 

[Uncle Gizmo]

The same goes for your recording the payment details. You already have a field called "Method". Provide this field with a lookup combo-box so that you can choose "cash" "visa" "check"... Please note the goal should be to have these entries in a lookup table so that you only record the look-up ID (a number) and not the text value like "cash" "visa" "check"

 

[Uncle Gizmo]

I think these values "MARS (Army, Navy, Air Force)" should have their own table. They don't fall into the same group of the "Amateur Radio Clubs" Fields. The "Amateur Radio Clubs" can be identified with a yes no boolean entry.

 

[Uncle Gizmo]

I'm not 100% clear on what you mean by this statement "Lastly I need to total the dues paid by All, Cash, and check" do you mean you want to Total everything as a single total? Or do you mean you want to Total each individual item cash, check, visa? In that case what do you mean by "All"?

 

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

 

[Uncle Gizmo]

count the number of Y's for the yes/no list boxes.

Assuming you have developed a table like the one in this picture:-

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1d.png

Then it is a simple process to create a query to count the values. There won't be any items listed where boolean is false so all you have to do is count the entries..

I have attached a sample database showing a query and a very crude report which extract the relevant information all done with wizards.

 

[Uncle Gizmo]

I think these values "MARS (Army, Navy, Air Force)" should have their own table. They don't fall into the same group of the "Amateur Radio Clubs" Fields. The "Amateur Radio Clubs" can be identified with a yes no boolean entry.

I've had second thoughts on this I think you should leave Mars where it currently is in the table. It might be sensible to copy it out into a separate table if you add other fields to record things, but for now it should be OK where it is.

 

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

 

[Uncle Gizmo]

Post your new db here.

 

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

 

[Uncle Gizmo]

I think you've sent me the wrong DB that one's is exactly the same as the first one you posted! I was expecting to see the changes I suggested in an earlier post...

 

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

 

[Uncle Gizmo]

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.

Well you've lost me.... I've done a YouTube video https://youtu.be/b8TGAI_tlV4 showing both databases side by side and you can see that they are exactly the same so either you have accidentally uploaded the same database or possibly you haven't carried out the changes I suggested.