I'm tracking down a minor inconvenience with Excel field formatting in a query that includes multiple linked Excel spreadsheets. The query produces field formatting for some of the numeric fields that are numbers stored as text.


The overall flow starts with a few dozen individual Excel files that contain accounting transactions. The fields, and their order, are the same in all the Excel files. Routine downloads of financial transactions are stored in the individual files, which use a few lookup tables that are set up for Data Validation, presenting a list of choices for things like Accounts and Categories.


Every one of the individual Excel transaction files are links in the Access data base, so the content is just whats in the individual files. My assumption at this point is that field formatting is whats in the linked Excel files. The tables with the lookup values are also linked to Access.



The next step in the flow are three simple UNION SELECT * queries (there are multiple because there seems to be a limit in the number of SELECTs in a UNION). There's a final UNION selects the three UNION SELECT * queries to produce a query with all the transactions in all the linked spreadsheets.


Next is a QUERY that uses the final UNION, along with a the lookup tables (Account and Category, along with a couple others) to produce a full list of all the transactions with all the lookup date included in the records.


This QUERY is linked to three report Excel spreadsheets that are used to create reports using functions like INDEX/MATCH and SUMPRODUCT against the database from the Access QUERY. (There's also one that doesn't have any reports and is used just to do simple sorts and other analysis of the transactions without the processing delay as Excel calculates the functions).


What I'm seeing is a few of the data fields in the Excel report files are formatted differently than what was in the original transaction files.


I'm going through each of the transaction tables to confirm that each has the same field formatting, but am curious how Access is deciding what field formatting to use. Since all of the data in a particular field, regardless of what transaction file they came from, is formatted the same, and since I know for sure that at least some of the individual transaction files are formatted correctly, it appears Access is making a formatting decision in either the UNION or the final QUERY.


At first I thought it might be that Access is deciding based on the very first record it sees in the QUERY, but I've confirmed that, for all the UNIONs, the first individual file in each is formatted correctly.


How does Access decide in this architecture how to format individual fields?