Print Report from Multiple Combo Boxes and tables

[JAES]

I’m having problems printing a report from a set of combo boxes. I don’t know if it is even possible with the current table structure.

Table tActivities have columns that include StudentID, FallSport, WinterSport, SpringSport, ConcussionForm, ActivityForm (this particular report shows the status of the forms for each player of an individual sport)

Table tSportsSeasons has a column Season that has Fall, Winter and Spring as rows

Table tAllSportsList has columns that include Sports and Season (and coach information, etc)

I have combo boxes getting the Season (cboSeason) from tSportsSeasons and Sport (cboSport) from tAllSportsLIst based on the season selected in cboSeason.

If cboSeason says “Fall” I would like to look up the value of FallSport in tActivities and select all rows that match cboSport (football, cross country, etc).

I’ve never asked for help without posting what I’ve done so far but I haven’t been able to come up with anything that remotely works. Any help is always appreciated.

 

[dhookom]

Is it too late to correct your table structure? tActivities should really have a field named "Season" and a field named "Activity". This is basic normalization. You are storing data (season names) in your field names. Every activity for every student for every sport should create a new row/record.

I would even go further to create another table called tForms with fields:
StudentID
FormID
SubmitDate
Comments

Duane
Hook'D on Access
MS Access MVP

 

[JAES]

Not too late at all, thanks. So tActivity would hold only the sports they are participating in and tForms would hold the information on the status of the forms, etc. The forms only have to be completed once a year so tForms would only have one row per athlete? Can I still use the combo boxes and use Insert or Update to add to the tables?

 

[JAES]

I tried to send you a copy of the current sub form that uses the tActivities table. I'm not sure how to attach it to this message.

 

[dhookom]

First tForms would have one record per athlete per form. Your structures should allow for any number of forms. Adding a new form should never require you to add a new field. The same for activities. Adding a new season should simply be adding a record to a table of seasons.

You would definitely want to use combo boxes to select athletes, seasons, activities, and forms.


Duane
Hook'D on Access
MS Access MVP

 

[JAES]

Thanks dhookom, I'm changing over the structure as you suggested. On a subform I show the status of the forms, which I have, and the sport they are playing under Fall, Winter and Spring label headings. I would like this to be where the assistants enter the sports they are playing. I'm not sure how to just show the sport they are playing in the fall under the fall label, .

 

[dhookom]

I don't understand your table structure. You mention "under" labels.

Do you have a table of all unique forms with renewal interval (months)? Do you have tables of activities by season?

Duane
Hook'D on Access
MS Access MVP

 

[JAES]

The forms only need to be done annually. If it is done during the fall season then it carries over into winter and spring. By making the tForms table like you suggested that took care of trying to carry it forward. It's either done or it isn't.

I have a table, tAllSportsList that has Sports (list of the individual sport), SportSeason (Fall, Winter or Spring) and coach information.

Table tActivities has StudentID and Sport. A student could have 3 rows of information if he or she is involved in fall, winter and spring sports. It also has a column Season that has the corresponding season but I don't think it is needed here.

The subform sfActivities used to have combo boxes where the medical assistant would enter or edit the sport in 3 combo boxes, FallSport, WinterSport and SpringSport. Now that I eliminated those columns from the activities table I'm not sure how to present the information for them to enter or edit.

Thanks

 

[dhookom]

Do you have a table that store which sports are in which seasons? This would be a junction table.

Your subform would be continuous and would have a single combo box that would store the primary key from the Sport or SeasonSport table.

Duane
Hook'D on Access
MS Access MVP

 

[JAES]

Thanks, I actually haven't used junction forms before so I have some reading to do before I can finish this part of the database. I've been working on other areas and will get back to this next week. Thank you again for all your help.