MS Query Formatting Text to Number

[Pink Elephant]

Hi Everyone,

I'm trying to get a pivot table with a financial summary for our company's consolidated financial results. I'm having some minor issues which I was hoping to get some help with.

Background:
- The accounting information is in a database on our server, we have an Excel file to pull the data, and a few columns are added (Amount, Original Currency amount, Month) that don't exist in the transaction data.

- There are 7 companies with separate databases, and are being pulled into tables in their own tab.

- In a separate worksheet, I've done a query to combine all of the tables into 1 pivot table.

The Problem:
- Despite the fact that I'm pulling the information from the first Excel sheet, which has the amount and month columns in number and date format, when I add the sum of amounts to the consolidated pivot, it returns zero as the column is in text format.

Here is the SQL command I'm using to join the tables. Note that it's only 2 of the 7. I tried using SELECT * but it gave me an error that my columns weren't the same (I can't see why they aren't the same, so I just did it this way for now).

SELECT `'MRF Data$'`.`Posting Type`, `'MRF Data$'`.`Account Number`, `'MRF Data$'`.`Account Description`, `'MRF Data$'`.`Account Category`, `'MRF Data$'`.Dept, `'MRF Data$'`.Category, `'MRF Data$'`.Subcategory, `'MRF Data$'`.Grouping, `'MRF Data$'`.`TRX Date`, `'MRF Data$'`.`Journal Entry`, `'MRF Data$'`.Reference, `'MRF Data$'`.`Originating Master Name`, `'MRF Data$'`.`Originating Document Number`, `'MRF Data$'`.`Originating TRX Source`, `'MRF Data$'`.`Originating Journal Entry`, `'MRF Data$'`.`Debit Amount`, `'MRF Data$'`.`Credit Amount`, `'MRF Data$'`.`Originating Debit Amount`, `'MRF Data$'`.`Originating Credit Amount`, `'MRF Data$'`.`Originating Amount`, `'MRF Data$'`.Amount, `'MRF Data$'`.Month, `'MRF Data$'`.Company
FROM `'MRF Data$'` `'MRF Data$'`
UNION ALL
SELECT `'Tele Data$'`.`Posting Type`, `'Tele Data$'`.`Account Number`, `'Tele Data$'`.`Account Description`, `'Tele Data$'`.`Account Category`, `'Tele Data$'`.Dept, `'Tele Data$'`.Category, `'Tele Data$'`.Subcategory, `'Tele Data$'`.Grouping, `'Tele Data$'`.`TRX Date`, `'Tele Data$'`.`Journal Entry`, `'Tele Data$'`.Reference, `'Tele Data$'`.`Originating Master Name`, `'Tele Data$'`.`Originating Document Number`, `'Tele Data$'`.`Originating TRX Source`, `'Tele Data$'`.`Originating Journal Entry`, `'Tele Data$'`.`Debit Amount`, `'Tele Data$'`.`Credit Amount`, `'Tele Data$'`.`Originating Debit Amount`, `'Tele Data$'`.`Originating Credit Amount`, `'Tele Data$'`.`Originating Amount`, `'Tele Data$'`.Amount, `'Tele Data$'`.Month, `'Tele Data$'`.Company
FROM `'Tele Data$'` `'Tele Data$'`

I would like the "TRX Data" and the "Month" columns to be in date format, and all of the amount columns to be number format.

Thanks for any help in advance!

 

[Stubble Kid]

Initially, I'd try forcing MS Query to make an implicit conversion by adding zero to the end of each of the date or amount fields (assuming the date field in the original DB is a Date/Time number):

For example: `'Tele Data$'`.`Originating Amount` becomes `'Tele Data$'`.`Originating Amount`+0


Alternatively, you will have to find a function that will do the conversion explicitly, but may have to experiment with using an Excel, VBA/Access, or SQL function (I can't remember what type of functions MS Query prefers as I haven't used in for years).

For example an Excel function like: VALUE(`'Tele Data$'`.`Originating Amount`) or DATEVALUE(`'Tele Data$'`.`TRX Date`)

For example a VBA/Access function like CCUR(`'Tele Data$'`.`Originating Amount`) or CDATE(`'Tele Data$'`.`TRX Date`)


Try these suggestions on a couple of the fields before going to the trouble of amending all the fields over your whole union query.