Reference to more than one table

I

iamnu

I get the following error message when executing the code below:
The specified field '[AcctID]' could refer to more than one table
listed in the FROM clause of your SQL statement.

SELECT tblProvider.ProviderID, tblProvider.AcctID, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM zAcctNum INNER JOIN tblProvider ON zAcctNum.AcctID =
tblProvider.AcctID
ORDER BY tblProvider.AcctID;

How do I code this to fix this problem?

Thanks,
Bernie
 
A

Arvin Meyer [MVP]

The field AcctID, exists in both tables in the Join. You need to specify
which one is being selected in the DSum:

[AcctID] = " & [AcctID]))) AS AcctTotal

Needs to be:

[TableName].[AcctID] = " & [TableName].[AcctID]))) AS AcctTotal
 
I

iamnu

The field AcctID, exists in both tables in the Join. You need to specify
which one is being selected in the DSum:

[AcctID] = " & [AcctID]))) AS AcctTotal

Needs to be:

[TableName].[AcctID] = " & [TableName].[AcctID]))) AS AcctTotal
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com


I get the following error message when executing the code below:
The specified field '[AcctID]' could refer to more than one table
listed in the FROM clause of your SQL statement.
Click to expand...
SELECT tblProvider.ProviderID, tblProvider.AcctID, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM zAcctNum INNER JOIN tblProvider ON zAcctNum.AcctID =
tblProvider.AcctID
ORDER BY tblProvider.AcctID;
Click to expand...
How do I code this to fix this problem?
Click to expand...
Thanks,
Bernie
Click to expand...
Click to expand...

So I changed the code to this:

SELECT tblProvider.ProviderID, tblProvider.AcctID, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[tblProvider].[AcctID] = " &
[tblProvider].[AcctID]))) AS
AcctTotal
FROM zAcctNum INNER JOIN tblProvider ON zAcctNum.AcctID =
tblProvider.AcctID
ORDER BY tblProvider.AcctID;

and now, when viewing the results, AcctTotal displays #Error for each
value.

Can you help with this problem, also?

Bernie
 
D

Dale Fye

I'm not sure why you even need the zAcctNum table in the picture, unless you
are using this to "filter" the AcctIDs visible in tblProvider.

Instead of the way you have it, why not try:

SELECT tblProvider.ProviderID,
tblProvider.AcctID,
Sum(Nz(tblPayDetail.[BillPayAmt]))
FROM tblProvider
LEFT JOIN tblPayDetail
ON tblProvider.AcctID = tblPayDetail.AcctID
GROUP BY tblProvider.ProviderID, tblProvider.AcctID
ORDER BY tblProvider.AcctID

That should be faster than using the DSUM function. Then, if you still need
the zAcctNum table to "filter" the result set, add that table back into the
query and join it to tblProvider with an inner join.

HTH
Dale
 
I

iamnu

I'm not sure why you even need the zAcctNum table in the picture, unless you
are using this to "filter" the AcctIDs visible in tblProvider.

Instead of the way you have it, why not try:

SELECT tblProvider.ProviderID,
               tblProvider.AcctID,
              Sum(Nz(tblPayDetail.[BillPayAmt]))
FROM tblProvider
LEFT JOIN tblPayDetail
ON tblProvider.AcctID = tblPayDetail.AcctID
GROUP BY tblProvider.ProviderID, tblProvider.AcctID
ORDER BY tblProvider.AcctID

That should be faster than using the DSUM function.  Then, if you stillneed
the zAcctNum table to "filter" the result set, add that table back into the
query and join it to tblProvider with an inner join.

HTH
Dale


I get the following error message when executing the code below:
The specified field '[AcctID]' could refer to more than one table
listed in the FROM clause of your SQL statement.
Click to expand...
SELECT tblProvider.ProviderID, tblProvider.AcctID, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM zAcctNum INNER JOIN tblProvider ON zAcctNum.AcctID =
tblProvider.AcctID
ORDER BY tblProvider.AcctID;
Click to expand...
How do I code this to fix this problem?
Click to expand...
Thanks,
Bernie
Click to expand...
Click to expand...

Thanks for your help, Dale!

I created a query to do the INNER JOIN as you suggested, and now IT
WORKS as I want.

However, your suggestion to use Sum(Nz(tblPayDetail.[BillPayAmt]))
instead of DCount, I could NOT get to work. Since I don't know what
I'm doing, that's understandable.

