Wrong results in imbedded query

Jddjr

New Member
Joined
May 4, 2018
Messages
1
I have a query in MSAccess. The results are correct there. I imbed the query in Excel and the results are different. I am using Access and Excel version 10 so I cant use other sources I have to link through the access button as the other way only sees .mdb.
Here is the SQL. The OutPut_Matl.Group field is the only one incorrect.


SELECT First_of_year.FirstOfYear, First_of_year.FirstOfMonth, First_of_year.Division, First_of_year.Order, First_of_year.[Operation #] AS Operation, OutPut_Matl.Description AS Classification, OutPut_Matl.Group, OutPut_Matl.tYPE, OutPut_Matl.SumOfCountOfClassification AS Cnt, OutPut_Matl.Cost1 AS Matl, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_Oper_Contr]![oper_cst]/[Count_of_materials]![CountOfDescription],[Total_Oper_Contr]![oper_cst]) AS Contr, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_Traffic]![Traffic]/[Count_of_materials]![CountOfDescription],[Total_Traffic]![Traffic]) AS Traf_cst, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_OS]![O-Serv]/[Count_of_materials]![CountOfDescription],[Total_OS]![O-Serv]) AS [O-Serv], IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_surch]![Surch]/[Count_of_materials]![CountOfDescription],[Total_surch]![Surch]) AS Surch, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_oper_lbr]![Labor]/[Count_of_materials]![CountOfDescription],[Total_oper_lbr]![Labor]) AS Lbr_cst, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Operation_Actual_Hours]![ACTUAL_WORK]/[Count_of_materials]![CountOfDescription],[Operation_Actual_Hours]![ACTUAL_WORK]) AS Lbr_hrs
FROM (((((((First_of_year LEFT JOIN OutPut_Matl ON (First_of_year.[Operation #] = OutPut_Matl.[Operation #]) AND (First_of_year.Order = OutPut_Matl.Order)) LEFT JOIN Count_of_materials ON (OutPut_Matl.[Operation #] = Count_of_materials.[Operation #]) AND (OutPut_Matl.Order = Count_of_materials.Order)) LEFT JOIN Operation_Actual_Hours ON (First_of_year.[Operation #] = Operation_Actual_Hours.[Operation #]) AND (First_of_year.Order = Operation_Actual_Hours.Order)) LEFT JOIN Total_Oper_Contr ON (First_of_year.[Operation #] = Total_Oper_Contr.[Operation #]) AND (First_of_year.Order = Total_Oper_Contr.Order)) LEFT JOIN Total_Traffic ON (First_of_year.[Operation #] = Total_Traffic.[Operation #]) AND (First_of_year.Order = Total_Traffic.Order)) LEFT JOIN Total_OS ON (First_of_year.[Operation #] = Total_OS.[Operation #]) AND (First_of_year.Order = Total_OS.Order)) LEFT JOIN Total_surch ON (First_of_year.[Operation #] = Total_surch.[Operation #]) AND (First_of_year.Order = Total_surch.Order)) LEFT JOIN Total_oper_lbr ON (First_of_year.[Operation #] = Total_oper_lbr.[Operation #]) AND (First_of_year.Order = Total_oper_lbr.Order);
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
so you are using a query on an internal table, gets 1 result,
then the same query on an external excel file and get another result?

if the data is the same in both ,you WILL get the same results.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,147
Members
409,804
Latest member
aceyus_michael

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top