Apparently Bogus "could refer to more than one.

[Phil Smith]

I have a query that was based on a table. I recreated the structure of
that table as a query, gave it that tables name. So now, without
changing it, I now have a query based on a query.

Now, If I try to edit that query, it refuses to open it in the Query
grid, producing the following error:

The specified field '[customer_id]' could refer to more thatn one table
listed in the FROMclause of your SQL statement.

Now, maybe I am blind, but every single reference I can see to
'[customer_id]' is preferenced with the table name.

Any Ideas?

SELECT [Report Card ItemType Padding].customer_id, [Report Card ItemType
Padding].item_types_name, Sum(IIf([Unionized Data
Table]!invoice_date>=Forms![Report Card Form]!Start And [Unionized Data
Table]!invoice_date<=Forms![Report Card Form]!End,[Unionized Data
Table]!Ext_Price,0)) AS Period1, Sum(IIf([Unionized Data
Table]!invoice_date>=Forms![Report Card Form]!SecStart And [Unionized
Data Table]!invoice_date<=Forms![Report Card Form]!SecStop,[Unionized
Data Table]!Ext_Price,0)) AS [Period 2], CDbl([Report Card ItemType
Padding]!customer_id) AS Customer_no
FROM [Report Card ItemType Padding] LEFT JOIN [Unionized Data Table] ON
([Report Card ItemType Padding].item_types_name=[Unionized Data
Table].item_types_name) AND ([Report Card ItemType
Padding].customer_id=[Unionized Data Table].customer_id)
GROUP BY [Report Card ItemType Padding].customer_id, [Report Card
ItemType Padding].item_types_name, CDbl([Report Card ItemType
Padding]!customer_id)
HAVING ((([Report Card ItemType Padding].customer_id)=Forms![Report Card
Form]!CustomerCombo))
ORDER BY [Report Card ItemType Padding].customer_id, [Report Card
ItemType Padding].item_types_name;

 

[mscertified]

If you can save the SQL and run it, I would not worry about it. It probably
does not like the AND in the LEFT JOIN.

-Dorian

 

[Phil Smith]

I get the same error when I attempt to run it.

Without that AND, assuming it is translating the joins I think it is, I
woudl not get the proper results, and woudl get about threee times as
many records as I want.

If you can save the SQL and run it, I would not worry about it. It probably
does not like the AND in the LEFT JOIN.

-Dorian

:

I have a query that was based on a table. I recreated the structure of
that table as a query, gave it that tables name. So now, without
changing it, I now have a query based on a query.

Now, If I try to edit that query, it refuses to open it in the Query
grid, producing the following error:

The specified field '[customer_id]' could refer to more thatn one table
listed in the FROMclause of your SQL statement.

Now, maybe I am blind, but every single reference I can see to
'[customer_id]' is preferenced with the table name.

Any Ideas?

SELECT [Report Card ItemType Padding].customer_id, [Report Card ItemType
Padding].item_types_name, Sum(IIf([Unionized Data
Table]!invoice_date>=Forms![Report Card Form]!Start And [Unionized Data
Table]!invoice_date<=Forms![Report Card Form]!End,[Unionized Data
Table]!Ext_Price,0)) AS Period1, Sum(IIf([Unionized Data
Table]!invoice_date>=Forms![Report Card Form]!SecStart And [Unionized
Data Table]!invoice_date<=Forms![Report Card Form]!SecStop,[Unionized
Data Table]!Ext_Price,0)) AS [Period 2], CDbl([Report Card ItemType
Padding]!customer_id) AS Customer_no
FROM [Report Card ItemType Padding] LEFT JOIN [Unionized Data Table] ON
([Report Card ItemType Padding].item_types_name=[Unionized Data
Table].item_types_name) AND ([Report Card ItemType
Padding].customer_id=[Unionized Data Table].customer_id)
GROUP BY [Report Card ItemType Padding].customer_id, [Report Card
ItemType Padding].item_types_name, CDbl([Report Card ItemType
Padding]!customer_id)
HAVING ((([Report Card ItemType Padding].customer_id)=Forms![Report Card
Form]!CustomerCombo))
ORDER BY [Report Card ItemType Padding].customer_id, [Report Card
ItemType Padding].item_types_name;

 

[Allen Browne]

Access won't let you have a table and a query with the same name, so
presumably you either deleted or renamed the original table. And now it
seems that Access is confused about what you are referring to here.

Suggestion sequence to fix it:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Recreate the query:
Open the query in SQL View.
Copy the query statement to clipboard (Ctrl+C.)
Close the query, and delete it.
Compact again.
Create a new query.
Switch to SQL View, and paste the query statement in (Ctrl+V.)

 

[Phil Smith]

I did rename the table.
I did all of things you suggested, in the order you suggested. It still
fails when I go ro run it or put it in Design view.

 

[Allen Browne]

Ah: it fails to go into design view as well?

That would suggest there is something wrong with the SQL statement itself.

 

[Phil Smith]

It started out in design view, as perfectly functional. I replaced the
underlying table with a query, got that error the forst time I tried to
open it in design view, and was thrust into SQL view. Running or design
view does not work, giving that error. I can't see anything wrong with
the query, can you?

 

[Allen Browne]

Hard to say, Phil. The query is clearly based on other queries, so there
could be some kind of mismatch going on (e.g. data type of calculated field
not working correctly.)

