Combining values from query

[diddydustin]

Hey guys,

I'm using the following query:

SELECT EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder,
EMR_TotalGraphicTime.Time_Code,
EMR_TotalGraphicTime.[Sum Of Time_Total] AS [SUM]
FROM
EMR_EmployeeHistory
INNER JOIN
EMR_TotalGraphicTime ON EMR_EmployeeHistory.Job_ID = EMR_TotalGraphicTime.Job_ID
GROUP BY
EMR_EmployeeHistory.Press_Operator,
EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder,
EMR_TotalGraphicTime.Time_Code,
EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Job_ID,
EMR_EmployeeHistory.Job_ID
HAVING (((EMR_EmployeeHistory.Job_ID)=[EMR_TotalGraphicTime].[Job_Id]));


It returns all times depending on Job_ID and associates each time with it's specific time code. However, for each job id, i want all times with time code 100 to return one value, all times with time code 900 to return another value, and everything else to total up to a third value. the query now returns every time code and also returns more then one line for, say, time code 100.

thanks in advance

dustin

 

[SkipVought]

dustin,

Try using Distinct in your query.

Also there was redundant code and superfluous code...

SELECT Distinct EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder, 
TGT.Time_Code, TGT.[Sum Of Time_Total] AS [SUM] 
FROM EMR_EmployeeHistory EMR 
INNER JOIN EMR_TotalGraphicTime TGT ON EMP.Job_ID = TGT.Job_ID;

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[diddydustin]

I'm trying this query now:

SELECT DISTINCT EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code, EMR_TotalGraphicTime.[Sum Of Time_Total] AS [SUM]
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID;

However, it is returning results such as these:

Press_Operator SecondPressman Feeder Time_Code SUM
Sonny Preston Andy Dietrich Robert E. Adams 10 6840
Sonny Preston Andy Dietrich Robert E. Adams 36 3600
Sonny Preston Andy Dietrich Robert E. Adams 100 21960
Sonny Preston Andy Dietrich Robert E. Adams 100 25200



Notice Time_Code 100 is displayed twice. I want both 100's to be one value, the sum of the two values displayed.

Why is this happening? Thanks

Dustin

 

[SkipVought]

that's because SUM has TWO SEPARATE VALUES -- EACH IS DISTINCT.

Your query needs to acually SUM the last column...
[tt]
SELECT Distinct EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder,
TGT.Time_Code, SUM(TGT.[Sum Of Time_Total]) AS [SUM]
FROM EMR_EmployeeHistory EMR
INNER JOIN EMR_TotalGraphicTime TGT ON EMP.Job_ID = TGT.Job_ID
GroupBy EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder,
TGT.Time_Code;
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[diddydustin]

Okay, I got the query working. However, how do I combine all SUMS that do not have a time code of 100 or 900 into one value?

Thank you
Dustin

 

[SkipVought]

[tt]
SELECT Distinct EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder,
iif(TGT.Time_Code=100,100,iif(TGT.Time_Code=900,200,999)),
SUM(TGT.[Sum Of Time_Total]) AS [SUM]
FROM EMR_EmployeeHistory EMR
INNER JOIN EMR_TotalGraphicTime TGT ON EMP.Job_ID = TGT.Job_ID
GroupBy EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder,
TGT.Time_Code;
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

how do I combine all SUMS that do not have a time code of 100 or 900 into one value?
You may try this:
SELECT EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder,
TGT.Time_Code, SUM(TGT.[Sum Of Time_Total]) AS [SUM]
FROM EMR_EmployeeHistory EMR
INNER JOIN EMR_TotalGraphicTime TGT ON EMP.Job_ID = TGT.Job_ID
WHERE TGT.Time_Code In (100, 900)
GROUP BY EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder, TGT.Time_Code
UNION
SELECT EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder,
0, SUM(TGT.[Sum Of Time_Total])
FROM EMR_EmployeeHistory EMR
INNER JOIN EMR_TotalGraphicTime TGT ON EMP.Job_ID = TGT.Job_ID
WHERE TGT.Time_Code Not In (100, 900)
GROUP BY EMP.Press_Operator, EMP.SecondPressman, EMP.Feeder
;

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

 

[diddydustin]

Okay, I had a little bit of trouble with your query PHV so i'm basically trying this. I have three seperate queries that look like this:

SELECT DISTINCT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE (((EMR_TotalGraphicTime.Time_Code)=100))
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code, EMR_EmployeeHistory.Time_ID;


The other two have

WHERE (((EMR_TotalGraphicTime.Time_Code)=900))

and

WHERE (((EMR_TotalGraphicTime.Time_Code)<>100 And (EMR_TotalGraphicTime.Time_Code)<>900))


I want to write a seperate query that unions these three into one query for a report. How can I do this?


Thanks!!

Dustin

 

[PHV]

First, don't use the DISTINCT keyword in an aggregate query as the job is done by the GROUP BY clause.
For your report you may try this union query:
SELECT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE EMR_TotalGraphicTime.Time_Code In (100,900)
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
UNION
SELECT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, 0
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE EMR_TotalGraphicTime.Time_Code Not In (100,900)
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder
;


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

 

[diddydustin]

Okay, that works-- however, my query has all the times either listed as 100, 900, or 0. That is fine, but in my report, how can I select only the times for the 100 code to list under one column, and the same for the 900's and 0's?

Thank you

Dustin

 

[PHV]

This is a new question with better chance of accurate replies in the Access: Reports forum.