one primary key going to 3 foreign keys 2

[VBVines]

I have a STAFF table that has a pk called staff_id and I have another table EVENTS that has 3 staff fields because on any given transaction up to 3 people from the STAFF table could be involved. I can run this query in sql server but I can't get it right in Access. Here is my sql server query

select staff.name, staff2.name as name2, staff3.name as name3,Events.event_id,events.date
from Events
INNER JOIN STAFF ON events.staff_id = Staff.Staff_ID
INNER JOIN STAFF as staff1 ON events.staffa = staff1.Staff_ID
INNER JOIN STAFF as staff2 ON events.staffb = staff2.Staff_ID
where Events.events_id = ' " & cmbevent & " '






aspvbwannab

 

[Golom]

Looks OK ... but Access loves parentheses and name may be a reserved word (not sure about that.) Date is definitely a reserved word.

select staff.[name], staff2.[name] as name2, staff3.[name] as name3,Events.event_id,events.[date]
from ((Events
INNER JOIN STAFF ON events.staff_id = Staff.Staff_ID)
INNER JOIN STAFF as staff1 ON events.staffa = staff1.Staff_ID)
INNER JOIN STAFF as staff2 ON events.staffb = staff2.Staff_ID
where Events.events_id = '[COLOR=red yellow] [/color]" & cmbevent & "[COLOR=red yellow] [/color]'

Do you really want spaces in the positions shown?

 

[PHV]

Perhaps this ?
select staff.name, staff2.name as name2, staff3.name as name3,Events.event_id,events.date
from [!](([/!]Events
INNER JOIN STAFF ON events.staff_id = Staff.Staff_ID[!])[/!]
INNER JOIN STAFF as staff1 ON events.staffa = staff1.Staff_ID[!])[/!]
INNER JOIN STAFF as staff2 ON events.staffb = staff2.Staff_ID
where Events.events_id = ' " & cmbevent & " '

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VBVines]

Thanks guys both got me going in the right direction but I somehow by luck wound up on this query and it seemed to do the trick. I went back and designed on table with 3 name fields and on staff table with just a name field and a name id field. I could'nt tell you how I wound up with what I have here but it works and I guess I'll study it later. thanks again for taking the time.

SELECT [events].[city], [staff].[name], staff_1.name, staff_2.name
FROM staff INNER JOIN ((events INNER JOIN staff AS staff_1 ON [events].[name2]=staff_1.staff_id) INNER JOIN staff AS staff_2 ON [events].[name3]=staff_2.staff_id) ON [staff].[staff_id]=[events].[name1];

aspvbwannab