Need a report based on the valve of list boxes

K

ka3pmw

Registered User.
Local time
Today, 09:46
Joined
Dec 11, 2015
Messages
87
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?
 

Attachments

What name do you give to this collection?

ARRL, ARES, RACES, Skywarn, MARS (Army, Navy, Air Force), VE, EOC, and 92-2
 
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 said:
What name do you give to this collection?

ARRL, ARES, RACES, Skywarn, MARS (Army, Navy, Air Force), VE, EOC, and 92-2
Click to expand...
Do these items belong to one set, one collection? If so what would you term that set/collection? What name describes it?
 
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.
 
Thank you. I think the group could be described as "Amateur Radio Clubs".
 
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
 
Last edited:
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"
 
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'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"?
 
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.
 
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 said:
count the number of Y's for the yes/no list boxes.
Click to expand...

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.
 

Attachments

Uncle Gizmo said:
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.
Click to expand...

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

Attachments

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...
 
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 said:
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.
Click to expand...


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.
 
You must log in or register to reply here.

Similar threads

D
How to view a monthly reports on the 1st of every month
Replies
5
Views
108
Pat Hartman
P
Safaa
How to display a specific number of records in a subreport linked to the original report with the patient code
2
Replies
20
Views
424
Pat Hartman
P
D
Report field base on another
Replies
11
Views
613
Design by Sue
D
M
How to show the value of criteria on query to report
Replies
5
Views
390
xavier.batlle
X
G
Solved Data from form not showing on Statement of Account report
2
Replies
21
Views
1,122
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom