Need a report based on the valve of list boxes

[ka3pmw]

I need to make a few fields all caps. the > in the table isn't working. Any ideas?

 

[ka3pmw]

I fixed the caps issue by putting a > in the format under the format tab in the layout. I'm just not thinking. Too much to do and too little time.

 

[ka3pmw]

Uncle Gizmo,
I found the following reports are not working:
rptCountOfRosterClass
rptMARSMembers

The following forms are not working:
sfmCashUp
sfmCashUpTotal
sfmPaid

Any Ideas?

Other than that I think it is ready to go. I put the Switch Board in and have the layouts the way we need them.

It still needs to have the MARS either/or problem fixed. I didn't figure out where you wanted me to put that index to fix that and what it gets related to.

 

[ka3pmw]

MARS either/or problem
I will need an explanation of this problem.

OK, The government will not permit a ham to belong to more than one MARS group at a time. Therefore if you are a MARS operator you either have to be Air Force, Army, or Navy. You do not have to be in the military now or ever. I think they eliminated Navy, but just incase there are some Navy operators in the club it's best to keep the field.

 

[ka3pmw]

I think there's a table called tblMars set the index in there.

I think there's a field rosterID and marsdesc, set the index to unique on these two fields.

The fields are:
MARSRosterID, MarsMARS, and Marsindex.

MARSRosterID is set to Number with Required set to Yes and Indexed set to Yes duplicates OK.

MarsMARS is set to Number with Required set to No and Indexed set to No.

MarsIndex is set to Number with Required set to No and Indexed set to Yes Duplicates OK.

 

[ka3pmw]

All the reports and forms work fine in my version.

Windows 7 Access 2007:
For rptCountOfRosterClass I get a window that says CountOfRosterID , Gives me a blank to type something, and an OK or Cancel Option.

For rptMARSMembers I get no data when there should be something listed.

For sfmCashUp I get Forms!frmPaid!cboYear a box to put something in and OK or Cancel options.

For sfmCashUpTotal I get Forms!frmPaid!cboYear a box to put something in and OK or Cancel options.

For sfmPaid I get Forms!frmPaid!cboYear a box to put something in and OK or Cancel options.

 

[ka3pmw]

In the query qryashUP the design view has SELECT tlkpInfo.tlkpInfoDesc, Sum(qryPaid.DuesAmount) AS SumOfDuesAmount
FROM tlkpInfo INNER JOIN qryPaid ON tlkpInfo.tlkpInfoID=qryPaid.DuesMethod
GROUP BY tlkpInfo.tlkpInfoDesc;

In the qryCashUpTotal the design view has SELECT Sum(qryPaid.DuesAmount) AS SumOfDuesAmount
FROM tlkpInfo INNER JOIN qryPaid ON tlkpInfo.tlkpInfoID=qryPaid.DuesMethod;

This looks like an SQL statement and we are not using SQL.

 

[ka3pmw]

Set the forms data entry property to no.

It is already and still allows you to select all the possible items and keep on adding dupes.

 

[ka3pmw]

I meant the subform.

That option is not available.

 

[ka3pmw]

Have you added Marsindex?

Yes, I made that correction.

The join is from the tblRoster, RosterID field to tblMARS,MarsMARS field.

 

[ka3pmw]

The example db you provided has two records.

However I gather from your comments that this is an existing concern.

That leads me to ask, how are you going to add the records from your existing system to the new system!

First of all, I probably goofed. There are 3 choices. Air force, Army, and Navy. They should have been in the example but one may have gotten away.

I am trying to add the MARS list box to the tblRoster as you suggested.
When I add a list box to the frmRoster with the following, I am getting two options in the box on the form, Air Force and Navy.

Control Source RosterMARS
Row Source " ";"Air Force";"Army";"Navy"
Row Type Value List
Bound Column 1
Allow Value List Edits Yes

This is the same as I did for the list boxes on my old form. I can't figure this one out!

 

[ka3pmw]

First I made a copy of the old database. Then I deleted the columns that I did not want. I then renamed the columns I wanted to the same as the new form.
Then I did a copy and paste from the old form to the new form. I got no errors.

 

[ka3pmw]

I suggest you do some experiments and see if you can copy the data into the new database. Not much point in continuing until this issue is resolved.

The only thing I copied was the data from the old Roster table. I did not copy the structures or anything else. I renamed the old fields so the copy and paste would accept them. I pasted that into the tblRoster table.

I'll do that again to be sure.

 

[ka3pmw]

The only thing I copied was the data from the old Roster table. I did not copy the structures or anything else. I renamed the old fields so the copy and paste would accept them. I pasted that into the tblRoster table.

I'll do that again to be sure.

I did it and it worked. Do you want me to send a sample to you?

 

[ka3pmw]

Well, if it's essentially the working copy I posted earlier, then I'd like to see why its not working now.

I also fixed the Affiliate members report, the Affiliate Summery, and added totals to report by Call sign and Last Name.

Check out the Switch Board too.

 

[ka3pmw]

In the Affiliate Query we only want the names of the people that are affiliated with that category as well as the summary. This is to track who is trained for certain positions.

Will do on the "tlkpInfo" We don't do PayPal and credit cards, that's why I took those out.

What I sent you was a copy of the table with 142 records removed for the privacy of the members. I neglected to run the query again after I removed them.

I will put the years back.

I think I have answered everything now. I have a public service net in about 20 min. It takes about an hour, then I can make the changes. I will be busy tomorrow helping my daughter do the baking for Christmas so I'll have to finish tonight.

Thanks and Merry Christmas.

 

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

 

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