Query won't return correct value but ? instead

[diddydustin]

Hey guys,

I'm using the following query:

SELECT EMR_TotalGraphicTime.[Sum Of Time_Total],
EMR_TotalGraphicTime.Time_Code,
EMR_TimeCodeList.Desc,
EMR_Base.Machine,
EMR_Base.Date,
EMR_Base.Shift
FROM
(EMR_Base INNER JOIN EMR_TotalGraphicTime ON EMR_Base.Time_ID = EMR_TotalGraphicTime.Time_ID) INNER JOIN EMR_TimeCodeList ON EMR_TotalGraphicTime.Time_Code = EMR_TimeCodeList.Code
WHERE
(((1)=1) AND ((EMR_Base.Machine)=1224) AND ((EMR_TotalGraphicTime.Time_Code)=[EMR_TimeCodeList].

))
GROUP BY 
EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Time_Code, 
EMR_TimeCodeList.Desc, 
EMR_Base.Machine, 
EMR_Base.Date,
 EMR_Base.Shift;

Here's an example of the what the query returns:

Machine	Sum Of Time_Total	Time_Code	Desc	Date	           Shift
1224	            5400	              900	          ?	     0/9/2004	          1



The values in Desc where the ? shows  up are like this:

Code	Desc
1	Machine Not Scheduled
2	Startup
3	Shutdown
4	Clean/Grease
5	Scheduled Maintenance
6	Non-Scheduled Maintenance (Mechanical)


Desc is of type Memo.

Do you guys know why it won't return the value 'Machine Not Scheduled' for example instead of ?? I'm using this query for a report and I need to display the details about the time code.

Thank you
diddydustin

 

[PHV]

Why have such redundant criteria ?
In a join:
ON EMR_TotalGraphicTime.Time_Code = EMR_TimeCodeList.Code
And in the where clause:
(EMR_TotalGraphicTime.Time_Code)=[EMR_TimeCodeList].

Furthermore I don't know if you may group by a memo field ...

BTW, why using a GROUP BY clause when you don't use any aggregate function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

It may be helpful to break this query up into several smaller queries. It might also be helpful to write out a description of what you want to do. Then it may be easier for you and us to diagnose the problem.

You can group on a memo field.

 

[Golom]

You appear to be grouping on every field in the Select part of the statement which means that the grouping is not necessary. I have removed the GROUP BY and eliminated some of the redundant WHERE restrictions that PHV referred to. "Date" is a reserved name so I enclosed it in brackets (though that's probably not the problem.) I've also inserted some table aliases just for readability.

SELECT 
  T.[Sum Of Time_Total],
  T.Time_Code, 
  L.Desc, 
  B.Machine, 
  B.[Date], 
  B.Shift

FROM 
  (EMR_Base As B INNER JOIN EMR_TotalGraphicTime As T ON B.Time_ID = T.Time_ID) 
  INNER JOIN EMR_TimeCodeList As L ON T.Time_Code = L.Code

WHERE 
  B.Machine=1224

I note that your result shows a "Time Code" value of 900 but the table that you presented gives values of "Code" in the range 1-6. Are you sure that the table "EMR_TimeCodeList" doesn't contain a row like
[tt]
Code Desc
900 ?
[/tt]