Two Queries but only one works for me

[Perceptus]

I made this query. Drives the input functions for order manipulation. The First one works as intended. No issues, the second one. I cannot get to work ambiguous query but I setup the same was as the first AFAIK. I need the 2nd one to be editable, the first one is. Do you see my problem? Both of these were made with the designer.



Query 1

SELECT tblRegions.Location, tblSalesOrders.SalesOrderID, tblCustomerShipTo.SiteName, tblSalesOrdersInventoryLineItems.LineNumber, tblPartMaster.LongDescription, tblWorkOrders.Status, tblSalesOrdersInventoryLineItems.Qty, tblSalesOrdersInventoryLineItems.DrawingNumber, tblPartMaster.DrawingNumber, Year([PrintedDate]) AS PrintedYear, tblSalesOrders.DatePromised, tblSalesOrders.RevisionNumber, tblPartMaster.PartNumber, tblSalesOrders.PrintedDate
FROM tblWorkOrders RIGHT JOIN (((tblSalesOrders INNER JOIN (tblPartMaster INNER JOIN tblSalesOrdersInventoryLineItems ON tblPartMaster.PartNumber = tblSalesOrdersInventoryLineItems.PartNumber) ON tblSalesOrders.SalesOrderID = tblSalesOrdersInventoryLineItems.SalesOrderID) INNER JOIN tblCustomerShipTo ON (tblSalesOrders.SiteID = tblCustomerShipTo.SiteID) AND (tblSalesOrders.CustomerID = tblCustomerShipTo.CustomerID)) INNER JOIN tblRegions ON tblCustomerShipTo.Region = tblRegions.ID) ON (tblWorkOrders.[Sales Order Li] = tblSalesOrdersInventoryLineItems.LineNumber) AND (tblWorkOrders.[Sales Order] = tblSalesOrdersInventoryLineItems.SalesOrderID)
WHERE (((Year([PrintedDate]))>=Year(Now())+CInt(DLookUp("[Property Value]","tblSettings","[Property Name]='SalesOrderDateWindow'"))))
ORDER BY Year([PrintedDate]) DESC , tblSalesOrders.PrintedDate DESC;

Query 2

SELECT tblRegions.Location, tblStockXfersInventoryLineItems.LineNumber, tblPartMaster.LongDescription, tblStockXfersInventoryLineItems.WorkOrderStatus, tblStockXfersInventoryLineItems.Qty, tblStockXfersInventoryLineItems.DrawingNumber, tblPartMaster.DrawingNumber, Year([PrintedDate]) AS PrintedYear, tblPartMaster.PartNumber, tblLoc.LocDesc, tblStockXfersInventoryLineItems.StockXferID, tblPartMaster.LongDescription, tblStockXfers.PrintedDate, tblStockXfers.RequestedShipDate, tblStockXfers.RevisionNumber
FROM ((((tblPartMaster INNER JOIN tblStockXfersInventoryLineItems ON tblPartMaster.PartNumber = tblStockXfersInventoryLineItems.PartNumber) INNER JOIN tblStockXfers ON tblStockXfersInventoryLineItems.StockXferID = tblStockXfers.StockXferID) INNER JOIN tblLoc ON tblStockXfers.ToLocID = tblLoc.LocID) INNER JOIN tblRegions ON tblLoc.Region = tblRegions.ID) INNER JOIN tblWorkOrders ON (tblWorkOrders.[Sales Order Li] = tblStockXfersInventoryLineItems.LineNumber) AND (tblStockXfersInventoryLineItems.StockXferID = tblWorkOrders.[Sales Order])
WHERE (((Year([PrintedDate]))>=Year(Now())+CInt(DLookUp("[Property Value]","tblSettings","[Property Name]='SalesOrderDateWindow'"))))
ORDER BY Year([PrintedDate]) DESC , tblStockXfers.PrintedDate DESC;

[Minty]

It's always difficult to see in a large query string, but your initial join is an Inner join in the second query as a opposed to a Right Join in the first one...

Generally Inner joins render queries uneditable.

[Perceptus]

Just noticed that as well. Good point