Two instances of the same table

[BruceM]

I have a database for managing a review process that has several steps. The
data are stored in the table tblCAR. Each step has an employee assigned to
it. The employee name is selected from a combo box that uses the Roster
table (tblRoster) as its row source. The EmployeeID number is stored (the
fields OwnerAssign and FolAssign in the SQL below contain two different
EmployeeID numbers). tblRoster and tblRoster_1 are two instances of the
same table. The idea is that on a report I can display the name rather than
the number by binding text boxes to OwnerName and FolName. Is this the
correct approach?

SELECT tblCAR.CAR_ID, tblCAR.OwnerAssign, tblCAR.FolAssign,

tblRoster_1.LastName AS OwnerName,

tblRoster.LastName AS FolName

FROM (tblCAR LEFT JOIN tblRoster ON

tblCAR.FolAssign = Roster.EmployeeID)

LEFT JOIN tblRoster AS tblRoster_1 ON

tblCAR.OwnerAssign = tblRoster_1.EmployeeID

ORDER BY tblCAR.CAR_Number;



I'll just point out that this is a simplified version of the SQL; the other
fields don't affect the result. Also, in actual practice I will concatenate
First Name and Last Name for the FolName and OwnerName fields, but again
there seemed no need to muddy the SQL with that detail. I'm just trying to
figure out if this is a good way to accomplish what I need.

 

[KARL DEWEY]

Try this --
SELECT tblCAR.CAR_ID, tblCAR.CAR_Number, tblRoster_1.LastName AS OwnerName,
tblRoster.LastName AS FolName
FROM (tblCAR LEFT JOIN tblRoster ON tblCAR.OwnerAssign =
tblRoster.EmployeeID) LEFT JOIN tblRoster AS tblRoster_1 ON tblCAR.FolAssign
= tblRoster_1.EmployeeID
ORDER BY tblCAR.CAR_Number;

 

[BruceM]

Thanks. I see that I didn't need to include FolAssign and OwnerAssign in
the SELECT part of the statement. Also, I left CAR_Number out of the SELECT
part, then ordered by it later, which wouldn't have worked had I actually
run the SQL. I use the query for the data entry form as well. When the
names are selected, the FolAssign and OwnerAssign fields are populated, so
those fields are in the data source anyhow. The query about which I asked
is used for a report that uses about 10 fewer fields than does the form, and
the form doesn't use concatenated fields (the report uses several), so maybe
a separate query would make more sense.
Anyhow, you have confirmed using two instances of the same table, and have
given me a few other things to think about as well.