UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Different Values From An Access Query, Office 2013    
 
   
guilhermegg
post Mar 20 2017, 06:50 PM
Post#1



Posts: 12
Joined: 18-November 16



Hello Guys,
I've a query in Access that shows the following result:

Date Product Total
13/03 Apple 30
13/03 Banana 35
13/03 Strawberry 25

That's the correct value.

However, when I try to connect this same query into Excel, here's what I got.

Date Product Total
13/03 Apple 30
13/03 Banana 36
13/03 Strawberry 27

Excel arbitrarily changes the value of two records.
Why does it happen?

Thanks in advance.

Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 07:03 PM
Post#2


UA Admin
Posts: 28,908
Joined: 20-June 02
From: Newcastle, WA


Are you really counting types of fruit? Often, "simplifying" by aliasing terms is an additional layer of abstraction that gets in the way.

We need to see three things to be able to offer suggestions:

The SQL from the query.

The values in the Access table.

The values in the Excel worksheet.



--------------------
Go to the top of the page
 
guilhermegg
post Mar 20 2017, 07:21 PM
Post#3



Posts: 12
Joined: 18-November 16



Hi @GroverParkGeorge!
No, I'm not couting types of fruit. I'm from Brazil so my query has some fields in portuguese. I just came up with an example that is similar to what my query does for easier understanding. My real query counts the number of times a subject was registered in a system used by some workers.

However, that's not the point here.
I'm getting exactly what I want from my Access Query.

These are the values in Access Query;
Date Product Total
13/03 Apple 30
13/03 Banana 35
13/03 Strawberry 25

The problem happens when I try to connect this query to Excel, the values in the Excel worksheet turned out to this:

Date Product Total
13/03 Apple 30
13/03 Banana 36
13/03 Strawberry 27

Thanks.


Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 07:27 PM
Post#4


UA Admin
Posts: 28,908
Joined: 20-June 02
From: Newcastle, WA


I understand that. My point is that we often see attempts to "simplify" that actually make it more difficult to communicate.

We need to see three things:

The SQL from the query.

The values in your Access table that produce the correct results.

The values in your Excel worksheet that do not produce the correct results.

Are you linking to the Excel sheet and using that linked table with your Access query? Or....?

--------------------
Go to the top of the page
 
guilhermegg
post Mar 20 2017, 07:42 PM
Post#5



Posts: 12
Joined: 18-November 16



@GroverParkGeorge

My SQL:
CODE
SELECT CT_Filtros_Ecomm.[Dt Criação], CT_Filtros_Ecomm.[Manifestação Nova], Format([CT_Filtros_Ecomm]![Dt Criação],"mmmm") AS Mês, Count(CT_Filtros_Ecomm.Atendim) AS ContarDeAtendim, TB_Semanas.Semana, CT_Filtros_Ecomm.[Assunto Completo] INTO TB_Ecomm_CRC
FROM CT_Filtros_Ecomm INNER JOIN TB_Semanas ON CT_Filtros_Ecomm.[Dt Criação] = TB_Semanas.Data
GROUP BY CT_Filtros_Ecomm.[Dt Criação], CT_Filtros_Ecomm.[Manifestação Nova], Format([CT_Filtros_Ecomm]![Dt Criação],"mmmm"), TB_Semanas.Semana, CT_Filtros_Ecomm.[Assunto Completo];


This code results in the following value:
<<removed external image. see below>>
Field "ContarDeAt" is the one that results in a different value in Excel.

My Excel Worksheet looks like the following:
<<removed external image. see below>>

I'm linking my Access Query to the worksheet and then using the worksheet with the linked query.
This post has been edited by GroverParkGeorge: Mar 20 2017, 08:25 PM
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 07:53 PM
Post#6


UA Admin
Posts: 28,908
Joined: 20-June 02
From: Newcastle, WA


Thank you.

It looks like the higher values for two categories are already there in the Excel source table. Wouldn't that be the cause of the difference in the query based on those values?

