MS Access Coursework Help?

[Dr Drae]

Basically, for one of the units of my course, I've got to design and create a database in MS Access ( ) for a small business offering driving lessons. I've got 6 tables;

-Instructor Details
-Student Details
-Car Details
-Car Usage
-Lesson Booking
-Receipts

I'm not learned in mysql or stuff, and I haven't used access much before this, so I was wondering firstly and foremost; how can I break it, and how do I stop other people from breaking it in the same way?

Secondly, in student details, the date of birth obviously needs to be at least 17 years ago. Can somebody help me with the validation rule for that?

Many thanks will be given for any help. :3

 

[Wol]

accesslol.

You mentioned MySQL. Remember MySQL is a database engine similar to MS Access. Their common thing is their use of SQL (the language). So it doesn't matter how learned you are in MySQL, it ain't gunna help you with MS Access.

1) Why are you trying to break it?

2) NO! Bad. You don't want to restrict the students birthday to be more than 17 years ago.

Are you trying to do this as a form in access or is it a different interface to add/edit/view the data?

You should be able to do some form of dateadd functionality if you want to compare dates somewhere. Can't you just choose a date entry field, rather than some form of text entry field though? I'd have thought that it would have ensured that the date was valid as you'd only be able to select from a calendar, rather than typing in "roflcopter".

 

[Dr Drae]

Oh yes, I was just putting out there before somebody else goes "mysql!" or something.

1) I'm trying to break it to make sure I can stop those problems from happening later on. Also, I need to compile a list of typical errors etc, and write a report on them. Trying to provoke a few isn't cheating, is it?

2) Possibly a stupid question, but why not? "

I've got it as a date field, but I'd like to put the validation in, for the extra marks. An 'advanced feature' attains me higher marks, apparently.

 

[Wol]

Oh yes, I was just putting out there before somebody else goes "mysql!" or something.

1) I'm trying to break it to make sure I can stop those problems from happening later on. Also, I need to compile a list of typical errors etc, and write a report on them. Trying to provoke a few isn't cheating, is it?

2) Possibly a stupid question, but why not? "

I've got it as a date field, but I'd like to put the validation in, for the extra marks. An 'advanced feature' attains me higher marks, apparently.

1) If you mean breaking it in use, then just try most combinations of button pressing between forms, what happens if you dont enter data, or just type text into it when its expecting a number / vice versa. That should mostly be covered by validation though most of the time.

2) Example: Mum phones up company a month before her darling daughters' 17th birthday and wants to book a lesson to be on her daughters birthday. When she phones up, the daughter will be 16 and 11 months old.... How do they enter the girls info when it'll keep telling them that shes too young?

What you *do* want to check is Students.DoB + 17 years > *Lesson*.Date, not the current date.

^^^^^ its things *assumed* to be "obvious" like that in a spec that can cause a system to fall over when actually in use. A very important part of doing a specification is that you have to consider the actual environment in which it will be used.

doing date validation should be a matter of putting something like "<= date()" in the "validation rule" property in the table design as far as I remember. That should prevent a date in the future from being entered. You can do other rules to do only numeric values. Or other things like "car registration should be up to 7 (?) alphanumeric uppercase characters, only allowing spaces."


Whats your table design so far?

 

[BiG D]

2) Example: Mum phones up company a month before her darling daughters' 17th birthday and wants to book a lesson to be on her daughters birthday. When she phones up, the daughter will be 16 and 11 months old.... How do they enter the girls info when it'll keep telling them that shes too young?

Sounds to me like the assignment says "for bonus marks, make sure students are at least 17 years old."

 

[Wol]

Sounds to me like the assignment says "for bonus marks, make sure students are at least 17 years old."

I was always the pain in the arse student who would tell them why their "for extra marks" things were dubious, and would implement it properly and then argue with the teacher! Aaaah. GCSE systems and control. What fun you were.

Guess it depends how obstreperous you are!

 

[Dr Drae]

I'd prefer to implement something properly and learn how to do something that isn't retarded than end up fucking a database later in life (if that's what I go on to do). It only says "add an advanced feature", which my lecturer says this sort of thing would qualify for, and basically said "just do that".

So what I think you're saying is that it would be better to have my receipt table validate by referring to the DOB in the student details field?

Also, thanks for the 1) stuff. :3

