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

 

[mattyn]

Last night I had a rework, and took advice not to have lookups in table fields. I have created the appropriate forms, and made the lookups from there, linking to the plain short text field in the table.

What I have noticed is on the relationships schematic, there are no relationships created for these links. Is this right, or should I add them in?

For instance, between tblstaff.Rank and tblRank.RankID etc? Or, as I see it from a mechanical point of view, are those minor tables (Rank, Position etc) purely there now to feed the major tables (staff, staffadqual etc)?

Thoughts always appreciated, and as ever, thank you in advance

Matt

Relationship diagram below:

 

[mattyn]

What I think the lack of relationships is causing is when using a query below, I only get in the fields for RankID, AdqualID, where I really need the full description - Rank and Adqual. I want this full description on the subsequent report.

Thoughts? Is there code to add to the fields in the report design or query design?

TIA

Matt

 

[GinaWhipp]

Do you want my database?

I do appreciate your help!

Matt

So, I finally had a minute to look at this example and I see what is being done. You need a Combo Box and a Text Box. What is the part you find confusing?

 

[GinaWhipp]

First of all you what is the new Yes/No table? And, why would you even need that?

Now, before you do the query you must set up the Relationships first. It's like building a house, you put up the walls until the foundation has been laid. So, please post your Relationships.

 

[mattyn]

First of all you what is the new Yes/No table? And, why would you even need that?

Now, before you do the query you must set up the Relationships first. It's like building a house, you put up the walls until the foundation has been laid. So, please post your Relationships.

Gina hi! Hope all is well.

The Yes/No table I created because I did not like the tick box standard from Access.

The relationships I initially had are as posted on 17 Jul at 09:09. All the lookups are created the same way and fields selected have started with the appropriate tableID PK field.

For example:

I created tblMandatoryTraining with fields MandatoryTrainingID (PK) and CourseTitle.
I created tblStaffMandatoryTraining with fields StaffMandatoryTrainingID (PK), StaffID (short text), MandatoryTrainingID (short text), DateAchieved and Review Date.
I then created frmStaffMandatoryTraining from all of the fields in tblStaffMandatoryTraining using the wizard. I then replaced in this form the fields StaffID and MandatoryTrainingID with a combo box.
For the StaffID combo box, I followed the wizard (using a existing table), and pulled through StaffID, FirstName and Surname to appear in the combo box, ordering by StaffID, though this was ticked not to appear in the list.
For the MandatoryTrainingID combo box, I again followed the wizard (using a existing table) and pulled through both fields from tblMandatoryTraining, ordering by MandatoryTrainingID, though this was ticked not to appear in the list.

A similar process was followed to create the tables and forms for tblAdQual and tblStaffAdQual. The correct text shows in the forms, appropriate numerical figures in the tables where data is gleaned from combo boxes.

A Query will obviously then pull data from tables, and this will be some numerical, and not the text I see in the forms. I am happy with this, as I wont use the query itself for management use. I will use a report for that.

However, when creating a report, it still shows the numbers, not the text. And it is this point I am stuck.

I feel relationships need building/adding in but I have no idea what I am doing at this point. Does it need to be something like this? The database works really well up to the point I start creating queries and reports.

 

[GinaWhipp]

You do not need a Table if you don't want the Check Box, you can bind that field to a Combo Box and then use *Value List* to have your Users make a selection.

The Relationships look pretty good so that should be fine. You just might want to consider applying Referential Integrity to all your JOINs, Cascade Update only not Cascade Delete.

When making a Report you need to include the Main Table and the Look-up Table and then instead of the field from the Main Table you use the one from the Look-up Table that shows actual data.

Other than that you should be able to move on.

 

[mattyn]

Gina thank you. I will work on this today, and let you know how I get on.

Matt

 

[mattyn]

Sorted it - thank you!

My next issue.

I carried out some experimenting with insterting images, and have found a clever process online. Whilst carrying out that experimenting, I unnecessarily bloated the size of the Db, and it increased by about 13megs. Is there any way of recovering that and reducing the Db size back to its original 1meg? I have removed the fields etc that I used, but the size does not go back. I have used "compact & repair Db" but that does not seem to work that well.

I now know I should have made a copy of the Db before I started that image work - but stupidly I didn't. Lesson learnt there!!

TIA, again!!

Matt

 

[mattyn]

I have created a new thread also as I have a snag with sub forms and sub-sub forms. Please have a look if you get chance.

Many thanks

Matt

 

[GinaWhipp]

Are you sure you removed ALL the images? If you are and it's still not shrinking try importing all your Objects into a clean database and see what size it shows...