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;
 
Ad

Advertisements

Joined
Aug 3, 2015
Messages
2
Reaction score
0
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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top