Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple queries -> one report?

Status
Not open for further replies.
diddydustin

diddydustin

Programmer
Jul 30, 2002
181
US
Hey guys,

I'm using the following 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
;


However, this query gives me one column of SUM values for time codes either 100, 900, or 0. In my report, I want to be able to break this down and have three columns, one for each time code. How can I do this?

Thank you
Dustin
 
Sort by date Sort by votes
Okay, great! Now I have a crosstab query with the time code as the column heading for now. However, the headings are like this: 1 3 4 5 11 55 90 78 100 900

I really just want:
0 (or anything for 0) 100 900

So for each employee I only want three times. I want everything that's not 100 or 900 into one value.

Thank you so much-- i'm learning a lot.

Dustin
 
Upvote 0 Downvote
I've tried messing with it a lot this morning... I've built some UNION queries based on what we've been talking about. However, I can't get the crosstab query to just show three columns instead of all of the codes.

Dustin
 
Upvote 0 Downvote
Your column headings would be an expression that evaluates the TimeCode and returns the proper result:
[blue]ColHead: IIf([TimeCode]<100,"Under 100",[Desc])[/blue]
THis assumes you have the EMR_TimeList table in your query.

BTW: Desc is a poor choice for a field name since it is used in SQL for "Descending". It may or may not cause issues.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Okay, great!! However, a couple things are occuring. The 'Downtime' (anything under 100) never shows up. Also, a name appears twice in the list. So it looks like this in the table:


John Day 4378 0
John Day 0 8937


I need it to look like this:

John Date 4378 8937


Is this possible? Thank you

Dustin
 
Upvote 0 Downvote
How about sharing your SQL view of the crosstab? Did John Day have any under 100? Is the code field text or numeric?


Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Time_Code is a number.

NO downtimes (code <> 100 && <> 900) show up for anyone, and I know they exist.

Here's the SQL for the crosstab:

TRANSFORM Sum(EMR_UnionTest.[Sum Of Time_Total]) AS [SumOfSum Of Time_Total]
SELECT EMR_UnionTest.Employee, Sum(EMR_UnionTest.[Sum Of Time_Total]) AS [Total Of Sum Of Time_Total]
FROM EMR_UnionTest
WHERE (((EMR_UnionTest.Desc)=IIf([Time_Code]<100,"Under 100",[Desc])))
GROUP BY EMR_UnionTest.Employee, EMR_UnionTest.Desc
PIVOT EMR_UnionTest.Time_Code;


 
Upvote 0 Downvote
Why do you have the IIf() expression in the criteria? This should be your column heading expression. That is what I tried to explain with:
[blue]Your column headings would be an expression that evaluates the TimeCode and returns the proper result:
ColHead: IIf([TimeCode]<100,"Under 100",[Desc])[/blue]
Also, remove the EMR_UnionTest.Desc from the Group By.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
When I try to just select 'Column Heading' and under critera I put 'IIf([Time_Code]<100,"Under 100",[Desc])', I get an error that says I need to select at least one row heading, one column heading, and one value. The cursor goes to where I'm supposed to select something for the Column Heading. If I choose 'Time_Code', then it automatically sets it to WHERE under Time_Code.

How am I supposed to just select a column heading?

Dustin
 
Upvote 0 Downvote
Don't put anything in any criteria. Try the SQL:
[tt][blue]
TRANSFORM Sum([Sum Of Time_Total]) AS TheValue
SELECT Employee, Sum([Sum Of Time_Total]) AS AllTimes
FROM EMR_UnionTest INNER JOIN EMR_TimeList On EMR_UnionTest.TimeCode = EMR_TimeList.Time_Code
GROUP BY Employee
PIVOT IIf(EMR_UnionTest.[Time_Code]<100,"Under 100",[Desc]);
[/blue][/tt]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Okay, great!! I got what I want. However, I'm trying to do this:

CInt([Makeready])/60/60
CDbl([Makeready])/60/60


This returns 0, when I really want it to return a decimal value to the nearest 100th. How is this possible? All my conversions aren't working.

Dustin
 
Upvote 0 Downvote
Could you share your SQL of your query? Also, "aren't working" isn't a very good description.
Where are you trying to format your display? This should be done in the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Here's the SQL to the query:

TRANSFORM Sum([Sum Of Time_Total]) AS TheValue
SELECT Employee, Sum([Sum Of Time_Total]) AS AllTimes
FROM EMR_UnionTest INNER JOIN EMR_TimeCodeList ON EMR_UnionTest.Time_Code = EMR_TimeCodeList.Code
GROUP BY Employee
PIVOT IIf(EMR_UnionTest.[Time_Code]<100,"Downtime",EMR_TimeCodeList.[Desc]);


In my report, I'm trying to do this in a textbox:

=[Downtime]/60/60

However, that returned an #Error. I then tried

=CInt([Downtime])/60/60
=CFloat([Downtime])/60/60

This returned a 0. The value stored in [Downtime] is a total of seconds-- basically i'm trying to convert into hours, so the report will display something like 12.6 or 4.2.

Dustin
 
Upvote 0 Downvote
Do you know if you have null values in DownTime? Is the format property of your text box set to display decimal places?

Try:
=Val(Nz([Downtime],0))/3600

