Design issues 1

[IknowMe]

I am working on form to allow user selections for reports. This is my first attempt and I am strugling with design issues and would like some help from those of you with more experience so I can avoid any pitfalls obvious to you.

I'm pretty comfortable with the setup below when my user identifies a specific tool (selected from combobox on form populated by query of unique entries in the tool column). But not clear as to the best aproach when one is not selected and the user would like to see totals for all tools. What would be the best way to to approach this? Stick with my sub and add the programming for looping through a query for each tool? I'm trying for a report that looks something like this

Area (org/off/div/bra) Tool1Name Tool2Name ect.
1/2/3/4 UsageSum ect. ect.


Private Sub cmdReport_Click()

Dim stReport As String
Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb


'If single tool is used we want to use single page report for all areas or as specified
If vTool <> "" Then
stReport = "SELECT Sum(Usage) AS Total FROM ComeTogether WHERE Tool='" & vTool & "'"
If vOrg <> "" Then
stReport = stReport & " and Org='" & vOrg & "'"

'Need to select an Org to break out by Office
If vOffice <> "" Then
stReport = stReport & " and Office='" & vOffice & "'"

'Need to Select an Office to break out by Division
If vDivision <> "" Then
stReport = stReport & " and Division ='" & vDivision & "'"

'Need to Select an Division to break out by Branch
If vBranch <> "" Then
stReport = stReport & " and Branch ='" & vBranch & "'"

'You get the point
If vSection <> "" Then
stReport = stReport & " and Section ='" & vSection & "'"
End If
End If
End If
End If
End If

'If date range is selected through calander use it
If Me.stD <> "" And Me.Edat <> "" Then 'Dates selected
stReport = stReport & " and ((([ComeTogether].Date)BETWEEN #" & Me.stD & "# AND #" & Me.Edat & "#))"
End If

Set db = CurrentDb
Set rs = db.OpenRecordset(stReport)

'
'set variable for report with sum of usage for selected tool
'ToBeDetermined = rs(0)

'Multiple tools (by omission of selection) needs multi page dynamic report for all tools and all areas
'If no tool selection all area data must be considered
Else
'would like to create a list of unique from tool data in query [ComeTogether].Tool
For Each tool

stReport = "SELECT Sum(Usage) AS Total FROM ComeTogether WHERE Tool='" & ToolReference & "'"


'If date range is selected through calander use it
If Me.stD <> "" And Me.Edat <> "" Then 'Dates selected
stReport = stReport & " and ((([ComeTogether].Date)BETWEEN #" & Me.stD & "# AND #" & Me.Edat & "#))"
End If

Set db = CurrentDb
Set rs = db.OpenRecordset()

'set variable for report with sum of usage for selected tool
'ToBeDeterminedArray(x,0) = ToolReference
'ToBeDeterminedArray(x,1) = rs(0)
Next

End If

rs.Close

End Sub

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[fneily]

Not knowing your table structure, could you use a Crosstab query or Pivottable to show the summary? Alot easier then coding.

 

[IknowMe]

I am unfamiliar with Crosstab query, but it looks promising. It's time to run but I'll research further tomorrow. Thanks for the suggestion fneily.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.