Alias expressions don't update in query after using Linked Table Manager...

[cpowell100]

I have two linked tables based on Excel 2000 files with a large number
of rows (about 25,000) – each Excel file has the same layout, fields,
etc., but the Excel file names change every month. When the files
change, I use Linked Table Manager, create links to the new pair of
files ("NewTable1" and NewTable2"), and Access 2000 successfully
refreshes the links to my new files. I created a select query that uses
both tables, and any single field columns in the query grid properly
reflect the field names from the new source tables.



However, when I use alias expressions to compare three numeric fields
between the two months, I want it to show DiffDollars: [NewTable1!Dollars]-
[NewTable2!Dollars], DiffAmount [NewTable1!Amount]-[NewTable2!Amount],
and DiffHours [NewTable1!Hours]-[NewTable2!Hours]. Instead, the Field
row in the query grid shows the following for DiffAmount: "DiffAmount:
[OldTable1!Amount]-[OldTable2!Amount]. In other words, the Select Query
picks up the respective single field names from the two new tables fine,
however, any alias expressions relying on multiple fields reflect the
old field names (based on the old versions of the tables).



How can I get Access to reflect the correct field names for alias
expressions in the query grid, or how can I use SQL to get the query
working properly?



Thanks in advance, Chris

 

[[MVP] S. Clark]

I think the following syntax should be used to refer to ambiguous column
names:

[TableName].[Fieldname]
[NewTable1].[Dollars]

The ! operator is typically used to refer to user-defined controls on a form
or report.
Forms!Formname!ControlName

You may be confusing Access with this syntax... or something??

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-