Showing certain information on a report.

[dodie]

I work for a Driller training company and I have been tasked with making a database to make certain tasks easier. I have not had any training in Microsoft Access, the last time I looked at Access was 13 yrs ago.

I have a table called Students which fills in a form called Students. This table is huge, it has nearly 100 columns, and I’m sure there is a better way of handling this information. Like I said, I’m a beginner.

This form contains the Student details on one tab (Company they are employed with, student number, name, DOB, mobile number, email, student photo and notes); on the other tab it contains the courses they are attending, the date of attendance, the date of cancellation, the course provider and venue.

These courses we book are with different providers for example St John’s do Senior First Aid Courses and Senior First Aid Refresher Courses, but Eureka 4WD and DriveSafe both do 4WD Recovery on Gravel. Any student can have any combination of courses and any combination of venues.
This form is supposed to serve the purpose that when we have clients contacting us to book Students in to different courses; we fill in the dates booked, select the provider and venue.
I want this form to be able to filter out the different courses selected according to what date they are booked on and create a Training Schedule for each individual student.

I have tried lots of different approaches. So far I have checkboxes to indicate which courses have been booked, but can’t seem to find a strategy for these to be shown only on the report. I have tried creating queries to filter the selected courses as True, using Macros to SetProperty in the OnFormat event to make certain controls visible. But so far all of my trial and error ends with nothing but error.

I know there’s only so much you can tell me, but I really am at a loss.
Any help is great, thankyou.

 

[Galaxiom]

Start by separating the course attendance records.

You should have:
CourseType table with CourseID (PK) and CourseName
CourseInstance table with CourseInstanceID (PK), CourseID (FK), Venue, InstanceDate

Attendance table with CourseInstance (FK), StudentID (FK)
These records show which course was attended and can easily be grouped by StudentID.

This is a classic Many to Many relationship.

 

[dodie]

Hi Galaxiom,

Thankyou for replying. I have started to make these tables but am unsure about how you make Foreign Key columns... Is this done through lookup columns?

 

[Galaxiom]

A foreign key is simply the primary key from another table.

So in the CourseInstance table the CourseID FK is the CourseID PK from the CourseType table. This connects the Instance to the Coursetype.

Make the FK a Lookup in the Instance table while you are designing the forms so that Access will automatically build your form controls to display the name of the course but store the ID. Remove the lookups when you split the database.

Make a main form from the CourseType table. Create a subform on this form using the CourseInstance table. The Master and Child Link Fields will be CourseID.

When the Course is loaded, all the instances of that course will be in the subform. When you add a new record to the subform the key will be automatically populated from this link.

Include some way to limit the courses shown in the subform. This could be a Closed field in the CourseInstance table. On the main form include a checkbox to switch between showing all and showing just the open courses. Otherwise there would be no way to retreive a course that was closed in error.

Another way is a date range in a textbox or combo on the main form. Include this control in the record source query of the subform. For versatility I would include both the Closed and the Date Range.

Hope this makes sense.

 

[dodie]

Hi Galaxiom,

Thank you for your lengthy reply. It's greatly appreciated.
I understand Primary Keys and Foreign Keys now. The main problem is having it run like I want it to and what best suits the company. The main purpose for this database is so when a company sends me a booking, I can just enter information quickly into this database and have it produce a Training Schedule. And where there is a cancellation, same concept applies. Rebook course, reprint schedule. I have attached a screenshot of the Student interface I have created. The tick boxes were created to indicate when a course has been booked, the combo boxes are there to select the nominated Venue. I was hoping there was an easy way of feeding this information into a report. At the moment all these controls are unbound because I began restructuring my database to how you suggested. I would also require these courses to be ordered by date also.