queries and pivot chart view question

[devagupt]

I have a form where the supervisors enter the date and click a button which runs a query and displays the information.
1st problem - The information displayed is correct but when i try to view the information in pivot chart , it doesnt display the value in the bar chart for some of the fields.

2nd question - can i have it in such a way that when the supervisors hit the button , they will view the results of a query in a pivot chart view as opposed to in a datasheet view?

Please help!

Thanks.

 

[devagupt]

For some reason it displays whole numbers only and any field which has a value more than 1. Any help?

 

[lespaul]

what's the SQL of the query(ies)?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[devagupt]

Below is the SQL code


CODE**

SELECT tblDowntimeTable.Date01, tblDowntimeTable.Line, tblDowntimeTable.Shift, Sum([tblDowntimeTable]![Defective Product]*0.0167) AS DefectiveProduct, Sum([tblDowntimeTable]![CH/LD Change]*0.0167) AS [CH/LDchange], Sum([tblDowntimeTable]![Fine Tuning]*0.0167) AS FineTuning, Sum([tblDowntimeTable]![Label Machine]*0.0167) AS LabelMachine, Sum([tblDowntimeTable]![Product Change]*0.0167) AS [Product Change], Sum([tblDowntimeTable]![Roll count]*0.0167) AS Rollcount, Sum([tblDowntimeTable]![Rotation]*0.0167) AS Rotation1, Sum([tblDowntimeTable]![Comp Control]*0.0167) AS CompControl, Sum([tblDowntimeTable]![Start up]*0.0167) AS Startup, Sum([tblDowntimeTable]![Equipment]*0.0167) AS Equipment1, Sum([tblDowntimeTable]![Long Breaks]*0.0167) AS LongBreaks, Sum([tblDowntimeTable]![Bad components]*0.0167) AS Badcomponents, Sum([tblDowntimeTable]![Re-ins/Re-work]*0.0167) AS [Re-ins\Re-work], Sum([tblDowntimeTable]![Dabrico]*0.0167) AS Dabrico1, Sum([tblDowntimeTable]![Morning meet]*0.0167) AS Morningmeet, Sum([tblDowntimeTable]![Make boxes]*0.0167) AS Makeboxes, Sum([tblDowntimeTable]![Other]*0.0167) AS Other1, Sum(([tblDowntimeTable]![Defective Product]+[tblDowntimeTable]![CH/LD Change]+[tblDowntimeTable]![Fine Tuning]+[tblDowntimeTable]![Label Machine]+[tblDowntimeTable]![Product Change]+[tblDowntimeTable]![Roll count]+[tblDowntimeTable]![Rotation]+[tblDowntimeTable]![Comp Control]+[tblDowntimeTable]![Start up]+[tblDowntimeTable]![Equipment]+[tblDowntimeTable]![Long Breaks]+[tblDowntimeTable]![Bad components]+[tblDowntimeTable]![Re-ins/Re-work]+[tblDowntimeTable]![Dabrico]+[tblDowntimeTable]![Morning meet]+[tblDowntimeTable]![Make boxes]+[tblDowntimeTable]![Other])*0.0167) AS Total, tblDowntimeTable.Comments
FROM tblDowntimeTable
GROUP BY tblDowntimeTable.Date01, tblDowntimeTable.Line, tblDowntimeTable.Shift, tblDowntimeTable.Comments, DatePart("ww",[tblDowntimeTable]![Date01])
HAVING (((tblDowntimeTable.Date01) Between [forms]![frmgetdowntimedata].[txbed] And [forms]![frmgetdowntimedata].[txbsd]) AND ((tblDowntimeTable.Line) Like [forms]![frmgetdowntimedata].[cboline1] & "*") AND ((tblDowntimeTable.Shift) Like [forms]![frmgetdowntimedata].[cboshift1] & "*")) OR (((tblDowntimeTable.Date01)=[forms]![frmgetdowntimedata].[txbsd]) AND ((tblDowntimeTable.Line) Like [forms]![frmgetdowntimedata].[cboline1] & "*") AND ((tblDowntimeTable.Shift) Like [forms]![frmgetdowntimedata].[cboshift1] & "*"))
ORDER BY DatePart("ww",[tblDowntimeTable]![Date01]);

 

[SkipVought]

Hi,

FYI, it would help YOU and anyone trying to help, if you would STORE & POST you code something like...

SELECT 
  Date01
, Line
, Shift
, Sum([Defective Product]*0.0167)   AS DefectiveProduct
, Sum([CH/LD Change]*0.0167)        AS [CH/LDchange]
, Sum([Fine Tuning]*0.0167)         AS FineTuning
, Sum([Label Machine]*0.0167)       AS LabelMachine
, Sum([Product Change]*0.0167)      AS [Product Change]
, Sum([Roll count]*0.0167)          AS Rollcount
, Sum([Rotation]*0.0167)            AS Rotation1
, Sum([Comp Control]*0.0167)        AS CompControl
, Sum([Start up]*0.0167)            AS Startup
, Sum([Equipment]*0.0167)           AS Equipment1
, Sum([Long Breaks]*0.0167)         AS LongBreaks
, Sum([Bad components]*0.0167)      AS Badcomponents
, Sum([Re-ins/Re-work]*0.0167)      AS [Re-ins\Re-work]
, Sum([Dabrico]*0.0167)             AS Dabrico1
, Sum([Morning meet]*0.0167)        AS Morningmeet
, Sum([Make boxes]*0.0167)          AS Makeboxes
, Sum([Other]*0.0167)               AS Other1
, Sum(([Defective Product]+[CH/LD Change]+[Fine Tuning]+[Label Machine]+[Product Change]+[Roll count]+[Rotation]+[Comp Control]+[Start up]+[Equipment]+[Long Breaks]+[Bad components]+[Re-ins/Re-work]+[Dabrico]+[Morning meet]+[Make boxes]+[Other])*0.0167)             AS Total
, Comments

FROM tblDowntimeTable

GROUP BY
  Date01
, Line
, Shift
, Comments
, DatePart("ww",[Date01])

HAVING (((Date01) Between [forms]![frmgetdowntimedata].[txbed] And [forms]![frmgetdowntimedata].[txbsd])
  AND ((Line)   Like [forms]![frmgetdowntimedata].[cboline1] & "*")
  AND ((Shift)  Like [forms]![frmgetdowntimedata].[cboshift1] & "*")) OR (((Date01)=[forms]![frmgetdowntimedata].[txbsd])
  AND ((Line)   Like [forms]![frmgetdowntimedata].[cboline1] & "*")
  AND ((Shift)  Like [forms]![frmgetdowntimedata].[cboshift1] & "*"))

ORDER BY
  DatePart("ww",[Date01]);

Skip,

_[red][/red]