Need a report based on the valve of list boxes

Looking back on your previous posts in this forum I note this http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=61219&d=1450048721 suggestion from another user that's exactly the right way to go about sorting out your payments why haven't you implemented that?

It was AccessBlaster:- http://www.access-programmers.co.uk/forums/showpost.php?p=1465796&postcount=11

Uncle Gizmo said:

Looking back on your previous posts in this forum I note this http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=61219&d=1450048721 suggestion from another user that's exactly the right way to go about sorting out your payments why haven't you implemented that?

It was AccessBlaster:- http://www.access-programmers.co.uk/forums/showpost.php?p=1465796&postcount=11

Click to expand...

I see.. Well you have sort of got the subform in there but you've got it in form you not datasheet view.

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.

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.

I directed you to some information that will help you with your problem in this thread comment here:- http://www.access-programmers.co.uk/forums/showpost.php?p=1466114&postcount=7

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.

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

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

Sorry

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.

I don't know what to say to you! You are now going backwards! Your mars thing was nearly right, one field and a selection of choices with a combobox I believe. Now you have a field with a check box for each possible selection. You obviously haven't took any notice of this thread I pointed you to earlier. I'm not surprised mind you I see this all the time, good advice ignored. When I did the video I wrote the following:-

Desperation
What I am saying now sounds like a rant, but actually it's more like desperation/frustration. I realise I am speaking to you however don't take it the wrong way I'm not actually speaking to you….. I'm speaking to everyone that ignores good advice.

Change your Structure
I can't think how many times I have explained to people how they should build a database. That post I directed you to is 7 years old. It didn't appear out of nowhere, a lot of work, time and effort went into creating it. Why? Why would I give you this advice? Because I know where you are with your database construction. I have been there; that's why I know you should change your structure.

Trainwreck
I tried very hard to build a database in the same model you are choosing to use. The model you have chosen, and the model I initially chose are the same. We are both the same... except I'm a few years a long, a few more painful experiences ahead of you! I have been through the painful process of trying to persevere with that database model. If you continue with it, it's not going to be nice! It's going to be a trainwreck. One day you will be sat crying in the corner thinking I wish I'd listened to Uncle Gizmo! And you won't be alone; and I have been there crying in that corner, and I would like to help you avoid it.

Easy at First
The model you are using is the obvious model it looks so correct, it looks so familiar, it looks easy, and initially it is easy. It's easy because you can see exactly what's happening. Your initial queries for extracting information are easy as well, the initial reports are easy to build. But then you suddenly realise there's something missing? You need to add something here; change something there. Now the nightmare begins!

Problems Will Become Difficult
I'm not saying you shouldn't carry on in the vein you are, that is entirely your decision. I'm not saying you won't get any help, indeed there are a lot of people out there that are using the same structure you are, and they are slightly more advanced than you in their understanding of it. They have solved some of the problems and they can show you how to solve the problems. But they will be doing you a disservice because eventually the problems will become so difficult they are very hard and time-consuming to solve.

Use the Alien Model
I'm offering you what looks like a very bad deal. I'm offering you a lot of work right at the beginning of your database. I'm showing you a model which is alien to you, a model you don't understand, a model that is difficult to understand because it just doesn't look like you think it should.

Convert to the Dark Side!
You may choose to carry on in the direction you are going, and as I said that is your right, your decision. There's nothing wrong with that decision as long as you know that you are taking the wrong road. You may get some help from others, misguided people who are further along the road you are following. People who if they are honest are starting to hit the odd pothole and wonder about a different way of doing things. Eventually you will come to understand what I am saying and you will convert to the dark side!

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.

This is how it should work....

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

ka3pmw said:

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?

Click to expand...

I think the Attached db Answers all the above questions....

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 said:

I need to have the boxes laid out horizontally instead of the vertical selections.

Is that possible and how do I go about it?

Click to expand...

Well; I think it would be possible but I'm afraid I haven't got the time or inclination to implement it. Why not go with the interface I have already provided?

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 said:

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

Click to expand...

Yes, I know, you will need to add an Index to stop duplicate values being added.

OK, Will do. Thanks.

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.