Or change your sql to:
[tt][blue]
TRANSFORM Val(Nz(Sum([Sum Of Time_Total]),0)) AS TheValue
SELECT Employee, Sum([Sum Of Time_Total]) AS AllTimes
FROM EMR_UnionTest INNER JOIN EMR_TimeCodeList ON EMR_UnionTest.Time_Code = EMR_TimeCodeList.Code
GROUP BY Employee
PIVOT IIf(EMR_UnionTest.[Time_Code]<100,"Downtime",EMR_TimeCodeList.[Desc]);
[/blue][/tt]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Those still give me an #Error. However, when I typecast it to an INT it returns 0. There are no null values in my query either.

Dustin
 
Upvote 0 Downvote
If there are no values in your query, where do you expect values to come from in your report?

What do you see in your datasheet if your query sql is:
[tt][blue]
SELECT Employee, Sum([Sum Of Time_Total]) AS AllTimes
FROM EMR_UnionTest INNER JOIN EMR_TimeCodeList ON EMR_UnionTest.Time_Code = EMR_TimeCodeList.Code
GROUP BY Employee[/blue][/tt]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
No no no, there are values in my query, but no NULL values-- meaning, there is a 0 instead of a " ".

The result of my query is this:

Employee Downtime Makeready Run Time
Andy Dietrich 33840 19080 78120
Ben Lane 0 0 13680
Bo Brandenburg 96480 31680 78840
Darrell McKinney 367740 32940 249120
David Carter 116220 0 297720
Derek Enlow 106200 139680 352800
Earl McCoy 39600 0 39600
Glenn Suel 16560 8280 26280
Greg Goins 88560 0 282240
Harvey Farris 2160 26640 0
James Gorman 357120 0 0
Jerry Carter 76980 11520 282240
Jerry Colyer 2160 5040 0
Jerry Renner 10800 124920 297360
Mark Loudermilk 1440 111600 57960
Mike Eldridge 16560 8280 26280
Monte Stamper 18720 13320 26280
Robert Adams 3600 11520 13680
Robert E. Adams 13320 15120 60840
Shane Franklin 2820 0 25920
Sonny Preston 10440 0 47160
Steve Burton 96480 31680 78840


For the query you just suggested, it is:

Employee
Andy Dietrich
Ben Lane
Bo Brandenburg
Darrell McKinney
David Carter
Derek Enlow
Earl McCoy
Glenn Suel
Greg Goins
Harvey Farris
James Gorman
Jerry Carter
Jerry Colyer
Jerry Renner
Mark Loudermilk
Mike Eldridge
Monte Stamper
Robert Adams
Robert E. Adams
Shane Franklin
Sonny Preston
Steve Burton
 
Upvote 0 Downvote
Are you saying that [blue]
TRANSFORM Sum([Sum Of Time_Total]) AS TheValue
SELECT Employee, Sum([Sum Of Time_Total]) AS AllTimes
FROM EMR_UnionTest INNER JOIN EMR_TimeCodeList ON EMR_UnionTest.Time_Code = EMR_TimeCodeList.Code
GROUP BY Employee
PIVOT IIf(EMR_UnionTest.[Time_Code]<100,"Downtime",EMR_TimeCodeList.[Desc]);[/blue]
Results in a datasheet with only four columns and none with the name "AllTimes"?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
No, sorry that results in:

Employee Downtime Makeready Run Time
Andy Dietrich 33840 19080 78120
Ben Lane 13680
Bo Brandenburg 96480 31680 78840
Darrell McKinney 367740 32940 249120
David Carter 116220 297720
Derek Enlow 106200 139680 352800
Earl McCoy 39600 39600
Glenn Suel 16560 8280 26280
Greg Goins 88560 282240
Harvey Farris 2160 26640
James Gorman 357120
Jerry Carter 76980 11520 282240
Jerry Colyer 2160 5040
Jerry Renner 10800 124920 297360
Mark Loudermilk 1440 111600 57960
Mike Eldridge 16560 8280 26280
Monte Stamper 18720 13320 26280
Robert Adams 3600 11520 13680
Robert E. Adams 13320 15120 60840
Shane Franklin 2820 25920
Sonny Preston 10440 47160
Steve Burton 96480 31680 78840



The difference between the two is the first has 0 while the second has null values. However, even with the 0's i still get an error. Do you know what it could be?

Dustin
 
Upvote 0 Downvote
I would think it is impossible from the sql provided to get the result you show in your most recent reply. Where is the column for "AllTimes"?

Is your only problem remaining, the ability to display 0 rather than nulls? If so, this can be handled easily in the format property of the text boxes of your form or report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Sorry, I had made AllTimes not visible because I am not using it.


That isn't the only problem-- I have 0's in my query so there should be no NULL values at all. There aren't when I look in the query--

however, i'm still getting an #Error everytime I try to do this:

=[Downtime]/3600

I get a 0 when I do this:

=CInt([Downtime])/3600

So for some reason when I cast the Downtime to an INT it's fine, however, without the typecast I get an #Error!

Is there any other typecast I can try? I'm trying to get a value like 12.6. I set the format to Number, etc, and it still gives me an #Error.


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TrekBiker
  • Locked
  • Question
Print multiple copies of report data 1
Replies
5
Views
494
dhookom
dhookom
breezett93
  • Locked
  • Question
Report often takes multiple minutes to load, lots of formatting time between pages
Replies
5
Views
509
dhookom
dhookom
JimStrand
  • Locked
  • Question
Expression Limit on Sum IIf In Method ? Used in Report Calculation
Replies
1
Views
520
dhookom
dhookom
SamuelR
  • Locked
  • Question
Dlookup in a report
Replies
13
Views
583
dhookom
dhookom
monkeysee
  • Locked
  • Question
Muti Page Report 1
Replies
5
Views
476
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top