The Error I get when using Sum(Nz(tblPayDetail.[BillPayAmt])) is:
"You tried to execute an a query that does not include the specified
expression 'ProviderID' as part of an aggregate function.

Maybe you could modify my new code below to show me how to use Sum(Nz
(tblPayDetail.[BillPayAmt])) instead of Dcount.

HERE IS MY NEW CODE THAT WORKS

SELECT [tblProvider Query].ProviderID, [tblProvider Query].AcctID,
[tblProvider Query].AcctNum, [tblProvider Query].PayeeID, [tblProvider
Query].PayeeName, [tblProvider Query].CoNameID, [tblProvider
Query].CoName, CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID]
= " & [AcctID]))) AS AcctTotal, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[PayeeID] = " & [PayeeID]))) AS
PayeeTotal, CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[CoNameID] =
" & [CoNameID]))) AS CoNameTotal
FROM [tblProvider Query]
ORDER BY [tblProvider Query].AcctNum, [tblProvider Query].PayeeName,
[tblProvider Query].CoName;

Thanks,
Bernie
 
D

Dale Fye

That is the error you generally get when you create an aggregate query and
fail to include one of the fields from the SELECT statement in the GROUP BY
statement.

Dale

I'm not sure why you even need the zAcctNum table in the picture, unless
you
are using this to "filter" the AcctIDs visible in tblProvider.

Instead of the way you have it, why not try:

SELECT tblProvider.ProviderID,
tblProvider.AcctID,
Sum(Nz(tblPayDetail.[BillPayAmt]))
FROM tblProvider
LEFT JOIN tblPayDetail
ON tblProvider.AcctID = tblPayDetail.AcctID
GROUP BY tblProvider.ProviderID, tblProvider.AcctID
ORDER BY tblProvider.AcctID

That should be faster than using the DSUM function. Then, if you still
need
the zAcctNum table to "filter" the result set, add that table back into
the
query and join it to tblProvider with an inner join.

HTH
Dale


I get the following error message when executing the code below:
The specified field '[AcctID]' could refer to more than one table
listed in the FROM clause of your SQL statement.
Click to expand...
SELECT tblProvider.ProviderID, tblProvider.AcctID, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM zAcctNum INNER JOIN tblProvider ON zAcctNum.AcctID =
tblProvider.AcctID
ORDER BY tblProvider.AcctID;
Click to expand...
How do I code this to fix this problem?
Click to expand...
Thanks,
Bernie
Click to expand...
Click to expand...

Thanks for your help, Dale!

I created a query to do the INNER JOIN as you suggested, and now IT
WORKS as I want.

However, your suggestion to use Sum(Nz(tblPayDetail.[BillPayAmt]))
instead of DCount, I could NOT get to work. Since I don't know what
I'm doing, that's understandable.

The Error I get when using Sum(Nz(tblPayDetail.[BillPayAmt])) is:
"You tried to execute an a query that does not include the specified
expression 'ProviderID' as part of an aggregate function.

Maybe you could modify my new code below to show me how to use Sum(Nz
(tblPayDetail.[BillPayAmt])) instead of Dcount.

HERE IS MY NEW CODE THAT WORKS

SELECT [tblProvider Query].ProviderID, [tblProvider Query].AcctID,
[tblProvider Query].AcctNum, [tblProvider Query].PayeeID, [tblProvider
Query].PayeeName, [tblProvider Query].CoNameID, [tblProvider
Query].CoName, CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID]
= " & [AcctID]))) AS AcctTotal, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[PayeeID] = " & [PayeeID]))) AS
PayeeTotal, CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[CoNameID] =
" & [CoNameID]))) AS CoNameTotal
FROM [tblProvider Query]
ORDER BY [tblProvider Query].AcctNum, [tblProvider Query].PayeeName,
[tblProvider Query].CoName;

Thanks,
Bernie
 

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

Similar Threads

DSum Not Working 5
Allen Browne's "Filtering on a field in the Subform" 3
specified field [equipment] could refer to more than one table listed in FROM clause of SQL statemen 1
Error Message: Could Refer to more than one table 3
specified field could refer to more than one table 6
Error: Specified field refers to more than one table 6
Feild could refer to more than one table? 2
more than one table listed in from clause of sql 3

Top