Confused on which schema is correct!!

[allboutdB]

Hi All,

Can anyone please help me understand out of the two schema's which one is correct or are they both correct, which one will be easier to write SQL queries against, i tried writing an SQL query against Schema1 that would give me the CAR ID, Service Initiated By's first, last name ,Service Completed By's First and last name but i had no success(may be i didn't join the tables properly), does access even allow two foreign keys in one table referencing the same primary key from the other table(Schema 1) i tried enforcing referential integrity in Schema1 but i got an error. Or would i have to break the Car_Service table into two tables, as in Schema2

Cheers

 

[boblarson]

Schema 1 is correct, but you have to add the mechanics table into the relationships twice. Once to link to the initiated by and once to link to the completed by.

 

[allboutdB]

Hi Bob,

Thank you for the quick response i will try the query tonight and see if i get it right, ,its good to know that help is at arm's reach

Cheers

 

[allboutdB]

Hi Bob,

I tried the query below and it gave me weird result

SELECT Mechanics.First Name,Mechanics.Last Name, Mechanics_1.First Name,Mechanics_1.Last Name
FROM Mechanics AS Mechanics_1, Mechanics INNER JOIN Car_Service ON(Mechanics.Mechanic ID = Car_Service.Service Initiated By) AND (Mechanics.Mechanic ID = Car_Service.Service Completed BY);

Am i joining the tables incorrectly(sorry i am still new to SQL)

 

[boblarson]

This would be it:

SELECT CAR_SERVICE.[Car ID], [Mechanics].[First Name] & " " & [Mechanics].[Last Name] AS ServiceInitiated, [Mechanics_1].[First Name] & " " & [Mechanics_1].[Last Name] AS ServiceCompleted
FROM (CAR_SERVICE INNER JOIN Mechanics ON CAR_SERVICE.[Service Initiated By] = Mechanics.[Mechanic ID]) INNER JOIN Mechanics AS Mechanics_1 ON CAR_SERVICE.[Service Completed By] = Mechanics_1.[Mechanic ID];

 

[allboutdB]

Hi Bob,

Many thanks for your help, after playing around for quite a bit i finally got it, initially i had added just the Mechanics and Car_Service Tables in the Design view of query. It showed the two links initiated by and completed by from car_service to the mechanics table then i tried your query and it didn't work and gave me missing value error ,after a bit of playing around i deleted one link between the two tables and added the mechanics table one more time so now with the two mechanics table(mechanics and mechanics_1) i got the query to work but this is what my query looks like(i didn't write the SQL code instead i used the query designer and then switched to SQL view)

SELECT [CAR_SERVICE].[CAR ID],Mechanics.FirstName& " " &Mechanics.LastName AS ServiceInitiated, Mechanics_1.FirstName& " " &Mechanics_1.LastName AS ServiceCompleted FROM Mechanics AS Mechanics_1 INNER JOIN(Mechanics INNER JOIN[CAR_SERVICE] ON Mechanics.Mechanic ID=[CAR_SERVICE].[Service Initiated By]) ON Mechanics_1.Mechanic ID = [CAR_SERVICE].[Service Completed By];
and it works fine now

Cheers