I try and open a form and it gives the error given below

[sschmidt460]

The specified field [Equipment] could refer to more than one table listed in the FROM clause of your SQL statement

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;

[Rabbit]

Please use code tags when posting code or formatted data.

You have more than one field with the same name. When you try to reference one of them, it's not going to know which one you mean to use. In this case, you are returning 2 fields with the name Equipment. And when you try to reference that field in the form, it doesn't know which of the two fields you are talking about. Also, you're returning the field Part twice.

As a side note, you should avoid domain aggregate functions when possible, they tend to be poor performers.

[sschmidt460]

Shouldnt it know which field i am referencing since i have specified using [table_name].equipment?

[sschmidt460]

and also I am setting them "=" to eachother allowing the reference to be the same?

[jforbes]

This:

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

Probably should be more like this:

FROM [Equipment List Table] 
INNER JOIN ([Inventory Table] 
ON [Inventory Table].Part = [Equipment Parts Table].Part) 
INNER JOIN [Equipment Parts Table] 
ON [Equipment List Table].Equipment = [Equipment Parts Table].Equipment

[sschmidt460]

I changed the code to jforbes suggestion, but there is now a "syntax error in the JOIN operation"

[Rabbit]

It's going to know in the query, yes. But it's not going to know in your form. Because the form control isn't set with the table name referenced.

Aside from the fact that you shouldn't have two things in a query with the same name, why would you want duplicate columns anyways? They contain the same data, it creates confusion, and it makes things run a little slower.

[jforbes]

Yep, there is a typo, I didn't pay much attention to it the first time. Just assumed they were correct:

FROM [Equipment List Table] 
INNER JOIN ([Inventory Table] 
ON [Inventory Table].Part = [Equipment List Table].Part) 
INNER JOIN [Equipment Parts Table] 
ON [Equipment List Table].Equipment = [Equipment Parts Table].Equipment

Rabbit is right, you will probably want to specify which (unique) columns to use from which tables and leave off the duplicates. Things can get really messy using the Star... [Inventory Table].*

[sschmidt460]

SELECT [Inventory Table].*, [Inventory Table].Part, 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], [Equipment Parts Table].Equipment
FROM [Inventory Table] INNER JOIN [Equipment Parts Table] ON [Inventory Table].Part = [Equipment Parts Table].Part;

[sschmidt460]

I revised it to this code to have only 1 field name "equipment" and the error remains the same. In the query I have gotten rid of one of the tables that contained the duplicate, but I still get the error. I dont understand how this can still be happening

[Rabbit]

Your inventory table probably has a field named Equipment. You didn't have to remove the join by the way. It's not the join that's causing the problem. It's the fact that you're returning fields with the same name. You just need to give it an alias if you need both fields returned. Otherwise, just leave it out of the select clause.