New Database Design Help

[mattyn]

Hi All

This seems a good place to start. I have a fair bit of historical IT knowledge - HND level but it was about 13 years ago. I am pretty good at spread sheets, and was reasonably ok with Access - but that was Access 2K.
Now I am in a new job (education facility) there is a massive requirement to move from spreadsheets to an all inclusive management database. Eventually I need to pull all sorts of stats but I will come to that later.

Courses: The facility I am in runs a lot of short term courses - from one day to 8 week duration. Each course is run multiple times per year (the minimum of each course occurrence is laid down by the head office). Each course is grouped into pillars (each pillar has about 15 courses). Each course has a number of EOs (enabling objectives) but not all are reached and I need to be able to see what EOs are not reached.

Students: Students may attend multiple courses (rarely the same course twice but not unheard of should they fail). They are awarded qualifications on successful completion of a course, but that is dependant on what EOs were reached. I need to chase students 6 months after a course to carry out a Course Review Questionnaire.

Staff: Each member of staff has a title, staff number, position name, position number, and fits into one pillar for course delivery. Also, each member of staff needs to complete some mandatory training annually - about 15 small courses which I need to track their due date for each staff member.

I also want to be able to manage classrooms - we have a number that differ in maximum capacity - from 6 to 16. Each course has a maximum capacity of students (based on course content not classroom size) and I need to be able to link the two together to enable a slightly easier planning phase of course generation.

First question is - this year we are planning 500 course occurrences with about 3000 students going through. Is Access up to the task?

Second question - I have started to design this, but scrapped it as I think I am trying to solve all the issues at once. What is the best way to attack this problem?

Third - the HQ are mental about stats. How many training days are we actually delivering, what EOs are we missing, why are students attending the course, how many courses were cancelled for non-attendance, how many no-shows did we get per year, to name but a few.

I see this as three databases in one - a Student, Staff and Course Management databases.

I would really appreciate some expert thoughts on this. Even if I don't get HQ to support it, I am quite keen to build it as a personal project - a bit of self learning!

Many thanks in advance

Matt

 

[GinaWhipp]

You don't set it to maximized...

In the Forms Property Window
Pop-Up = Yes
AutoSize = Yes
Scrollbars = None

 

[mattyn]

Oh so simple.

TY.

 

[GinaWhipp]

Yeah, it's the forest for the trees scenario, glad to help...

 

[mattyn]

An issue has risen regarding cascading combo boxes. I have followed and got working (almost) the cascading boxes as detailed in the link below under Cascading List Demo 2 to make one for Service (RN, Army, RAF, Civilian) and then Rank (a long list of each rank in each service). All driven from tblAll which has two columns, tblAll.Service and tblAll.Rank

I have utilised it in a couple of forms, and as far as I could see made it work. However there are two snags:

1. When I close and then re-open the form, the data on the Service field on the form and table has gone blank. I would like to keep it for future statistical reasons.

2. One form is a tabular form, when I choose the Service on record 2 onwards, all the previous records Service field change to what I most recently selected.

Code is as such:

Service Field Row Source: SELECT DISTINCT tblAll.Service FROM tblAll ORDER BY tblAll.Service;
Service Field After Update: [Event Procedure]
Option Compare Database

Option Explicit

Private Sub cboService1_AfterUpdate()
On Error Resume Next
Combo31.RowSource = "Select tblAll.Rank " & _
"FROM tblAll " & _
"WHERE tblAll.Service = '" & cboService1.Value & "' " & _
"ORDER BY tblAll.Rank;"
End Sub

cboService1 is the combo box for Service, Combo31 is the combo box for Rank.

Any ideas why 1 and 2 occur, and how to fix? If you need more info please let me know.

Many thanks

Matt

 

[GinaWhipp]

1. Not sure about that unless you are opening to a New record?
2. That is a problem with Combo Boxes with Criteria on a Continuous Form. A long time ago some wrote something to *fix* that, I just need to remember who that is...

 

[GinaWhipp]

Found it!

Sandra Daigle's Access Samples

 

[mattyn]

Gina

Thank you - No - it is missing on saved records. Any idea?

and thanks for the link but it completely baffles me as to what I need to do!

Matt

 

[GinaWhipp]

Missing on saved records? Sorry, I don't understand please provide more details...

Having never used that myself because I don't do that on Continuous FOrms I would have to look at it. I will try to do this weekend.

 

[mattyn]

Do you want my database?

I do appreciate your help!

Matt

 

[GinaWhipp]

Nope, I was going to look at the sample to see what was required and why it's confusing.