Originally posted in Excel, but think it fits here instead.
I have Access combining data records from a bunch of Excel files into a full table, adding fields containing data from a couple lookup tables. That query is linked to in a couple Excel files where the table data is used to for analysis and reporting, mainly using Excel functions like Index/Match and Sumproduct.
Some of the field formatting in the final query doesn't seem to match whats in the Excel transaction files. Specifically, some of the fields containing numeric data are shown as numbers stored as text. I'm assuming Access tables use the field formatting in the linked Excel files, so somewhere between the tables and the analysis and reporting Excel files, some field formatting is changing.
All the Access tables are linked Excel files, and include the transaction data tables, a few tables containing lookup data, and a table that has some constants used to control the date range of the final query.
There are five queries, four of which are Unions. I think there are limits on the number of Select statements that can be in a single Union, so there are three that collectively include all the tables, and one that is the union of those three.
The final query simple selects every record from the last Union, and adds field data from the lookups.
There's no field formatting or any data changes in Access. The only field use, other than just inclusion, is a date range selection and a single field sort in the final query.
I have pretty high confidence that the linked Excel files are all formatted correctly, but haven't yet ruled that out completely. I did validate the files listed first in each of the three unions, assuming that queries might decide on field formatting based on the format of the first record they encounter.
I've been looking at the first record encountered by each of the three Unions that collectively select all the linked Excel tables to see the actual field data. Again, I'm assuming Access queries might do field formatting based on the format of field data in the first record.
Am I on the right path? Assuming there is no explicit field formatting in Access in any of the tables or queries, does Access decide field formatting for linked Excel tables based on the formatting of the fields in the Excel files? Do queries decide on field formatting based on the data in the first record encountered?
I have encountered some differences in the order that the Unions are extracting the data, so have been sidetracked a little figuring things out. What I expected was that records from the query would be in the same order as the Select statements in each Union, but one of the Unions didn't sequence that way. For ease of maintenance, my practice had been to order the Union Select statements in the alphabetically by file name, but I noticed that wasn't the case in one of the Unions which appeared as if it sorted the Selects into file alphabetic order before executing the query. I'll do some more on this today, but it left some doubt about what data record the Union is actually first encountering.
Tom