Newbie trying to create schedule database

[Ettiene]

Hi

I want to start off by saying I have NO experience with access or any type of database. I did a introduction to access course on Udemy by Bruce Myron so I have a vague idea of what is a table, form and query.

Now onto what I want to achieve:
We run a driver training centre, students will buy a course that has a number of hours. For example a course is 5 hours long and its broken into 30 min lessons.
My office lady must then schedule the students on the vehicles in 30 min lessons. Currently everything is just typed into excel and printed out and given to the instructors.

This causes some problems namely:
1) If a person did not pay for the full course he only gets the hours he paid for, there is nothing preventing the office lady just to copy and paste another cell with his name in and book him for training without payment.
2) Some of the courses we offer has different "activities" that must be done in sequence. For instance the student must first have basic knowledge of the vehicles controls before he/she can venture on the freeway/highway . Again this is left up to the office lady to make an comment in excel what activity the student has done before and then to manually check the next lesson what must be done next. This is obviously prone to errors.

My idea is the following:
Paid hours get imported into a table from excel by a authorised person, each record represents 30mins for a student.
Then a lot of dropdowns on a form that represents 30mins each, the office lady then selects what student she wants to schedule what time.
After she selects and 30min lesson in one drop down it must not be able to be selected again in any other dropdown.

I have attached an excel that a kind lady made for me to achieve this, but I think access will be much better suited.
I have attached the access database I have tried to create.

I made a table with the students student number, name and the current hour in one field, the other field has a yes/no to show if the lesson has been allocated.
I made a query that checks what records is NO, the combo boxes reads this query. After selecting an lesson in a combo box there is a macro that runs another query that updates the NO to YES. Currently it updates all the records to YES I do not know how to let it update only the one record.

Help and suggestions will be highly appreciated, as I said I have no experience in this and don't know if I am barking up the wrong tree.

Thank you

Ettiene

 

[Dreamweaver]

I can't look at your files at the moment but will tomorrow if nobody else is able to help you.

 

[Deleted member 147267]

I'm going to go out on a limb here and say that every idea you have expressed in your post and what you say you have done is not going to work very well, if at all. Already you're mixing unrelated data (hours or hours slots) with student data.

