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?

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

Very elegant, thanks for your time WAYNE.