And cheers for this; it's much better to be taught by people who know what they're talking about, as opposed to somebody who can't directly answer a question. >.<

 

[Wol]

If it was me, I'd do something like checking that the person is 17 or over on the scheduled date of the lesson, and in the report (Assuming you have to write one?) would say that you could have done a simpler option of "date of birth is 17 or over, but that wouldn't take into account you can book before 17, but as long as you are 17 on the date of the lesson, it's okay". Shows a bit more forethought before implementation I'd have said.

Or at least I'd give someone more credit if they'd done that!

 

[Ronin Storm]

On the criteria "add an advanced feature" then doing it Wol's way sounds like an excellent way to proceed.

On a practical / real life note, though, this is something where clarifying the requirement would be more important than producing any code. Incomplete, incorrect or misinterpretted specifications result, more often than not, in broken systems. The place to fix this is as early as possible, preferably back when you're reviewing the specification. In this case, the question "what constitutes an advanced feature?" is something to be very clear on.

For business systems I find that implementing is relatively speaking fairly easy. Understanding what is wanted or needed can be comparatively difficult, especially when dealing with less technical customers with small budgets.

Boils down to: get your requirements right.

Conversely:

Requirements don't produce anything. A broken system may be better than no system at all. Balance is needed.

Hence:

All praise agile development.

 

[Wol]

Boils down to: get your requirements right

And as I'm sure you already know Ronin: The main problem is that the customer rarely knows what they want in the first place

 

[BiG D]

If we're talking practical real life experience, don't use access

 

[Wol]

If we're talking practical real life experience, don't use access

too true.

Although my other half isn't too bad at it. Ended up making various databases and forms and the like for work over the last few years. Turns out people do actually use it in the real world!

Odd.

 

[Ronin Storm]

Ah, well, yes, there is the "don't use Access" line to it all. However, I also know that a number of public sector departments won't accept anything else so while it's antiquated and, in some respects broken, it's still used.

(When I say "accept", it's all a matter of integrating software onto a "secure" business network, where the easy routes may involve Access and anything else is too expensive and far too time consuming.)

 

[VibroAxe]

If we're talking practical real life experience, don't use access

The main thing here, is access can be used a single standalone file, with builtin ui and everything which can be accessed by anyone with a >buisness suite of office.

If i write a simple database in mySQL and then wish to use it somewhere else it's alot of faf to setup the database again and install whatever ui/interpreter i've used. Just running with access (for the simple buisness world) is actually not all that bad.

 

[Ronin Storm]

An alternative to MS Access, of course, is SQL Server Express. It's like mini-SQL Server but relatively easy to install. No front-end, of course, but that's what we developers are for, isn't it? I guess it's kinda like MDAC, just worthwhile.

 

[BiG D]

Just running with access (for the simple buisness world) is actually not all that bad.

It's about as bad as running windows 98, I'd say. Just because it mostly works doesn't make it a good solution.

 

[Dr Drae]

IF I had a choice in the matter, I'd learn sql or whatever it is, and do a proper database. But this unit is only some 9 weeks long; and we've all got to conform.

But definitely thanks for your help guys; hopefully I'll be able to actually achieve something on this unit. Hopefully you won't mind me drawing on your collective knowledge at other points in the next couple of years

Cheers ^_^

 

[Ronin Storm]

Hopefully you won't mind me drawing on your collective knowledge at other points in the next couple of years

Not at all. Databases aren't really my thing, though I know a bit. Shout when you hit scripting or programming, though, and I'll be more helpful.

 

[DeZmond]

It's about as bad as running windows 98, I'd say. Just because it mostly works doesn't make it a good solution.

As a mac user, what really gets under my skin is the lack of compatibility with the equivalent office suite - Access is simply AWOL, not great if you are given a project to do in Access!

On a different note entirely, has anyone tried Oracle Express (the free one) and if so, is it any good? Currently pondering what form of SQL server to install as I'm going to be needing a decent development environment in the coming months.

 

[Ronin Storm]

SQL Server comes in a Development edition, at least in 2005 (admittedly old). That's a reduced licence cost but full SQL Server. Alternative, SQL Server Express (in whatever version, 2005+) can be made very functional with the free SQL Server Client Tools package.

For single user local development stuff, SQL Server Express is probably what you need.

Not really got a whole lot of experience with Oracle so couldn't help on that.