Solution for a drop down list to fill in form

[Tangent]

In my driving school database I currently have three related tables:
Pupils
Prices
Lessons

I also want to create a table for TestResults with fields for all the criteria on the driving test form so that I can monitor whether I have any weaknesses in my tuition skills or whether any particular examiners are harsher than the others.

I think that is all that I will need.

The Lessons table is so that I can enter all lessons given at the end of the week. The fields are:

PupilId
PupilName
Date
Time
TypeOfLesson (I do lessons at various different prices)
Duration
CostPer30MIns (since I cannot enter 1.5 for a one and a half hour lesson)

At the end of the tax year I can run a query to find the price and duration for all lessons for that financial year by running a query

I can then copy this into Excel and get it to multiply all the rows to give me monies taken for each lesson and then total column C to give me my turnover for the year for my tax return.

In future I will want to get information such as comparing the numbers of lessons taken to test of 17-21 year old males with 17-21 year old females and other similar stuff. Therefore the PupilId in the Lessons table will be related to the PupilID in the Pupils table.

I am totally new to this and although I can handcode html and build a web site I am totally baffled by most threads on this forum! So if I have made any glaring mistakes so far I would love to try to understand what they are and why.

My problem at the moment is this:

I need a dropdown list of my current pupils only when filling in the lesson data each week. I don't believe it is possible to separated current pupils from non current pupils in a lookup field. The 'Current' field in the Pupils table is a yes/no field.

My solution to this is to run an append query to bring up the PupilID and PupilName of all my current pupils into another table, PupilsCurrent.

This table should not have any relationships with any of the others.
It is just so I have say 24 pupils to select from instead of hundreds later on.

Is this solution not a good one?
And if it isn't what is an alternative one?

 

[Brianwarnock]

On the input form for the lessons table you will have a combobox for the control source for the pupilid, the record source for this combobox will be a query based on the pupils table which will have the criteria set to select current pupils only.

Brian

 

[Tangent]

Thanks Brian