I'm not sure I completely understand the requested query, but here's my attempt (I assume the goal is to achive the result with one query and no temp tables or the like in the way?).
"CD" item is Item.Format = "CD"
"Suppliers Spreadsheet" =
"Customer Order" = Line item in CustOrderItem
"Back Order Route" = Item in BackOrderDetail
p.s. BackOrderDetail isn't designed very well, since you have SupplierID stuck in there even though there's a supplierID in the ItemCode table. Plus item code is there twice?
FROM Supplier S
INNER JOIN SupplierItem SI
ON SI.SupplierID = S.SupplierID
INNER JOIN Item I
ON I.ItemCode = SI.ItemCode
AND I.Format = 'CD'
INNER JOIN CustOrderItem COI
ON COI.ItemCode = I.ItemCode
WHERE s.SupplierID NOT IN ( SELECT DISTINCT SupplierID
FROM BackOrderDetail BOD
WHERE BOD.ItemCode = COI.ItemCode )
So if I wrote that correctly we'd want to get Supplier Names who stock items which have been purchased (COI table) and are Format 'CD', then disregard all Supplier IDs which exist in the BOD table for any items found in the first query.
Although I can't remember if you can reference COI in the WHERE clause...