I also see that filters have been applied to both the Access table and the Excel table. Is that a factor?

Technically, we can't support externally linked images like this, so I'm going to ask you to upload them here.

Once that's done we'll have to delete the external links. Unfortunately, it's a matter of consistency within our guidelines.


--------------------
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 07:54 PM
Post#7


UA Admin
Posts: 28,908
Joined: 20-June 02
From: Newcastle, WA


Another potential factor is that your query shows a join between two tables. That's fine in Access, but how is that supposed to work with the linked Excel table. Is the join the same in the query that gets records from it?

--------------------
Go to the top of the page
 
guilhermegg
post Mar 20 2017, 08:09 PM
Post#8



Posts: 12
Joined: 18-November 16



@GroverParkGeorge
I think you have missed my point.

I'm not using any table from Excel to make my Access Query.

I'm just trying to connect my Access Query (that has the correct values) in a new Excel Worksheet using the default Data External Connection available in Excel.

That's when the problem happens, my Excel Worksheet should look exactly the same as the Access Query because overall they are the same thing.
In theory, I'm just copying the Access Query and pasting it into an Excel Worksheet, but somehow Excel changes its values.
Filter is just to exemplify a single day where Excel changed the values so it's not a Factor.

Sorry about the links, I've tried to upload the pictures but got an error message, I've attached it now.

Thanks.
Attached File(s)
Attached File  consultaExcel.PNG ( 24.43K )Number of downloads: 5
Attached File  consultaAccess.PNG ( 22.46K )Number of downloads: 4
 
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 08:24 PM
Post#9


UA Admin
Posts: 28,908
Joined: 20-June 02
From: Newcastle, WA


Ah, I see. You are trying to query the same data in Access, but running the query from a data connection in Excel. I got it the other way around, that your query was only in Access, trying to use data in Excel.

In that case, I can't think of a reason for the discrepancy but one thing you could verify is that the Excel data connection is actually looking at the right Access tables.


--------------------
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 10:49 PM
Post#10


UA Admin
Posts: 28,908
Joined: 20-June 02
From: Newcastle, WA


Sorry, I've been trying to find a problem that might be indicative of what you're seeing, but things do seem correct.

Are you connecting using the saved query, or the SQL from it?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Mar 21 2017, 08:05 AM
Post#11


UA Admin
Posts: 28,908
Joined: 20-June 02
From: Newcastle, WA


In other words, you have both an Access query and an Excel version of that query. It's important to be sure the actual syntax in the Excel data connection is the same. Are you calling the query by name in the data connection? Or by running the SQL statement directly?

I've tried several queries and not seen anything like this problem.

Perhaps you can upload a sample of your accdb and xlsx files with just enough data to see the query working as you describe.

--------------------
Go to the top of the page
 
guilhermegg
post Mar 21 2017, 10:00 AM
Post#12



Posts: 12
Joined: 18-November 16



@GroverParkGeorge
I Manage to solve the problem by changing my Access Query to a Make Table Query, then I connected my Access Table to the Worksheet and the values matched.

However, I'm intrigued because I've been doing this kind of procedure (Creating a Query in Access then connecting it to Excel directly by the Query not the SQL) for quite a long time and I've never seen something like that before it must be some weird bug afterall.

Anyway, thank you for your help.
If I find anything else I'll update this topic :>

Cheers,
Guilherme.

Go to the top of the page
 
GroverParkGeorge
post Mar 21 2017, 10:25 AM
Post#13


UA Admin
Posts: 28,908
Joined: 20-June 02
From: Newcastle, WA


I'm very curious as well. There's simply nothing that I know of which would account for that problem at the application level. I was thinking it might be data related; the fact that creating a temporary table in Access with the query results DOES work as required points in the same direction. I'd love to see the sample data to poke around in, if that is possible. I don't know Portuguese, but my Spanish is not half bad, and there's enough similarity that I could fumble my way around your query.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    28th March 2017 - 10:20 PM