Excel showing two different sets of results from same query

[Citrus_Dan]

Hi guys, new member here.

I hope I have posted this in the correct section!

I've done a little bit of a search on the net and I can't seem to find an answer to this..

Basically we have a query here that works just fine when run through access. When we try to link it to excel however, it shows up different data.

The query is pretty simple, but carries across a fair amount of data (5367 lines).

It is strange because the data is pretty much identical in both places, but on excel, some of the values (of our sales) have changed. Excel actually ends up showing that we have made less money than access says.

This is choosing the 'link data from access' option through excel.

However, if I right click on the query in access and choose 'export to excel' the correct values are actually displayed in excel!

So somehow, pushing the data from access is correct, and pulling the data from excel is incorrect.. :banghead:

(Excel and access both 2013 versions, latest updates)

We really need the live link to a spreadsheet.

If anyone has come across this issue, or has any idea what is going on, I'd be very grateful to hear back.

Thanks

Dan

 

[spikepl]

There is nothing solid to look at: show the SQL of the query.

 

[Citrus_Dan]

Oh, sorry!

Query SQL:

SELECT qryTDAFigs_Export_C.CourseYear, qryTDAFigs_Export_C.CourseMonth, qryTDAFigs_Export_C.AC_Manager, qryTDAFigs_Export_C.CTLAccountName, qryTDAFigs_Export_C.BookingTDA, qryTDAFigs_Export_C.ClientTDA, qryTDAFigs_Export_C.ClientTDM, qryTDAFigs_Export_C.SumOfOverallTotal, qryTDAFigs_Export_C.Type, tblCTLSales.SalesType, tblCTLSales_1.SalesType AS ACSalesType
FROM tblCTLSales AS tblCTLSales_1 RIGHT JOIN (tblCTLSales RIGHT JOIN qryTDAFigs_Export_C ON tblCTLSales.SalesContact = qryTDAFigs_Export_C.CTLAccountName) ON tblCTLSales_1.SalesContact = qryTDAFigs_Export_C.AC_Manager
WHERE (((qryTDAFigs_Export_C.CourseYear)=Year(Date())-1) AND ((qryTDAFigs_Export_C.CourseMonth) Between 1 And 12)) OR (((qryTDAFigs_Export_C.CourseYear)=Year(Date())) AND ((qryTDAFigs_Export_C.CourseMonth) Between 1 And 12)) OR (((qryTDAFigs_Export_C.CourseYear)=Year(Date())+1) AND ((qryTDAFigs_Export_C.CourseMonth) Between 1 And 12));

 

[spikepl]

Nothing obvious, but you have not shown all SQL since this query is based on another query. Do that first before we waste more time on guesses.

My bet would be data types, i.e. you don't get the same in both cases and bits and pieces get "lost in translation". Show specific example of "some of the values (of our sales) have changed. Excel actually ends up showing that we have made less money than access says."

 

[Citrus_Dan]

The SQL for the other query is:

SELECT ALL
CourseYear, CourseMonth, BookingTDA, ClientTDM, ClientTDA, CTLAccountName, AC_Manager, SumOfOverallTotal,Type
FROM qryTDAFigs_TrainingAllSum_C
UNION ALL SELECT ALL
CourseYear, CourseMonth, BookingTDA, ClientTDM, ClientTDA, CTLAccountName, AC_Manager, SumOfCPCSBookingTotal, Type
FROM qryTDAFigs_CPCSCombSum_C
UNION ALL SELECT ALL
CourseYear, CourseMonth, BookingTDA, ClientTDM, ClientTDA, CTLAccountName, AC_Manager, SumOfCharge, Type
FROM qryTDAFigs_CancelSum_C
UNION ALL SELECT ALL
CourseYear, CourseMonth, BookingTDA, ClientTDM, ClientTDA, CTLAccountName, AC_Manager, SumOfCPCSBookingCancel, Type
FROM qryTDAFigs_CPCSCancelCombSum_C
UNION ALL SELECT ALL
AdjustmentYear, AdjustmentMonth,TDA,TDM, Null, Null, Null, SumOfAdjustmentAmount, Type
FROM qryTDAFigs_Adjustments
UNION ALL SELECT ALL
CourseYear, CourseMonth, BookingTDA, ClientTDM, ClientTDA, CTLAccountName, AC_Manager, DelegateTotal, BookingType
FROM qryTDAFigs_EWPADels
UNION ALL SELECT ALL
CourseYear, CourseMonth, BookingTDA, ClientTDM, ClientTDA, CTLAccountName, AC_Manager, CancelDelsTotal, Type
FROM qryTDAFigs_EWPADelsCancel;

thanks

 

[Citrus_Dan]

And then I guess you'll be asking for the SQL of the other query, right?