Access Changing Query Field Name to Expr1035

[seanfleming10]

Hello,

I've got an Access Database setup with multiple tables. In my master query, I've specified the criteria I'd like to see. When I run the query in Access, everything returns fine. However, when I connect to the database from excel, my query field - "GPS Dept #" is renamed to Expr1035. How do I prevent this from happending. My SQL is shown below.

Thanks,
Sean






SELECT [COE Hierarchy].[Cost Center #], [COE Hierarchy].[Cost Center Name], [COE Hierarchy].[Profit Center], [COE Hierarchy].[Profit Center Name], [COE Hierarchy].[L2 Description], [COE Hierarchy].[L2 Leader], [COE Hierarchy].[L3 Description], [COE Data].[PY Budget], [COE Hierarchy].[L3 Leader], [COE Hierarchy].[L4 Description], [COE Hierarchy].[L4 Leader], [COE Hierarchy].System, [COE Hierarchy].Country, [COE Hierarchy].Company, [COE Data].Period, [COE Hierarchy].COE, [COE Data].[Cost Element #], [Category Rollup].[Cost Element Name], [Category Rollup].[Category Group], [Category Rollup].[RE Category], [Category Rollup].[RE Sort Order], [COE Data].Actuals, [Category Rollup].[Slide Category], [Category Rollup].[Slide Category Sort Order], [COE Hierarchy].[Finance Partner], Quarter.Quarter, [COE Data].[Planning Version], [COE Data].Budget, [COE Data].[PY Actuals], OCM.[OCM Category], OCM.[OCM Sub Category], [COE Hierarchy].[New Org], [COE Hierarchy].[L5 Description], [COE Data].[RE Prior], [COE Data].[RE Current], [COE Hierarchy].[GPS Dept #],[COE Hierarchy].[FDW - L1], [COE Hierarchy].[FDW - L2], [COE Hierarchy].[FDW - L3], [COE Hierarchy].[FDW - L4], [COE Hierarchy].[FDW - L5], [COE Hierarchy].[FDW - L6], [COE Hierarchy].[FDW - L7], [Category Rollup].[RE Agg RLI], [Category Rollup].[RLI Level 5], [Category Rollup].[RLI Level 6], [Category Rollup].[RLI Level 7], [COE Hierarchy].[GPS Dept #]FROM (OCM RIGHT JOIN ([COE Hierarchy] RIGHT JOIN ([Category Rollup] RIGHT JOIN [COE Data] ON [Category Rollup].[Cost Element] = [COE Data].[Cost Element #]) ON [COE Hierarchy].[Unique ID] = [COE Data].[Unique ID]) ON OCM.Account = [COE Data].[Cost Element #]) LEFT JOIN Quarter ON [COE Data].Period = Quarter.Period;

[Bob Fitz]

Not sure but perhaps this is because you have used "#​" in a field name.

[pbaldy]

I think because you have the field in the SELECT clause twice, Access will alias the second instance of it.

[JoeM]

Why are you returning the same field twice in your Select clause ([COE Hierarchy].[GPS Dept #])?
When you do that without using an alias, I believe it gives the second one a generic name like that.

Then way to get around that is to give each its own distinct alias, i.e.

SELECT ...,[COE Hierarchy].[GPS Dept #] as GPS_Dept1,..., [COE Hierarchy].[GPS Dept #] as GPS_Dept2 FROM ...

[ssanfu]

And it would help if you got rid of the spaces and special characters (examples: [Cost Element #] and [FDW - L5]) in your object names.....

[seanfleming10]

That was it. I built the query in design view and didn't realize the duplication. Thanks for the SQL around Alias - very helpful.