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 put that code in a Module and in you query you would use

Work_Days(BegDate, EndDate)

That said, you wouldn't store Total Days as it's a calculation.

 

[mattyn]

Magic thank you. Rather simple.

Now, I have another question regarding a drop down menu based on a previous drop down menu.

The first in in a table called tblStudent. One field is a drop down menu from a table called tblService, which has 5 different items (Army, RAF, RN, RM and Civilian). The next field is called Rank will be a list of ranks for each Service.

What is the best way to create this? One table for the list of services, then one table (5 in total) for each rank of each service?

And then, how do I link the two fields together?

TIA again !

Matt

 

[GinaWhipp]

Umm, are you using Tables rather than Forms? I hope not. That said, sounds like you need...

tblStudents
tlkpServices
tlkpRanks
tblStudentServices (to which you can associate tblRanks as long as it's one Rank per Service)

 

[mattyn]

Umm, are you using Tables rather than Forms? I hope not. That said, sounds like you need...

Yes I am using tables - well I intended to - that has confused me even more. I always thought I should use tables as the basis for all the forms.

 

[GinaWhipp]

They are the Recordsource for all Forms but you don't put the drop downs in the Tales you use Combo Boxes on the Form.

 

[mattyn]

hmmm - I am confused now.

I have tables as follows:

tblStudent with the following fields: StudentID, ServiceNumber, FirstName, Surname, Email, Service, Rank

tblService which lists services RN, RAF, Army, Civilian.

tblRNRank, tblRAFRank, tblArmyRank, tblCivRank which list all the relevant ranks in each service.

I created a form called fmStudent from the tblStudent.

I also have the following code embedded in Class Objects in fmStudent: The field Service I have linked to the tblService by calling it cboService and its RowSourse is tblService. The Event Procedure is linked to the code below.

Private Sub cboService_AfterUpdate()
On Error Resume Next
Select Case cboService.Value
Case "RN"
cboRanks.RowSource = "tblRNRanks"
Case "RAF"
cboRanks.RowSource = "tblRAFRanks"
Case "RM"
cboRanks.RowSource = "tblArmyRanks"
Case "Army"
cboRanks.RowSource = "tblArmyRanks"
Case "Civilian"
cboRanks.RowSource = "tblCivRank"
End Select

End Sub

The form drop down for Service works, but I get nothing for the Rank. I am stuck - I tried to copy the cascade demo here : Access Tips: Cascading Lists for Access Forms

But I cannot get it to work! The simple alternative is I have a big drop down list for Ranks to include all services, but it would be good to make it simpler.

Anyone help? Gina? Koen?

Many thanks in advance

Matt

 

[GinaWhipp]

Umm, you need one table for Ranks, all Ranks not separate tables then you have one RowSource for your Combo Box no Select Case statement needed.

 

[mattyn]

Gina

So, cancelling what I did last night, I now have

tblStudent with the following fields: StudentID, ServiceNumber, FirstName, Surname, Email, Service, Rank

tblService which lists services: RN, RAF, Army, Civilian, RM.

tblStudentRanks, with fields RankID, ServiceID and Rank and this list all the relevant ranks in each service.

On the form fmStudents. I have created one drop down called cboService, and this works, pulling the list from tblService.

However the next field on this form cboRanks is blank. I don't think it is talking to the previous cboService.

I have deleted that module above, and have no EventProcedure on cboService.

 

[mattyn]

Ignore all that!! Managed to work out where I had gone wrong - and solved it. It now works!!!

 

[mattyn]

Hi All

It is all going well... so far.

I have a form, that I need/want as a pop up form. I use the following on Load:

Private Sub Form_Load()
DoCmd.Maximize
End Sub

to load that pop up form maximised.

Question I have: Is there a way of loading the form to the size it needs to be, rather than maximised to the full screen. I want it big enough to show the full form, without the user having to use scroll bars. The form, when maximised, has a fair bit of blank space.

Hope that makes sense...

TIA

Matt