Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-01-2020, 09:42 AM
junkcarp junkcarp is offline How does an Access query of linked Excel tables decide on field formatting? Windows 10 How does an Access query of linked Excel tables decide on field formatting? Office 2016
Novice
How does an Access query of linked Excel tables decide on field formatting?
 
Join Date: Oct 2019
Posts: 9
junkcarp is on a distinguished road
Default How does an Access query of linked Excel tables decide on field formatting?

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?
Reply With Quote
  #2  
Old 09-01-2020, 02:43 PM
junkcarp junkcarp is offline How does an Access query of linked Excel tables decide on field formatting? Windows 10 How does an Access query of linked Excel tables decide on field formatting? Office 2016
Novice
How does an Access query of linked Excel tables decide on field formatting?
 
Join Date: Oct 2019
Posts: 9
junkcarp is on a distinguished road
Default Status

BTW, just realized that the question is about Access field formatting, so, if I should move, just suggest.


Excel does bookend Access in the design. The front end are all the individual Excel files with the transaction data, and the back end is the analysis and reporting. Access does the work to combining the data, while adding fields that contain reconciled data from a few lookup tables.


The resultant table is linked to from Excel files that use Excel functions to analyze and report the data.


Staying in Access, I have noticed some differences in the sequence of records from the unions. Although the intent was to have the select records in the unions in alphabetical order by file name, in at least one of the unions the select statement sequence is different than the file name order.


It's as if the unions are sorting the select statements into file name order before doing the actual extract.



Will test some more.
Reply With Quote
Reply

Tags
access, field formatting, query

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How does an Access query of linked Excel tables decide on field formatting? Formatting linked MS Excel tables in MS Word PWindsor Word 3 02-17-2019 01:34 PM
Excel ADO query from Access Not Working bconner Excel Programming 0 03-22-2017 09:29 AM
Using MS Access Query and VBA to create Excel Chart jrmontgom Excel Programming 0 04-07-2015 11:21 AM
Problem With Updating Linked Excel Tables in Word 2013 maverick1714 Word 6 09-30-2014 12:30 AM
mail merge from access query or two tables peter Mail Merge 0 08-24-2010 05:32 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft