Make table query error

[standenman]

I am trying to make a table with this code:

SELECT NPIMonsterPared.NPI, NPIMonsterPared.[Entity Type Code], NPIMonsterPared.[Provider First Name], NPIMonsterPared.[Provider Middle Name], NPIMonsterPared.[Provider Last Name (Legal Name)], NPIMonsterPared.[Provider First Line Business Practice Location Address], NPIMonsterPared.[Provider Second Line Business Practice Location Address], NPIMonsterPared.[Provider Business Practice Location Address State Name], NPIMonsterPared.[Provider Business Mailing Address Postal Code], NPIMonsterPared.[Provider Business Mailing Address Postal Code], NPIMonsterPared.[Provider Business Mailing Address Telephone Number], NPIMonsterPared.[Provider Business Mailing Address Fax Number], NPIMonsterPared.[Provider Business Practice Location Address State Name] INTO DallasTXOrgaNPIs
FROM NPIMonsterPared
WHERE (((NPIMonsterPared.[Entity Type Code])=2) AND ((NPIMonsterPared.[Provider Business Practice Location Address City Name])="dallas") AND ((NPIMonsterPared.[Provider Business Practice Location Address State Name])="TX"));

When I try to run the query I get this non-sensical response from Access:

 

[June7]

I can't see anything wrong with SQL. Why create table with simply filtered recordset?

Are you pulling every field from NPIMonsterPared?

 

[CJ_London]

very poor naming practice which may be contributing to the problem. Try removing all the spaces and non alphanumeric characters

 

[standenman]

The names come from the excel spreadseet that I downloaded. Access is such a fussy little...

 

[standenman]

When I try to rename this field to "ProviderBusinessMailingState" MS access says that it does not follow Access object-naming rules.

 

[June7]

This is a linked worksheet? Does it open in datasheet view?

Built SQL with designer and tried to run there?

 

[plog]

SELECT NPIMonsterPared.NPI, NPIMonsterPared.[Entity Type Code]
, NPIMonsterPared.[Provider First Name]
, NPIMonsterPared.[Provider Middle Name]
, NPIMonsterPared.[Provider Last Name (Legal Name)]
, NPIMonsterPared.[Provider First Line Business Practice Location Address]
, NPIMonsterPared.[Provider Second Line Business Practice Location Address]
, NPIMonsterPared.[Provider Business Practice Location Address State Name]
, NPIMonsterPared.[Provider Business Mailing Address Postal Code]
, NPIMonsterPared.[Provider Business Mailing Address Postal Code]
, NPIMonsterPared.[Provider Business Mailing Address Telephone Number]
, NPIMonsterPared.[Provider Business Mailing Address Fax Number]
, NPIMonsterPared.[Provider Business Practice Location Address State Name]

Not 100% sure, but perhaps your duplicated fields in the SELECT are causing this. Clean those up and try again.

 

[June7]

Dang! Good eye, plog.

If this had been built with designer, I would have expected alias names for those dupes.

 

[The_Doc_Man]

The error message says you have named something NPIMonsterPared_Provider Business Practice Location Address State Name when it is clear that the underscore SHOULD be a dot. So this error is telling you that you have a typo involving that field. Underscores are permitting in field names so that typo just looks like a big, long, ugly field name. If that is actually what happened, then another problem is that you have 70 bytes in a field name, and I think the limit is shorter than that. If true, then the non-sensical response derives from a non-sensical field name.

Access WILL handle this correctly if you get the punctuation right, but these fields are suspicious. It ALMOST seems as though you are somehow combining what should be individual short fields names into one long field name. I almost don't want to know what it really represents, but I have to say that your naming convention probably could use some revisitation.

 

[June7]

Doc, the SQL doesn't show an underscore anywhere. Access won't allow a field name to exceed 64 characters in table design. However, it does allow in query in Design View. Creating alias field name over 64 characters in SQL View fails. SQL View will show field name over 64 characters when created in Design View but editing it will trigger error if it is still over 64 when leaving SQL View.

 

[CJ_London]

The names come from the excel spreadseet that I downloaded. Access is such a fussy little...

downloaded means what exactly? you linked to the excel spreadsheet or you imported it? in either case using transferspreadsheet or sql?

 

[The_Doc_Man]

Doc, the SQL doesn't show an underscore anywhere.

But the error message DOES. Was the query shown to us derived from a copy-paste or a re-enter by hand?

 

[Pat Hartman]

Access is such a fussy little...

It is actually not fussy enough. In a "good" language, there should be only one way to do anything. VBA is actually too loosey-goosey. Excel is not a relational database so it caters to people who know nothing about anything.

The first thing you should do is to get rid of these ridiculously long names. Do it either by importing the data into a table with rational names or by using a query to alias the long names.