The only thing that's really obvious is that the CDbl() will error if
customer_id is null. It may help to put square brackets around End, since I
believe it is a reserved word. The list of names to avoid is here:
http://allenbrowne.com/AppIssueBadWord.html#E

(Personally, I would be tempted to use a WHERE clause rather than a HAVING
clause here, and to explicitly declare the parameters so their data type is
defined.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

It started out in design view, as perfectly functional. I replaced the
underlying table with a query, got that error the forst time I tried to
open it in design view, and was thrust into SQL view. Running or design
view does not work, giving that error. I can't see anything wrong with
the query, can you?

SELECT [Report Card ItemType Padding].customer_id,
[Report Card ItemType Padding].item_types_name,
Sum(IIf([Unionized Data Table]!invoice_date >=
Forms![Report Card Form]!Start
And [Unionized Data Table]!invoice_date <=
Forms![Report Card Form]!End,
[Unionized Data Table]!Ext_Price, 0)) AS Period1,
Sum(IIf([Unionized Data Table]!invoice_date >=
Forms![Report Card Form]!SecStart
And [Unionized Data Table]!invoice_date <=
Forms![Report Card Form]!SecStop,
[Unionized Data Table]!Ext_Price, 0)) AS [Period 2],
CDbl([Report Card ItemType Padding]!customer_id) AS Customer_no
FROM [Report Card ItemType Padding]
LEFT JOIN [Unionized Data Table]
ON ([Report Card ItemType Padding].item_types_name =
[Unionized Data Table].item_types_name)
AND ([Report Card ItemType Padding].customer_id =
[Unionized Data Table].customer_id)
GROUP BY [Report Card ItemType Padding].customer_id,
[Report Card ItemType Padding].item_types_name,
CDbl([Report Card ItemType Padding]!customer_id)
HAVING ((([Report Card ItemType Padding].customer_id) =
Forms![Report Card Form]!CustomerCombo))
ORDER BY [Report Card ItemType Padding].customer_id,
[Report Card ItemType Padding].item_types_name;

 

[George Nicholson]

Can you successfully open the underlying queries in design view?

 

[David W. Fenton]

Now, maybe I am blind, but every single reference I can see to
'[customer_id]' is preferenced with the table name.

You can't use ! in SQL. You should use . instead. So, where you have:

CDbl([Report Card ItemType Padding]!Customer_id) AS Customer_no

you should have:

CDbl([Report Card ItemType Padding].Customer_id) AS Customer_no

The ! operator works only for form and report items (controls on a
form/report or fields in the recordsource of the form/report) or in
recordsets. In SQL, the . is used to specify the parent of a field.
So, your SQL should read:

SELECT [Report Card ItemType Padding].Customer_id,
[Report Card ItemType Padding].item_types_name,
Sum(IIf([Unionized Data Table].invoice_date

=Forms![Report Card Form]!Start

And [Unionized Data Table].invoice_date
<=Forms![Report Card Form]!End,
[Unionized Data Table].Ext_Price,
0)) AS Period1,
Sum(IIf([Unionized Data Table].invoice_date

=Forms![Report Card Form]!SecStart

And [Unionized Data Table].invoice_date
<=Forms![Report Card Form]!SecStop,
[Unionized Data Table].Ext_Price,
0)) AS [Period 2],
CDbl([Report Card ItemType Padding].Customer_id) AS Customer_no
FROM [Report Card ItemType Padding] As A
LEFT JOIN [Unionized Data Table] As B
ON ([Report Card ItemType Padding].item_types_name=
[Unionized Data Table].item_types_name)
AND ([Report Card ItemType Padding].Customer_id=
[Unionized Data Table].Customer_id)
GROUP BY [Report Card ItemType Padding].Customer_id,
[Report Card ItemType Padding].item_types_name,
CDbl(A!Customer_id)
HAVING ((([Report Card ItemType Padding].Customer_id)=
Forms![Report Card Form]!CustomerCombo))
ORDER BY [Report Card ItemType Padding].Customer_id,
[Report Card ItemType Padding].item_types_name;

I'd also recommend getting rid of spaces in the names of tables,
fields, forms and reports. And you can make your SQL more readable
with table aliases. I reworked your SQL to be this in order that I
could understand it:

SELECT A.Customer_id, A.item_types_name,
Sum(IIf(B.invoice_date>=Forms![Report Card Form]!Start
And B.invoice_date<=Forms![Report Card Form]!End,
B.Ext_Price,
0)) AS Period1,
Sum(IIf(B.invoice_date>=Forms![Report Card Form]!SecStart
And B.invoice_date<=Forms![Report Card Form]!SecStop,
B.Ext_Price,
0)) AS [Period 2],
CDbl(A.Customer_id) AS Customer_no
FROM [Report Card ItemType Padding] As A
LEFT JOIN [Unionized Data Table] As B
ON (A.item_types_name=B.item_types_name)
AND (A.Customer_id=B.Customer_id)
GROUP BY A.Customer_id, A.item_types_name, CDbl(A!Customer_id)
HAVING (((A.Customer_id)=Forms![Report Card Form]!CustomerCombo))
ORDER BY A.Customer_id, A.item_types_name;

And, of course, as someone suggested, your HAVING clause should
probably be a WHERE clause, instead. For years, I didn't know that
you could have a WHERE clause in GROUP BY queries because I never
scrolled down the dropdown list of options to see that WHERE was one
of the options for the column where I was placing the criteria.