Need a report based on the valve of list boxes

[Uncle Gizmo]

I note that the query "qryCountOfRosterClass" is not working properly. On investigation I discover that the join between the lookup table and the roster table has been moved... From the lookup table "lookupID" to the "lookup description" it needs moving back to the lookup ID... When you move it back you will get an error message "type mismatch in expression" this is because you have changed the data type of the field roster class in the roster table to text it should be a number "long integer" so to get this query working again change the join and a field in the roster table.

 

[Uncle Gizmo]

I note that the report "rptCountOfRosterClass" was not working. On investigation I discovered that the field count of roster ID needs to be renamed to "CountOfRosterClass" I don't know what has caused this error, I suspect tinkering again!

 

[Uncle Gizmo]

I can't see anything else wrong with it. Like I said earlier, make a backup first before you do any tinkering! So when you lose some functionality, or something doesn't work properly, you don't have to post a question... you can just go back to a previous version, change something else and try again. Tinkering about with the database is one of the best ways to learn; so carry on doing it, but do it in a way where you don't dig a hole for yourself.

 

[ka3pmw]

Ok, I replaced everything and have some of it working.

I may have gotten a slight reprieve on the deadline also.

In the interest of keeping this discussion shorter, maybe we should use email.

dave.alexander1947@gmail.com

 

[ka3pmw]

I note that the affiliate table, and one or possibly two of the other tables have some blank cells in them. You need to add indexes to prevent null values being added to the table.

I had the blank lines there incase someone goofed and put a value in that field that didn't belong there or dropped out of something.

I noticed that you can just delete what is written in the box by highlighting the data and hitting Delete.

 

[Uncle Gizmo]

I noticed that you can just delete what is written in the box by highlighting the data and hitting Delete.

You can prevent deletion like that by going to the forms properties and setting allow delete to "no".

You might want to consider doing that in most places in your database because it is not considered good policy to allow your database user to delete records willy-nilly as this can cause you all sorts of problems.

When offering the user the option to delete records, I actually don't allow them to delete the record. I put a flag in the record, basically a check box which when checked indicates the record has been deleted. This is a good tip and can save you a lot of heartache.

You can then write a routine for removing deleted records safely under the control of someone who knows what they are doing, the database administrator for instance.

 

[Uncle Gizmo]

Talking about deleting records reminded me of relationships. I'm pleased to note you haven't got any relationships setup yet, to my mind they are a hindrance to the initial stages of database development. Once you are happy with the structure of your database then you might want to consider adding relationships. I suggest you read up on them first, and then add them to a temporary copy of your database to see what happens, before adding them to your main database.

 

[Uncle Gizmo]

In the interest of keeping this discussion shorter, maybe we should use email.

Discussions are public so that others can learn from them, direct email would prevent this.

Also a public discussion often leads to input from others which is both beneficial to you in that more knowledge is applied to the problem, and me, in that I often learn a better way to do something. For instance, a recent thread convinced me that "calculated fields" are something I should use more often.

 

[ka3pmw]

You can prevent deletion like that by going to the forms properties and setting allow delete to "no".

You might want to consider doing that in most places in your database because it is not considered good policy to allow your database user to delete records willy-nilly as this can cause you all sorts of problems.

When offering the user the option to delete records, I actually don't allow them to delete the record. I put a flag in the record, basically a check box which when checked indicates the record has been deleted. This is a good tip and can save you a lot of heartache.

You can then write a routine for removing deleted records safely under the control of someone who knows what they are doing, the database administrator for instance.

If the user goofs or the member ops out of a group, how do I record that without creating a blank record on a form? just have the report ignore blank fields?

I probably won't be answering much today. We are baking. I have 1 pie in the oven and am about to start making cookies.

 

[ka3pmw]

Talking about deleting records reminded me of relationships. I'm pleased to note you haven't got any relationships setup yet, to my mind they are a hindrance to the initial stages of database development. Once you are happy with the structure of your database then you might want to consider adding relationships. I suggest you read up on them first, and then add them to a temporary copy of your database to see what happens, before adding them to your main database.

I don't anticipate having to add any relationships at this point. Every thing is working with the exceptions which I will address later today.

They are:
1. In MARS, T need to select only one of the three. That selection could be changed. As it stands, I tried a selection like I originally had but it only gave me a choice of two of the three selections.

2. Affiliates will allow for more records to be added, and allow you to add the same affiliation more than once. Neither of these are a good thing.

3. The Class report is only showing the class number and total of the class, not the class title.

The Dues work perfectly, Thanks!

 

[Uncle Gizmo]

1. In MARS, T need to select only one of the three. That selection could be changed. As it stands, I tried a selection like I originally had but it only gave me a choice of two of the three selections.

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

 

[Uncle Gizmo]

2. Affiliates will allow for more records to be added...

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

 

[Uncle Gizmo]

allow you to add the same affiliation more than once.

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

 

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

 

[Uncle Gizmo]

If the user goofs or the member ops out of a group, how do I record that without creating a blank record on a form? just have the report ignore blank fields?

Good point! I'm not sure... This would be a good question to post to the forum, I'd be interested in others thoughts in it...

 

[Uncle Gizmo]

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

Ah I see. In that case, the index mentioned before would sort that out, because it would only allow you to enter one of each, therefore, automatically enforcing the no duplicates, and a maximum number of records. Also note, if another item is added to the list, then it would automatically accommodate 8, 9, 50 a hundred.....

 

[Uncle Gizmo]

It will only give me Air Force and Navy. I can't figure out why.

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

 

[Uncle Gizmo]

Like what and should I associate it with a field?

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