Hello all,
I am extremely confused by the results I am getting from 2 different queries that should be very similar. I copied the query that is working properly and renamed it, then modified the new query to use the correct table but when I view the results it makes no sense. The first query uses tblM29242 which has 3 records in it, when I view the results of the query it does indeed give me 3 records as expected. The new query uses tblM16086 which has 9 records however when I view the results of the query I get 6426 records when I should be getting 9. I have checked the relationships and it all seems to be correct. The SQL for the queries is shown below. Can someone please let me know what I am missing?
SELECT DISTINCT tblPartMain.MfrPartNumber, tblPartMain.PartDescription, tblPartMain_1.PartMainID_PK, tblPartMain_1.MfrPartNumber AS Child_Part_Number, tblM29242Rel.Qty, tblPartMain_1.PartDescription AS Child_Part_Description, tblCategories.PartType, tblPartMain_1.SubAssembly, [Work Orders].Qty, [tblM29242Rel]![Qty]*[Work Orders]![Qty] AS [Build Qty], [Work Orders].DHRNumber, [Work Orders].DHRID, Nz([UnitsReceived],0)-Nz([UnitsSold],0)-Nz([UnitsShrinkage],0) AS QtyonHand, tblM29242Rel.Units, [Work Orders].SerialNum, [Inventory Transactions].PurchaseOrderID, tblM29242Rel.DHROrder, tblM29242Rel.MyNotes
FROM ((tblCategories RIGHT JOIN tblPartMain AS tblPartMain_1 ON tblCategories.ID = tblPartMain_1.CategoryID) RIGHT JOIN tblM29242Rel ON tblPartMain_1.PartMainID_PK = tblM29242Rel.ChildID) INNER JOIN ((tblPartMain LEFT JOIN [Inventory Transactions] ON tblPartMain.PartMainID_PK = [Inventory Transactions].ProductID) LEFT JOIN [Work Orders] ON tblPartMain.PartMainID_PK = [Work Orders].PartID) ON tblM29242Rel.PartMainID_FK = tblPartMain.PartMainID_PK
ORDER BY tblM29242Rel.DHROrder;
SELECT DISTINCT tblPartMain.MfrPartNumber, tblPartMain.PartDescription, tblPartMain_1.PartMainID_PK, tblPartMain_1.MfrPartNumber AS Child_Part_Number, tblM16086Rel.Qty, tblPartMain_1.PartDescription AS Child_Part_Description, tblCategories.PartType, tblPartMain_1.SubAssembly, [Work Orders].Qty, [tblM16086Rel]![Qty]*[Work Orders]![Qty] AS [Build Qty], [Work Orders].DHRNumber, [Work Orders].DHRID, Nz([UnitsReceived],0)-Nz([UnitsSold],0)-Nz([UnitsShrinkage],0) AS QtyonHand, tblM16086Rel.Units, [Work Orders].SerialNum, [Inventory Transactions].PurchaseOrderID, tblM16086Rel.DHROrder, tblM16086Rel.MyNotes
FROM ((tblCategories RIGHT JOIN tblPartMain AS tblPartMain_1 ON tblCategories.ID = tblPartMain_1.CategoryID) RIGHT JOIN tblM16086Rel ON tblPartMain_1.PartMainID_PK = tblM16086Rel.ChildID) INNER JOIN ((tblPartMain LEFT JOIN [Inventory Transactions] ON tblPartMain.PartMainID_PK = [Inventory Transactions].ProductID) LEFT JOIN [Work Orders] ON tblPartMain.PartMainID_PK = [Work Orders].PartID) ON tblM16086Rel.PartMainID_FK = tblPartMain.PartMainID_PK
ORDER BY tblM16086Rel.DHROrder;
I have looked at this both in design view and SQL view until I am crosseyed I just can't seem to find out what I am missing. Something stupid I am sure.
Thanks
Dave