Need a report based on the valve of list boxes

[ka3pmw]

I suggested that you put this back in the roster table, like you had it originally.

I tried that and only got 2 of the three. I used " ";"Air Force";"Army";"Navy" and it will only give me Air Force and Navy. I can't figure out why. I should also say that it is probable that most people will not be members of this affiliation, That is why I need the blank. I suppose I could replace it with a word like None.

 

[ka3pmw]

Please explain when the limit to the number of added records is reached, how is this decided?

The max would be 7, none of which should appear more than once. ARRL, ARES, RACES, Skywarn, VE, EOC, and 92-2.

 

[ka3pmw]

You need to add an index to the affiliates table to correct this problem. I think I also mentioned that before.

Like what and should I associate it with a field?

 

[ka3pmw]

Where is the combo box getting the values Air Force and Navy from?

tblRoster the tblMars line I added and the frmRoster has them listed too.

 

[ka3pmw]

You need to add the index in the affiliates table.

This isn't a straightforward index because you have two fields, you have the field which stores the value which relates to the person in the roster table, and then a field for the affiliation.

RosterID > 8 ~ AffiliateRef 1
RosterID > 8 ~ AffiliateRef 3
RosterID > 8 ~ AffiliateRef 4
RosterID > 8 ~ AffiliateRef 7

You want the combination of 8~1, 8~3, 8~4, 8~7 to be unique.

With regard to the above sequence you need it to refuse another entry of:- 8~3...

In the Affiliate table select index's, select both fields:- RosterID and AffiliateRef, and select "Unique".

Are you saying to add a new index or one that already exists?

 

[ka3pmw]

Where is the combo box getting the values Air Force and Navy from?

Never mind this one. My fault. I had the control Wizard on when I designed it. Mt Bad!
I set 4 fields, No, Air Force, Army, and Navy. That way there are no blank fields.

 

[ka3pmw]

You need to add the index in the affiliates table.

This isn't a straightforward index because you have two fields, you have the field which stores the value which relates to the person in the roster table, and then a field for the affiliation.

RosterID > 8 ~ AffiliateRef 1
RosterID > 8 ~ AffiliateRef 3
RosterID > 8 ~ AffiliateRef 4
RosterID > 8 ~ AffiliateRef 7

You want the combination of 8~1, 8~3, 8~4, 8~7 to be unique.

With regard to the above sequence you need it to refuse another entry of:- 8~3...

In the Affiliate table select index's, select both fields:- RosterID and AffiliateRef, and select "Unique".

Ok, I'm missing something here. I created a field in the affiliate table called AffiliateIndex. Now I need to create a relationship to the roster table?
I suggest we take this one a step at a time.

 

[ka3pmw]

Start here:-


https://youtu.be/uYtJ_0GSeQc

Ok, I created AffiliateIndex in the Affiliate table.

Next step?

 

[ka3pmw]

Check to see if the Affiliate data entry works like you want it to.

Ok, I did and it sort of works. There are three problems left. The one that is keeping it from being usable is the License Class Report so let's look at it first.

When I do the query or report, I am getting a number instead of the name of the class and the total count for that class.
The total is what we want, but we need the name of the class, not the number. Ex. 13 is HF Tech. We need it to say "HF Tech" not 13.

 

[ka3pmw]

do you mean:- the report - "rptCountOfRosterClass" ?

Both the report and the query. I don't care so much what is shown on the query, unless I have to have the info there for the report, but the report is what is important.

The new license classes are Tech, General, and Extra (The FCC combined the old ones but some people still have the old ones.)

The club gives classes to get the Tech license but not General and extra. This would show the need to offer classes for General and Extra Licenses.

 

[ka3pmw]

Both the report and the query. I don't care so much what is shown on the query, unless I have to have the info there for the report, but the report is what is important.

The new license classes are Tech, General, and Extra (The FCC combined the old ones but some people still have the old ones.)

The club gives classes to get the Tech license but not General and extra. This would show the need to offer classes for General and Extra Licenses.

Never mind this. I got it working!!

Now for the next problem. I need to limit the number of selections in the MARS and Affiliation boxes and prevent duplications in those boxes.

There are three possible entries for MARS, and seven for Affiliations. I need the choice list to allow only these and not allow the user to go beyond that number.

I was getting confused with the answers to all the questions before.

 

[ka3pmw]

I have that working. what I need now is the answer to my next question, the list length.

 

[ka3pmw]

We've already covered this! You need to create an Index. Please see the video I linked to earlier. The video demonstrates adding, and indexing "One field". However in your case you need to add two fields... AND set the index to unique. So basically it's the same as in the video. BUT--- add two Fields instead of one. Give the index a name, and then select a field, then below that field, select next field. You can select a maximum of 10. In your case your table(s) only have two Fields in them, and you need to select both of the fields. Once you've done that make sure you select... (I think it's a checkbox) check the checkbox "Unique" by the way it won't work if the table already contains duplicate values or if some of the fields in the tables are empty. So before trying to create the index check the tables for duplicate values that means where the combination of the left and right field is repeated. You need to do this to both tables the Mars table "tblMARS" and the affiliate table "tblAffiliate"

Ok, that is what was confusing me. I had too many questions going at onse. Will do but not sure how soon I will get to it. May be a couple of days.

 

[ka3pmw]

OK, I have everything done except the MARS and Affiliations problems. You had said something about using something like:

RosterID > 8 ~ AffiliateRef 1
RosterID > 8 ~ AffiliateRef 3
RosterID > 8 ~ AffiliateRef 4
RosterID > 8 ~ AffiliateRef 7

I don't follow what you are saying. I have the indexes created but I don't understand where this goes. I have included the latest version. As for being able to delete/change one I can just add a NO selection, select it, then don't display it on the reports.

 

[ka3pmw]

Perfect, works like a champ. Only one problem. You can still select more than one value for MARS. A person can only belong to one MARS branch at a time.

 

[ka3pmw]

I decided to go back to the old way of entering the MARS from a listbox to eliminate being able to select multiple values. The only problem I am having with that is that when I select it for one person, it copie3 that value to other people. Not Good!

Any Ideas? It used to work.:banghead:
I did not use the control wizard to create it.

 

[ka3pmw]

Yes... You mentioned this before and I suggested you go back to having MARS in the Roster table.

I tried that and I can not get it to behave. Once I change a value, it keeps that value all the way through till I change it again. I am using No for the first value and default to do as you said and avoid blank records.

 

[ka3pmw]

That worked. THANK YOU VERRY MUCH for all of your help and patience. It's not fun getting old!