Show a record for each side of a join

[ssteinke]

I have a table, named tblRecords, and a joined table, named tblUserRecords. The tables are one-to-many joined by RecordID.

In tblRecords, I have a field named UserID to show who entered the record.
In tblUserRecords, I also have a field named UserID for user(s) who have access to the record.

I want a query to show two seperate records if a user has both entered the record and received the record (i.e. tblRecords.UserID and tblUserRecords.UserID are the same).

An example of what I am not looking for,

SELECT tblRecords.RecordID, tblRecords.UserID, tblUserRecords.UserID
FROM tblRecords
LEFT JOIN tblUserRecords ON tblRecords.RecordID = tblUserRecords.RecordID
WHERE tblRecords.UserID='Bob' OR tblUserRecords.UserID='Bob'

This gives me
4 Bob Randy
4 Bob Bob
4 Bob Gregg
7 Gregg Bob
9 Tom Bob

This is what I want
4 Bob Entered
4 Bob Received
7 Bob Received
9 Bob Received

Does this require a subselect or subquery?

 

[WayneRyan]

ssteinke,

Query1:

SELECT tblRecords.RecordID As TheID, tblRecords.UserID As USER, 'Entered' As Action
FROM tblRecords Left JOIN tblUserRecords ON 
     tblRecords.RecordID = tblUserRecords.RecordID
WHERE tblRecords.UserID = 'Bob' 

UNION

SELECT tblUserRecords.RecordID As TheID, tblUserRecords.UserID As USER, 'Received' As Action
FROM tblUserRecords Left JOIN tblRecords ON 
     tblRecords.RecordID = tblUserRecords.RecordID
WHERE tblUserRecords.UserID = 'Bob'

Query2 would just sort that query on the ID Field:

Select TheID, USER, Action
From   Query1
Order By TheID, Action

Wayne

 

[ssteinke]

Very elegant, thanks for your time WAYNE.