Access query read from Excel doesn't show the same values (1 Viewer)

[Dylan41]

In Excel I linked an Access query but some values are not shown correctly. Imagine I have TableA with these values:
Description | Value1 | Value2 | Value3 | Quantity
Bricks | 12.32 | 1.22 | 5.03 | 2
Wood | 12.22 | 1.1 | 5.55 | 1
Steel | 12.1 | 2.2 | 5.12 | 3

In a query, I have:
SELECT TableA.[Description], TableA.[Value1], TableA.[Value2], TableA.[Value3], TableA.[Value1]+TableA.[Value2]+TableA.[Value3] AS SumOfValues
FROM TableA

Then, in Access I have:
Description | Value1 | Value2 | Value3 | SumOfValues
Bricks | 12.32 | 1.22 | 5.03 | 18.57
Wood | 12.22 | 1.1 | 5.55 | 18.87
Steel | 12.1 | 2.2 | 5.12 | 19.42

But when I link that query from Excel, in Excel I have:

Description | Value1 | Value2 | Value3 | SumOfValues
Bricks | 12.32 | 1.22 | 5.03 | 18.57
Wood | 12.22 | 1.1 | 5.55 | 28.77
Steel | 12.1 | 2.2 | 5.12 | 148.12

i.e, the second row is calculated as 12.22+11+5.55 and the third row is calculated as 121+22+5.12

I tried to put 2 decimals in each value, but I couldn't. I tried Format([..], "Fixed") but it concatenates the values, it doesn't make a sum

What can I do?

Thanks

 

[The_Doc_Man]

What are the data types on the fields in their original Access tables?

 

[Dylan41]

General in all of them. I tried using numeric and 2 decimals but it didn't work

 

[Galaxiom]

General in all of them. I tried using numeric and 2 decimals but it didn't work

There is no "General" datatype in Access.

I tried Format([..], "Fixed") but it concatenates the values, it doesn't make a sum

This is because Format returns a string.

 

[Dylan41]

There is no "General" datatype in Access.

This is because Format returns a string.

I mean, in Property Sheet it says:
Description:
Format:
Decimal places:
Input mask:
Caption:


All is blank

 

[Galaxiom]

I wonder if you are using Decimal datatype and the problem is a variant of this issue.

Decimal value is rounded up/down to integer value - Microsoft 365 Apps

Describes a problem that occurs when you enter a decimal value in a column in Access 2007. The decimal value is rounded up or down to an integer value.

docs.microsoft.com

Decimal stores numbers as scaled integers and it might be getting the scale wrong because of the first entry which has two decimal places.

 

[arnelgp]

Create a Query (qryMasonry) in Access:

SELECT tblMasonry.Description,
    tblMasonry.Value1,
    tblMasonry.Value2,
    tblMasonry.Value3,
    [Value1]+[Value2]+[Value3] AS SumOfValues
FROM tblMasonry;

Link (Data->Get Data->From Database) the qryMasonry to Excel.

 

[Dylan41]

Create a Query (qryMasonry) in Access:

SELECT tblMasonry.Description,
    tblMasonry.Value1,
    tblMasonry.Value2,
    tblMasonry.Value3,
    [Value1]+[Value2]+[Value3] AS SumOfValues
FROM tblMasonry;

Link (Data->Get Data->From Database) the qryMasonry to Excel.

That's what I did. I called it from Excel (Data -> Get External Data -> From Access). Then, I go to the table and select Data -> Properties. And, if the query is called qryMasonry, I write SELECT * FROM `qryMasonry`.

Is that OK?

 

[Pat Hartman]

Access is not seeing the data type in the linked spreadsheet correctly. Try fixing the spreadsheet. Do NOT use general as the data type. Use the specific numeric data type. That may still not work. If it doesn't, you're going to have to format the columns in a query to coerce Access into using the correct data type so you can sum the columns.

BTW, Access doesn't have a function to sum across columns (and neither does any other relational database) because that function makes no sense in a properly normalized table. You would always be summing the values in a single column across all rows.

 

[June7]

I cannot replicate this issue.

Don't enclose qryMasonry in apostrophes. Could just simply reference the query name, no need for SELECT statement.