Daily/weekly/quarterly Reports

[umair434]

hi,

I want to create daily/weekly/quarterly reports - the data is from the same table. Do I have to make separate queries to get each report.

issue is because I need to make like 30 reports
I know I can use datepart("ww", [dte]) to get the weeks, and similarly get the quarters. but is there anyway I can select "quarter" from combo box and it gives runs the query for quarter and similarly, without making another query for each type required?

thanks!

 

[April15Hater]

Are you using a form to drive your report? I don't think you can put a dropdown on an actualy report...

Where is your dropdown listsource?

I'm thinking you make a form with a listbox and when you make a selection, it opens the report. When it loads the report, use the open event to set the rowsource based on what was selected from the dropdown:

Select Case me.Listbox
  Case is = "Week 1"
      me.rowsource = "SELECT * FROM Table WHERE DateInterval = Week 1"
  Case is = "Week 2"
      me.rowsource = "SELECT * FROM Table WHERE DateInterval = Week 2"
End Select

May be way off here, but I think we need more info to get your question answered. Tell us more about your table.

Joe

 

[umair434]

I have a couple of combo boxes on a form and a list box as well.

combo boxes:
type of employees - day employees or night employees
Name of employees
Range of reports (Weekly, quarterly, yearly etc)

Selecting appropriately shows the list box with the desired reports.

basically, I learnt access on my own so I did not know about normalization before I went ahead and started developing this application.
night time Employees have their own table with respective employeeId, and day time employees have their own table -

these reports are basically fields from the table! so I want to show how are these fields being populated according per day employees, night employees, then break them down per each employee - and also by weekly, quarterly, yearly etc!

hope i am making some sense.

thanks

 

[April15Hater]

What fields are in your table?

 

[umair434]

ActivitiesID
dte (datefield)
employeeID

and here are the reports names with (Numbers)

e.g

highcube
Caseflow
Prewab

etc

 

[vbaInet]

What version of Access are you using? If you are using Access 2007+ then you can use the report's Group By feature to group by an expression. That is, you can group by the expression in your first post, =datepart("ww", [dte])

Now, what you need to do is to create a global function and a global variable in this form:

Option Compare Database
Option Explicit
 
Private strInterval As String
 
 
Public Function GetInterval() As String
    GetInterval = strInterval
End Function
 
 
Public Sub SetInterval(strDatePart As String)
    strInterval = strDatePart
End Sub

Then in your grou by expression you put:

=DatePart(GetInterval(), [dte])

So before you open the report just set the interval:

SetInterval "ww"

... (for example).

 

[umair434]

Option Compare Database
Option Explicit
 
Private strInterval As String
 
 
Public Function GetInterval() As String
    GetInterval = strInterval
End Function
 
 
Public Sub SetInterval(strDatePart As String)
    strInterval = strDatePart
End Sub

I put this code in the on current event of the form where all the combo boxes are, right?

=DatePart(GetInterval(), [dte])

using Access 2007. So this code goes after "ADD a group" options comes

SetInterval "ww"

where do I put this? i tried putting this in the onload event of the report, but it gives me an error "Getinterval() undefined"

since the field will be the same, I just want to get daily/weekly and quarterly reports. what field do I pull in from the query when I am creating a report? do I pull the dte as well or just the calculated part?

thanks!

 

[vbaInet]

ALL wrong!

1. The first set of code goes in a GLOBAL module, not a form or report module and not a class. Just a Module
2. There's such a thing as SORTING & GROUPING in a report. That line goes in there as a new Group as an Expression.
3. You use that code just before you open your report. That is what would determine whether to use ww, w or q.

 

[umair434]

ALL wrong!

1. The first set of code goes in a GLOBAL module, not a form or report module and not a class. Just a Module
2. There's such a thing as SORTING & GROUPING in a report. That line goes in there as a new Group as an Expression.
3. You use that code just before you open your report. That is what would determine whether to use ww, w or q.

