Need a report based on the valve of list boxes

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

 

[Uncle Gizmo]

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

You will need to create the index.

 

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

 

[Uncle Gizmo]

Start here:-

Computer Training : How to Create an Index in Microsoft Access to Sort Records Faster

 

[ka3pmw]

Start here:-


https://youtu.be/uYtJ_0GSeQc

Ok, I created AffiliateIndex in the Affiliate table.

Next step?

 

[Uncle Gizmo]

Ok, I created AffiliateIndex in the Affiliate table.

Next step?

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

 

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

 

[Uncle Gizmo]

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.

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

 

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

 

[Uncle Gizmo]

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.

The Query "qryCountOfRosterClass" should look like this

The SQL Version is:- (Text versuion of the above)

SELECT tlkpInfo.tlkpInfoDesc, Count(tblRoster.RosterClass) AS CountOfRosterClass
FROM tlkpInfo INNER JOIN tblRoster ON tlkpInfo.tlkpInfoID = tblRoster.RosterClass
GROUP BY tlkpInfo.tlkpInfoDesc;

Please post an image and/or the SQL of yours...

 

[ka3pmw]

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

 

[Uncle Gizmo]

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.

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"

 

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

 

[Uncle Gizmo]

I have created a YouTube video HERE:- MS Access - Prevent Duplicates showing how to create an index to prevent duplicate values being added in the affiliates and MARS tables.

 

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