lookup in query sql to another table

[jazminecat]

SELECT BidsTable.BidNum, BidsTable.DocumentTitle, BidsTable.NoticeDate1, BidsTable.NoticeDate2, BidsTable.NoticeDate3, BidsTable.OpeningDate, BidsTable.AwardedTo, tblYear.Year, tblDepartment.Department, BidsTable.[2Department]
FROM tblDepartment INNER JOIN (BidsTable INNER JOIN tblYear ON BidsTable.YearID = tblYear.YearID) ON tblDepartment.ID = BidsTable.Department
ORDER BY tblYear.Year DESC , BidsTable.BidNum;

I have a form based on this query.

However, in my table I now have a need for a second department listing, called 2Department. Not all records will have a second department, but some do. For those records, the ID of the department is stored in the field 2Department. I need to have this query lookup the department name from the department table and show that name when the query is run, when the form loads.

(I have no idea why it's putting brackets around 2Department, but if I go into SQL view to remove them it throws an error so I left them there.)

any help is appreciated.

 

[PHV]

Something like this ?
SELECT B.BidNum, B.DocumentTitle, B.NoticeDate1, B.NoticeDate2, B.NoticeDate3, B.OpeningDate, B.AwardedTo, Y.Year, D.Department, D2.Department
FROM ((BidsTable AS B
INNER JOIN tblYear AS Y ON B.YearID = Y.YearID)
INNER JOIN tblDepartment AS D ON B.Department = D.ID)
LEFT JOIN tblDepartment AS D2 ON B.[2Department] = D2.ID
ORDER BY Y.Year DESC , B.BidNum

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