1) I created a module and saved it as getinterval, so 1 is done
2) I put the expression in where you mentioned


3) Case "report"
SetInterval "ww"
DoCmd.OpenReport "report", acViewPreview
End Select

I put this code in the double click event of the list box! but it gives me the same error. I know I must be missing something simple here

 

[vbaInet]

1. No, save it as basGetInterval or mdlGetInterval. Avoid repeating names.
2. Good! Which is where?
3. Shouldn't this be also dependent on what is selected in the Range of reports combo box?

 

[umair434]

1) done
2) Add a group, then group on expression - a mini window opens up, and this is where I copy the code =DatePart(GetInterval(), [dte])

3) yeah it should. this is where I would get the option of viewing the reports in either daily/weekly/quarterly view, right? how can I pursue? Do you know what I mean?

 

[umair434]

oh wait! i think i get it. i will post you back

 

[projecttoday]

You might want to have a look at http://www.utteraccess.com/forum/Monthly-Quarterly-Yea-t1957959.html&hl=quarterly

 

[umair434]

SELECT tblActivities.dte, Sum(tblActivities.[Prewab Report Available]) AS [SumOfPrewab Report Available], Sum(tblActivities.[Prewab Report Actioned]) AS [SumOfPrewab Report Actioned]

FROM Range, tblActivities INNER JOIN tblEmployees ON tblActivities.EmployeeID = tblEmployees.EmployeeID

WHERE (((tblEmployees.EmployeeID) Like [forms]![main]![combo] & "*") AND ((Range.rangeID)=[forms]![main]![combo1]))

GROUP BY tblActivities.dte

HAVING (((Sum(tblActivities.[Prewab Report Available]))<>0) AND ((Sum(tblActivities.[Prewab Report Actioned]))<>0));

here is the sql for my query. Any selection from combo1 (range combo box) gives me the same results because I don't have it set up. when I open the report, I get the same result as the query - that means it is not being grouped by "ww".

How do I make that happen? Also, how do I the query to show me "ww" results when I select "weekly" from the combo box, and show me quartely results when I use "q" from the combo box?

thanks again. much appreciated

 

[vbaInet]

You might want to have a look at http://www.utteraccess.com/forum/Monthly-Quarterly-Yea-t1957959.html&hl=quarterly

I'm not sure if your sample does it but the aim here is to be able to GROUP BY the datepart too, not just filter. So if the user wants to Force a New Page per group it is possible too. Does your sample do that?

From what I read, your sample uses OpenArgs and unless you change the Grouping in code then you won't be able to achieve the above.

 

[umair434]

I tried the sample, but couldn't get it working.

any ideas of how to get this working, vbaInet?

thanks

 

[vbaInet]

It's nothing to do with your SQL statement. As long as your sql provides the raw data that needs to be Grouped, then everything will fall into place nicely.

So you need to do something like:

Select Case Nz(Me.ComboBoxName, "")
    Case "Daily"
        SetInterval "d"
    Case "Weekly"
        SetInterval "ww"
    Case "Quaterly"
        SetInterval "q"
End Select

DoCmd.OpenRert ... etc

Does that make sense?

 

[umair434]

yes. That makes sense. Sorry for the late reply.

anyways, I tried implementing what you had suggested. I made a copy of my database and just wanted to experiment it with 1 query and 1 report, but it gives me an error saying "the expression is typed incorrectly or too complicated to be evaluated"

I am posting the "simplified" database here. Would it possible for you to look it through? I have to finish this in 4 more days and then I will be done with this project -

thank you so much

 

[umair434]

I think I was referring to the wrong column in the combo box. I think I see what it is. I will post you back. thanks!

 

[umair434]

ok! I spent last 2 hours or so trying to fix it. the report opens, but I can't group it right.

when I click "weekly" from the combo box - the report should display something like

week 1 - - - 2010 - - - SUM of data (for that week and 2010)

------------ 2011 - - - SUM of data (for that week and 2011)

I'm uploading a revised version here. Please let me know what can I do with this? :S