Tangent
Always going off on one!
- Local time
- Today, 22:04
- Joined
- Apr 3, 2010
- Messages
- 19
The 'Pupil Details' table of my driving school database includes the fields:
'Pupil Type' (Learner with no previous experience; Learner with previous experience; refresher, etc)
'Age at Start'
'Sex'
with the 'Pupil ID' (a six digit number with the first two digits denoting the year) as the primary key.
The 'Lessons' table, with an automated number as the primary key currently consists of:
Pupil ID
Date
Time
Lesson Type (Learner, Pass Plus, Refresher, Motorway, Parking, Basic Maintenance)
Lesson Price
Lesson Duration
The main purpose of the Lessons table is to be able to easily calculate my turnover at the end of the tax year but I would also like to be able to get statistics from the database after a couple of years, an example of which would be:
How many lessons to test does the average male learner aged between 17 and 21 with no previous experience take compared to the corresponding female learner?
Is this at all possible without including the pupil type, age at start and sex in the lessons table?
The only way a lesson is linked to a pupil is via the Pupil ID field.
Also that one pupil may take Pass Plus lessons after the test but this info is in the lessons table under Lesson Type.
I can't imagine how I'd go about this. I'd first have to establish all the Pupil ID's of all males aged 17 to 21 and then all females aged 17-21 to begin with, which could easily be obtained via a query. But then how would I get all the Learner lessons (but not Pass Plus or Motorway lessons) and the duration of each of those lessons for every Pupil ID in the category and calculate them all?
Hope that makes sense.
I feel that adding all those fields to the Lessons table has got to be the easiest way but is that not duplicating data? I don't want to find months down the line that I have to make major changes and add months or years worth of data.
Any suggestions or views very welcome,
Lois
'Pupil Type' (Learner with no previous experience; Learner with previous experience; refresher, etc)
'Age at Start'
'Sex'
with the 'Pupil ID' (a six digit number with the first two digits denoting the year) as the primary key.
The 'Lessons' table, with an automated number as the primary key currently consists of:
Pupil ID
Date
Time
Lesson Type (Learner, Pass Plus, Refresher, Motorway, Parking, Basic Maintenance)
Lesson Price
Lesson Duration
The main purpose of the Lessons table is to be able to easily calculate my turnover at the end of the tax year but I would also like to be able to get statistics from the database after a couple of years, an example of which would be:
How many lessons to test does the average male learner aged between 17 and 21 with no previous experience take compared to the corresponding female learner?
Is this at all possible without including the pupil type, age at start and sex in the lessons table?
The only way a lesson is linked to a pupil is via the Pupil ID field.
Also that one pupil may take Pass Plus lessons after the test but this info is in the lessons table under Lesson Type.
I can't imagine how I'd go about this. I'd first have to establish all the Pupil ID's of all males aged 17 to 21 and then all females aged 17-21 to begin with, which could easily be obtained via a query. But then how would I get all the Learner lessons (but not Pass Plus or Motorway lessons) and the duration of each of those lessons for every Pupil ID in the category and calculate them all?
Hope that makes sense.
I feel that adding all those fields to the Lessons table has got to be the easiest way but is that not duplicating data? I don't want to find months down the line that I have to make major changes and add months or years worth of data.
Any suggestions or views very welcome,
Lois