Did you search for any templates that are off the shelf suitable, or at least ones that would give you pointers? At the moment, you seem to have a skeleton of an idea, but who is going to put the muscle on it? Without some inherent knowledge (normalization perhaps being the most important of all) you are in for a long and steep learning curve if you want to do this by yourself. Be prepared to start over at least once (not because it's the norm, but because of your self proclaimed lack of experience). A students, scheduling and payments db is no easy task IMO.

 

[Uncle Gizmo]

Well there's no Excel spreadsheet, and the sample database you have provided does not convey any useful information. Your first step is to identify the tables you will need. It looks like you might need a table for courses which you would call tblCourses and would have Fields possibly CourseID, CoursName, CourseDuration, CourseCost... Then you will need a schedule table tblSchedule, I'm not really sure what fields you need in the tblSchedule table, but I'm guessing it will be apparent once you provide the spreadsheet. And you're probably going to need something similar to an invoicing table.

 

[Ettiene]

There is no payments or other info that must be on the database. I only need a list of hours that was done or not done.

I have attached the excel

 

[Deleted member 147267]

There is no payments or other info that must be on the database.

In that case, maybe you can adapt a scheduling db. One of the best Outlook style ones I've seen is here (I forget which post). If you take a look at it, there might not be enough appointment data in the table(s) so just add some and see if you think it will work for you. You could probably add table fields to support the existing form or forms that you might add to expand its usefulness. If the link to the file is missing, let me know.

 

[Ettiene]

I had a look at the link you posted, as far as I can see its just a normal calendar where you add the appointments manually. There is nothing that limits the user from booking more time than paid for.

Edit is this database normalised?

 

[Ettiene]

I'm going to go out on a limb here and say that every idea you have expressed in your post and what you say you have done is not going to work very well, if at all. Already you're mixing unrelated data (hours or hours slots) with student data.

Did you search for any templates that are off the shelf suitable, or at least ones that would give you pointers? At the moment, you seem to have a skeleton of an idea, but who is going to put the muscle on it? Without some inherent knowledge (normalization perhaps being the most important of all) you are in for a long and steep learning curve if you want to do this by yourself. Be prepared to start over at least once (not because it's the norm, but because of your self proclaimed lack of experience). A students, scheduling and payments db is no easy task IMO.

Did you had a look at the excel I posted in my second post?

I am willing to pay someone to assist me with this.

 

[Uncle Gizmo]

Now onto what I want to achieve:
We run a driver training centre, students will buy a course that has a number of hours.

I am willing to pay someone to assist me with this.

I don't think you are in a position yet, where you could employ an expert to help you. The problem is you will need to explain to the expert what you want, and I don't think you know what you want yourself. You have a vague notion, but you have no concrete idea. Now I'm sure someone will gladly take your money to help you through this process but it will very likely be a long and unrewarding experience for both you, and anyone helping you. You would be far better off collecting your thoughts, specifying exactly what you want, honing this down into something you can discuss and turn it into a useful database.

There are several techniques to help you with this process, building something in Excel, in something you are familiar with will help you organise the data aspect and this data will turn into tables.

Record the way you run your business, make notes. Every Time you realise you've done something which you would like to do in the database, make a note of it. I find a very effective way of doing this is to have post-it notes. At the end of the day spent 10 minutes collating all the notes in a Word or Google Doc. Alternatively, use an APP on your phone, or send yourself an email to yourself.

To develop an idea of how your application should look, find existing applications. Many vendors provide evaluation versions of their applications. A good place to find these "Vertical Applications" is look in your Industries magazines. Very often you will find adverts promoting software that can help you advertised in these Mags. It is possible you will find some software that will do exactly what you want, however if it doesn't do exactly what you want then it's time for an MS Access database. You will be well ahead of the game because you will be able to describe in detail what you want and even provide screenshots.

 

[Ettiene]

Thanks for the advise.
I seems there is a communication gap somewhere.
Let me ask this, is there a way to do exactly what the excel is doing in a access database?

Thanks

 

[The_Doc_Man]

is there a way to do exactly what the excel is doing in a access database?

That, I will answer in the abstract. Access can do almost anything Excel can do. BUT... they are structurally dissimilar and therefore have issues in those differences. Excel can trivially reference the previous row. In Access this is remarkably more difficult. Excel is less worried about internal data formats. Access is very much more exacting in that regard.

My advice to you is a two-part bit of advice, since you are trying to design this. I.e. this is not a "fix" yet because it is not implemented yet.

Old Programmer's Rule #1 - if you can't do it on paper, you can't do it in Access.

If you don't have a data-flow or work-flow diagram or some other hard-copy explanation of your business flow, you are not ready to try to implement anything in Access. This is because Access is an expert at making tables, queries, forms, reports, macros, and modules - but has absolutely no way to know what to put into those items. YOU are the subject-matter expert. Access is a glorified power tool. E.g. just because you have a power saw and a power drill and a hammer doesn't mean you are qualified to build a French-motif armoire. You have to know a little about carpentry. You need blueprints or something like that. Similarly, you cannot expect Access to do the work of constructing your app if you don't understand how it goes together. You need a "road-map" that answers all the questions you are likely to ask about "when I see this condition, what do I do about it?" If you don't do a thorough up-front analysis, you will come to grief when slogging through the detail-work.

Old Programmer's Rule #2 - Access won't tell you anything you didn't tell it first.

Computer apps are dumb unless you put some "smarts" in them. But they also can't give you data if you don't give them any. So if you want to see some output of "X" then somewhere in that app there has to be an input of "X." If you want to see XYZ then you either need a direct source of XYZ or you need inputs of X, Y, and Z plus the formula that combines them to give you XYZ. Which means that after you have your road-map, you will often have to back-track through the data flow to verify data availability.

 

[Ettiene]

That, I will answer in the abstract. Access can do almost anything Excel can do. BUT... they are structurally dissimilar and therefore have issues in those differences. Excel can trivially reference the previous row. In Access this is remarkably more difficult. Excel is less worried about internal data formats. Access is very much more exacting in that regard.

My advice to you is a two-part bit of advice, since you are trying to design this. I.e. this is not a "fix" yet because it is not implemented yet.

Old Programmer's Rule #1 - if you can't do it on paper, you can't do it in Access.

If you don't have a data-flow or work-flow diagram or some other hard-copy explanation of your business flow, you are not ready to try to implement anything in Access. This is because Access is an expert at making tables, queries, forms, reports, macros, and modules - but has absolutely no way to know what to put into those items. YOU are the subject-matter expert. Access is a glorified power tool. E.g. just because you have a power saw and a power drill and a hammer doesn't mean you are qualified to build a French-motif armoire. You have to know a little about carpentry. You need blueprints or something like that. Similarly, you cannot expect Access to do the work of constructing your app if you don't understand how it goes together. You need a "road-map" that answers all the questions you are likely to ask about "when I see this condition, what do I do about it?" If you don't do a thorough up-front analysis, you will come to grief when slogging through the detail-work.

Old Programmer's Rule #2 - Access won't tell you anything you didn't tell it first.

Computer apps are dumb unless you put some "smarts" in them. But they also can't give you data if you don't give them any. So if you want to see some output of "X" then somewhere in that app there has to be an input of "X." If you want to see XYZ then you either need a direct source of XYZ or you need inputs of X, Y, and Z plus the formula that combines them to give you XYZ. Which means that after you have your road-map, you will often have to back-track through the data flow to verify data availability.

I was not referring to excel as a program. I was referring to the excel file I uploaded. Did you have a look at it?

 

[Uncle Gizmo]

[

I was not referring to excel as a program. I was referring to the excel file I uploaded. Did you have a look at it?

seems there is a communication gap somewhere.

Indeed!

You have had two, not so dissimilar responses to your question and you are just ignoring them... Good luck with your project....

 

[Ettiene]

[



Indeed!

You have had two, not so dissimilar responses to your question and you are just ignoring them... Good luck with your project....

The two responses does not answer anyone of my questions directly. They only state some obvious general information.
Can you answer maybe as to why when running the macro it changes all the NO's to YES's. I know it has something to do with that the id isn't selected to only update the one record.

Regarding the whole not knowing what I want thing you guys keep on preaching. Why is it that I have given the same explanation to an excel expert and within a day she replied back with a somewhat workable sheet, with pointer why something won't work and what is the workaround.

 

[theDBguy]

I am willing to pay someone to assist me with this.

Hi. Please send me an email (link below) to see if we can work together on this. Cheers!

 

[Deleted member 147267]

If the question about looking at the spreadsheet was directed to me, no, sorry - haven't had time. Besides, you stated you wanted to do something in Access; you uploaded a database ( I think) and you want a database solution, so I didn't see the point in looking at your spreadsheet. Nor do I see the point of your comment about how you quickly got an answer for an Excel problem if you want to develop an Access solution.

Sorry for your less than favourable experience so far; that is not anyone's intent. I think it might have to do with the fact that we see the same sort of situation time and time again, so maybe we're a bit biased. By that I mean the OP claims to know nothing about databases and poses questions we can't answer because they don't realize the breadth of business process knowledge that it often requires in order to begin to formulate an answer. I think that if you contract this out you will quickly realize the complexity of the process, although you won't have to learn the intricacies of it.

Can you answer maybe as to why when running the macro it changes all the NO's to YES's. I know it has something to do with that the id isn't selected to only update the one record.

As for that question, I'd say you hit the nail on the head. It has everything to do with lack of discrimination when the macro/code/sql does the updating. It appears that you already realize the problem, but perhaps what you're looking for is a solution to that. Sounds to me like you got that one solved somewhere else. Makes me think there is some cross posting involved here...

If DBGuy takes this on for you, it should turn out fine.

 

[strive4peace]

hi Ettiene,

Welcome!

Your Access database has just one table ... it is a mistake to set up Access tables the way you do Excel spreadsheets.

You have Instructors, Cars, Students, and Schedule. Probably more tables too!

 

[Ettiene]

If the question about looking at the spreadsheet was directed to me, no, sorry - haven't had time. Besides, you stated you wanted to do something in Access; you uploaded a database ( I think) and you want a database solution, so I didn't see the point in looking at your spreadsheet. Nor do I see the point of your comment about how you quickly got an answer for an Excel problem if you want to develop an Access solution.

I am getting lectured by how I don't know what I want and I must first get some ideas down on paper of how my business works. Yet the excel sheet I posted is 80% of the requirements, it is the "screenshots" that conveys the idea much better than words can. From your wording I gather you didn't even open the access database, and as you said you didn't look at the spreadsheet. How can you even give any input if you don't have the faintest idea of what is required or what have been done so far? Where did you ever get the idea from that I am going to code a student information + scheduling + payments database on my own?

As for the excel problem, I am trying to compare the "helpfulness" of the replies received here by Access "boffins" versus the another boffin. The same business background was given to them with a sample file of an attempt. Yet there was much more achieved corresponding with them achieved in an short time.

Sorry for your less than favourable experience so far; that is not anyone's intent. I think it might have to do with the fact that we see the same sort of situation time and time again, so maybe we're a bit biased. By that I mean the OP claims to know nothing about databases and poses questions we can't answer because they don't realize the breadth of business process knowledge that it often requires in order to begin to formulate an answer. I think that if you contract this out you will quickly realize the complexity of the process, although you won't have to learn the intricacies of it.

I understand if you can get a bit peeved with the same problems over and over, but don't put each new person under the same umbrella. Instead of taking your valuable time typing a generic "rejection letter" rather try to help the person with constructive points. Or just don't reply.

As for that question, I'd say you hit the nail on the head. It has everything to do with lack of discrimination when the macro/code/sql does the updating. It appears that you already realize the problem, but perhaps what you're looking for is a solution to that. Sounds to me like you got that one solved somewhere else. Makes me think there is some cross posting involved here...

It may surprise you but I came to that conclusion ALL by myself. You can search high and low, you won't find this question on any other forum. I did actually read the guideline of not crossposting before I posted. It shows your true color that you think so little of me, that I am just a stupid newbie without your inherent access knowledge that I couldn't figure it out on my own.
I need the Access boffin's to tell me how to fix it. Heck maybe its just a question of putting a primary key in the table or referencing something correctly.

 

[strive4peace]

hi Ettiene,

> "the excel file I uploaded... Did you have a look at it?"

with all due respect, your first upload was another copy of the Access database ... so if someone opened it, as I did, they wouldn't have seen the actual Excel file that you posted later.

We are all just trying to give you help -- sometimes it is short because folks don't have much time ... so don't read more into that.

It is frustrating to ask for help, and frustrating to get to the point where you have to. Luckily you're in a good place with people who have much knowledge to share.

Building a successful database application takes planning.

Looking at the database you posted, I see that the fields that are grouped -- AccountNumber, Status, and TypeWheels each need their own tables to define them

for Status, so far I see Active and Cancelled -- what are other choices?

What I referred to as TypeWheels would be Car, Bus, or whatever else. And perhaps each car or bus also has identifying information?

AccountNumber -- who's account? Where is the table to define who account numbers are assigned to?

Seeing your data from another perspective can take time ...

btw (by the way), on naming fields: its good not to use spaces or special characters (underscore _ is ok), and always start with a letter of the alphabet.

 

[Deleted member 147267]

Or just don't reply.

For you, that will be a pleasure going forward.