Access/Database Issue

[Slogan]

Hello folks, (hope this is the right forum)

Just an issue I have with Access at the moment and it's more than likely down to poor planning than anything else.

I have to create a database relative to a driving test centre. I have been given four entities, DRIVERS, CARS, EXAMINER and TESTS.

I have created four tables for these as follows, identified with an asterisk as their primary key:

Drivers

Driver Number*
Driver Name
D.O.B

Tests

Test Number*
Date
Time

Examiner

Examiner Number*
Examiner Name
Employment date

Car

Car Number*
Car Brand
Car Colour

So far that seems okay to me - I don't do databases a lot so I could be very wrong. The problem arrives with foreign keys. I can move DriverNumber into Tests but I can't move Test Number into Examiner, because Examiners should be able to hold or take many tests. I either am left with the options of duplicating primary key fields (examiner number) or adding multiple entires into the tests that the examiners hold.

I don't think either of those are practical and creating as many examiners as there are drivers doesn't reflect the many to one relationship between test and examiner.

Any suggestions on a different approach around this? The attritubutes under the entities in the tables are totally flexibile. I'm just a bit stumped, but will keep on playing around with it.

Thanks.

 

[Slogan]

Hello,

Thanks for your reply. Unfortunately it did help - but I'm too far along to start all over again and sort things through - so I went for the lazier option of just adding as many driving instructors as there are drivers or "learning" drivers. It breaks the many to one but I honestly don't care.

I have a SQL issue with it at the moment. I'm needing:

Two tables joined in a single query block with 2 extra selection clauses (2 extra AND clauses over and above the join clause, which represent one selection clause from each of the two tables)

I have knocked together an SQL for this like follows:

SELECT ExaminerNumber, CarColour, CarBrand
FROM Car INNER JOIN Examiner ON Car.CarNumber=Examiner.CarNumber;

This displays the ExaminerNumber and CarColour and CarBrand relative to the ExaminerNumber (they are assigned their own car). However, I'm having trouble with the two AND clauses that are needed - any AND clauses that I add seem to be breaking up the code.

How would I say I want the CarColour to be say, White and the CarBrand to be a Ford?

Thanks

 

[Slogan]

Hey,

I do have a test and car relationship, yeah. I attempted the same code that you mentioned there but it splurts back a:

"Characters found after end of SQL statement" error.

Edit:

Actually, that did the trick! Just had to shift the semi-colon.

Thanks

 

[Slogan]

Back again! Hopefully for the last time in regards to this.

I'm trying to nest a query although I'm not sure I'm going the right way about it. Do all nesting queries have to be all relative to eachother or can they be for example, calculating the average cost of something then within that, detailing those who have tests within certain time periods.

In addition to the original post, I added 'LessonCost' to the Driver table. I have to perform an aggregate function which I can do with the LessonCost (average) but I'm having difficulty with the nest after that. Not sure whether I've typed it in wrong or it can't be performed because it's not relevant:

Here it is anyway:

SELECT 
FROM Driver INNER JOIN Test ON Driver.DriverNumber = Test.DriverNumber
WHERE LessonCost > (SELECT AVG(LessonCost) FROM Driver)

   SELECT DOB
   FROM Driver
   WHERE (DOB >=08/18/1967);

Apparently Access only works with American dates so I've had to play about with that but still no luck. Getting a:

'The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect'

Error. Can't see it myself.