Access/Database Issue

Slogan · 10 Dec 2007 at 18:15

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

Code:

Driver Number*
Driver Name
D.O.B

Tests

Code:

Test Number*
Date
Time

Examiner

Code:

Examiner Number*
Examiner Name
Employment date

Car

Code:

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.

JIMA · 10 Dec 2007 at 22:57

Hi,

If there is only one examiner per test then you would include the examinerno in the Test table as a FK. The test table would then give you information about the test, who sat the test, and who the examiner was. Given a test you could join back to the examiner table to get their details. Nothing wrong with this as you're using the key to join the tables and not holding information in multiple places. The maintaining of the integrity of the tables e.g each examinerno must exist on the Examiners table must be enforced and can usually be done using constraints, rules etc. depending on the database being used.

If there was more than one examiner per test then you'd need a "resolver" table to resolve the many-to-many relationship (each examiner also performs many tests), something like TestExaminer. This would contain two columns, examinerno and testnumber. Whether you use this combination as the key or decide to create a surrogate key e.g a manufactured unique key for each examinerno+testno combination, would then need to be considered.

You could use the TestExaminer table in the one-to-many scenario but it could be making things more complex than they really need to be.

Hope that helps.

Jim

Slogan · 11 Dec 2007 at 15:08

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

Fourstar · 11 Dec 2007 at 16:49

I'm not positive on the syntax as I haven't used access for ages.

Code:

SELECT ExaminerNumber, CarColour, CarBrand
FROM Car INNER JOIN Examiner ON Car.CarNumber=Examiner.CarNumber
WHERE
  Car.CarColour = "White" AND
  Car.CarBrand = "Ford"

Do you have a relationship between Car and Tests along with Car and Examiner?

Without a Car/Tests relationship when an examiner gets a new car all of his old tests will be recorded as using his new car.

Slogan · 11 Dec 2007 at 18:08

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 · 11 Dec 2007 at 21:12

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:

Code:

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.

voodooflux · 11 Dec 2007 at 23:41

You need to surround the date with # symbols I believe:

Code:

SELECT DOB FROM Driver WHERE DOB >= #08/18/1967#

EDIT: And ditch the semi-colon at the end.

suarve · 12 Dec 2007 at 18:16

That looks like fairl basic sql there mate. I'd defo have a read of the w3 schools tutorial as that covers everything you have asked above.