p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Veiw Reports only for current month. (http://p2p.wrox.com/showthread.php?t=33867)

Dharam80 September 30th, 2005 10:12 AM

Veiw Reports only for current month.
 
I have set parameters to view report by asking the user to enter Month name they would like to view reports for. Is there a way to view reports for current month whithout parameters?

Dharmesh Bhavsar

dartcoach September 30th, 2005 10:48 AM

Dharmesh,

Check the system date and compare the report data month.

DatePart("m", Date()) = DatePart("m", ReportDate)

Hope this helps.

Kevin




dartcoach

Dharam80 September 30th, 2005 11:11 AM

So where do I put that code?

Dharmesh Bhavsar

dartcoach September 30th, 2005 11:13 AM

Dharmesh,
Where do you check for their input month? You need to replace the code that compares their input month with the above code.

Kevin

dartcoach

Dharam80 September 30th, 2005 11:19 AM

Well soon as they click view a certain report, I want the access to automatically show the report for the current month without promting them to enter Month name.

Dharmesh Bhavsar

dartcoach September 30th, 2005 11:22 AM

What is the report source? A table or a query? If it's a query, can you send me the sql?

Kevin

dartcoach

Dharam80 September 30th, 2005 11:27 AM

its too long but here you go: SELECT DISTINCTROW [Extrusion Report Query].Date, [Extrusion Report Query].[Line#], [Extrusion Report Query].[Shift#], [Daily Production Summary Totals].[First Pass Yield], (([Total Hours]-[Sum Of Total Down time_Total Hours])/[Total Hours]) AS Utilization, [Total LBS Made]/[Production Total Hours] AS [Total LBS/Hr], [Daily Production Summary Totals].[Color %], [Daily Production Summary Totals].[CACO %], IIf(IsNull([Sum Of Production D/T Hours]/[Total Hours]),0,[Sum Of Production D/T Hours]/[Total Hours]) AS [Production D/T], IIf(IsNull([Sum Of Maintenance hours]/[Total Hours]),0,[Sum Of Maintenance hours]/[Total Hours]) AS [Maintenance D/T], [Daily Production Summary Totals].[Good LBS], [Daily Production Summary Totals].[Scrap LBS], [Daily Production Summary Totals].[Total LBS Made], Sum((IIf(IsNull([Maintenance hours]),0,[Maintenance hours]))) AS [Sum Of Maintenance hours], Sum((IIf(IsNull([Production D/T Hours]),0,[Production D/T Hours]))) AS [Sum Of Production D/T Hours], Sum(Nz([Total Down time]![Total Hours],0)) AS [Sum Of Total Down time_Total Hours], [Production Hours Total].[Production Total Hours], Nz([Production Total Hours],0)+Nz([Sum Of Total Down time_Total Hours],0) AS [Total Hours], Calendar.Week, Calendar.Month, [Daily Production Summary Totals].[Nat CLR Prime], [Daily Production Summary Totals].Regrind, [Daily Production Summary Totals].[Color LBS], [Daily Production Summary Totals].Prime, [Daily Production Summary Totals].CACO
FROM ([Production Hours Total] INNER JOIN ([Daily Production Summary Totals] RIGHT JOIN ((([Extrusion Report Query] LEFT JOIN Maintenance ON ([Extrusion Report Query].DownTimeList = Maintenance.DownTimeList) AND ([Extrusion Report Query].[Operator Id] = Maintenance.[Operator Id]) AND ([Extrusion Report Query].[Shift#] = Maintenance.[Shift#]) AND ([Extrusion Report Query].[Line#] = Maintenance.[Line#]) AND ([Extrusion Report Query].Date = Maintenance.Date) AND ([Extrusion Report Query].[Total Hours] = Maintenance.[Maintenance hours])) LEFT JOIN [Production Downtime] ON ([Extrusion Report Query].[Shift#] = [Production Downtime].[Shift#]) AND ([Extrusion Report Query].[Line#] = [Production Downtime].[Line#]) AND ([Extrusion Report Query].Date = [Production Downtime].Date) AND ([Extrusion Report Query].[Total Hours] = [Production Downtime].[Production D/T Hours]) AND ([Extrusion Report Query].DownTimeList = [Production Downtime].DownTimeList) AND ([Extrusion Report Query].[Operator Id] = [Production Downtime].[Operator Id])) LEFT JOIN [Total Down time] ON ([Extrusion Report Query].[Shift#] = [Total Down time].[Shift#]) AND ([Extrusion Report Query].[Line#] = [Total Down time].[Line#]) AND ([Extrusion Report Query].Date = [Total Down time].Date) AND ([Extrusion Report Query].[Total Hours] = [Total Down time].[Total Hours]) AND ([Extrusion Report Query].[Operator Id] = [Total Down time].[Operator Id]) AND ([Extrusion Report Query].DownTimeList = [Total Down time].DownTimeList)) ON ([Daily Production Summary Totals].Date = [Extrusion Report Query].Date) AND ([Daily Production Summary Totals].[Line#] = [Extrusion Report Query].[Line#]) AND ([Daily Production Summary Totals].[Shift#] = [Extrusion Report Query].[Shift#])) ON ([Production Hours Total].[Shift#] = [Extrusion Report Query].[Shift#]) AND ([Production Hours Total].[Line#] = [Extrusion Report Query].[Line#]) AND ([Production Hours Total].Date = [Extrusion Report Query].Date)) INNER JOIN Calendar ON [Production Hours Total].Date = Calendar.Date
GROUP BY [Extrusion Report Query].Date, [Extrusion Report Query].[Line#], [Extrusion Report Query].[Shift#], [Daily Production Summary Totals].[First Pass Yield], [Daily Production Summary Totals].[Color %], [Daily Production Summary Totals].[CACO %], [Daily Production Summary Totals].[Good LBS], [Daily Production Summary Totals].[Scrap LBS], [Daily Production Summary Totals].[Total LBS Made], [Production Hours Total].[Production Total Hours], Calendar.Week, Calendar.Month, [Daily Production Summary Totals].[Nat CLR Prime], [Daily Production Summary Totals].Regrind, [Daily Production Summary Totals].[Color LBS], [Daily Production Summary Totals].Prime, [Daily Production Summary Totals].CACO;


Dharmesh Bhavsar

dartcoach September 30th, 2005 11:32 AM

Dharmesh,

I need to see the sql for Extrusion Report Query

Also, what is in the table Calendar?

Kevin

dartcoach

Dharam80 September 30th, 2005 11:35 AM

The Calender table is the calender company follows for production reports and here is the sql for exrusion query:
SELECT [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, ([Minutes]/60) AS [Total Hours], [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Month
FROM [Extrusion DT Log] INNER JOIN (Calendar INNER JOIN [Extrusion Report] ON Calendar.Date = [Extrusion Report].Date) ON [Extrusion DT Log].[Extrusion DT Id] = [Extrusion Report].DownTimeList
GROUP BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Month
HAVING (((Calendar.Month)=[Enter Month Name]))
ORDER BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].DownTimeList;


Dharmesh Bhavsar

dartcoach September 30th, 2005 11:41 AM

Dharmesh,
In the table Calendar, what is the format of Month? Is it the full name of the month, i.e. January, or Jan?

Kevin

dartcoach


All times are GMT -4. The time now is 05:39 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.