specified field [equipment] could refer to more than one table listed in FROM clause of SQL statemen

Joined
Aug 3, 2015
Messages
2
Reaction score
0
SELECT [Inventory Table].*, [Inventory Table].Part, [Equipment List Table].Equipment, Nz(DSum("[Quantity]","[Return Table]","[Part]='" & [Forms]![Inventory Form].[Part Text] & "'"))-Nz(DSum("[Quantity]","[Requisition Table]","[Part]='" & [Forms]![Inventory Form].[Part Text] & "'"))+Nz(DSum("[Quantity]","[Add Stock Table]","[Part]='" & [Forms]![Inventory Form].[Part Text] & "'"))+Nz(DSum("[Quantity]","[Adjustment Table]","[Part]='" & [Forms]![Inventory Form].[Part Text] & "'"))+Nz(DSum("[Quantity on Hand]","[Inventory Table]","[Part]='" & [Forms]![Inventory Form].[Part Text] & "'")) AS [Current]
FROM [Equipment List Table] INNER JOIN ([Inventory Table] INNER JOIN [Equipment Parts Table] ON [Inventory Table].Part = [Equipment Parts Table].Part) ON [Equipment List Table].Equipment = [Equipment Parts Table].Equipment
ORDER BY [Inventory Table].Part;
 
Code:
SELECT [Inventory Table].*, [Inventory Table].Part, [Equipment List Table].Equipment, [Equipment List Table].Name, Nz(DSum("[Quantity]","[Return Table]","[Part]='" & [Inventory Table].Part & "'"))-Nz(DSum("[Quantity]","[Requisition Table]","[Part]='" & [Inventory Table].Part & "'"))+Nz(DSum("[Quantity]","[Add Stock Table]","[Part]='" & [Inventory Table].Part & "'"))+Nz(DSum("[Quantity]","[Adjustment Table]","[Part]='" & [Inventory Table].Part & "'"))+Nz(DSum("[Start]","[Inventory Table]","[Part]='" & [Inventory Table].Part & "'")) AS [Current]
FROM [Equipment List Table] INNER JOIN ([Inventory Table] INNER JOIN [Equipment Parts Table] ON [Inventory Table].Part=[Equipment Parts Table].Part) ON [Equipment List Table].Equipment=[Equipment Parts Table].Equipment
ORDER BY [Inventory Table].Part;
 
Back
Top