Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: Generating reports

    I made a db with several tables, queries and forms.
    All I made so far is only for data-entry which is done by our HR-department.

    All the other users are only supposed to see the data they need.
    So I was thinking of creating a form on which they can select the desired fields, add criteria to this fields for filtering, sort and group the results and at last generate a report based on all the selections.

    BUT... at the moment I have no idea how I can work this out.
    Does anyone have an example?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Sorry but i thing that there is nothing about that, the must similar me by is the wizard for reports and prepare the user to use.
    Saludos
    Norberto

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Generating reports

    Originally posted by artemide
    I made a db with several tables, queries and forms.
    All I made so far is only for data-entry which is done by our HR-department.

    All the other users are only supposed to see the data they need.
    So I was thinking of creating a form on which they can select the desired fields, add criteria to this fields for filtering, sort and group the results and at last generate a report based on all the selections.

    BUT... at the moment I have no idea how I can work this out.
    Does anyone have an example?

    Thanks
    Do you mean, you want to create a Report wiz? Why you have a built in.

  4. #4
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Generating reports

    Originally posted by hammbakka
    Do you mean, you want to create a Report wiz? Why you have a built in.
    Can I start the built-in wizard using VB?
    How?

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Generating reports

    Originally posted by artemide
    Can I start the built-in wizard using VB?
    How?
    Good question, really I am not sure but try and look up DoCmd.RunCommand

  6. #6
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Generating reports

    Originally posted by hammbakka
    Good question, really I am not sure but try and look up DoCmd.RunCommand

    I tried using the wizard (not from within VB).
    Therre you can select the fields you want, group and sort the results as you wish, but as far as I can see there's no possibility to filter fields depending on their value.
    Or am I wrong?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What about this????

    Try making reports supported by the different selection criteria available and when the user hits on that combination run the specified report ...

  8. #8
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by M Owen
    What about this????

    Try making reports supported by the different selection criteria available and when the user hits on that combination run the specified report ...
    You mean create predefined reports?
    This is not possible because the number of combinations is too much.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    That is what I meant. How many controls are you talking about? THink about it ... How about "most common/popular" reports? Are you allowing for pulling from multiple tables? Allowing for join operations? Making "canned" reports that all that is pumped in is parameters would be a way to go ... What about splitting up the selections? Have multiple intefaces for generating the different reports?

  10. #10
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by M Owen
    That is what I meant. How many controls are you talking about? THink about it ... How about "most common/popular" reports? Are you allowing for pulling from multiple tables? Allowing for join operations? Making "canned" reports that all that is pumped in is parameters would be a way to go ... What about splitting up the selections? Have multiple intefaces for generating the different reports?
    My db contains all the jobdescriptions of our workers and all the course they followed till now.
    To make it easy I made 2 queries : one containing all the data concerning the jobs and the other containing all the data concerning the courses.
    My biggest problem is to find an easy way to filter the data :
    For example :
    I want all workers from team1 who graduated in mechanics OR (all the people from team2 who graduated in physics AND finished a safety course succesfully)

    My main problem is how I can create a userfriendly interface where the users can add ORs and ANDs as much as they wish.
    You can compare it with tthe querybuilder in Access.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I don't see a real problem with this ... What you do is determine what is to display on each report depending on the selection criteria ... The user will not select the fields to be displayed (reported on) You can setup your interface to daisy-chain comboboxes of selection criteria with other comboboxes with logical operations ... You will have to construct AND validate that the resultant SQL statement is valid ...

  12. #12
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Generating reports

    Originally posted by artemide
    I made a db with several tables, queries and forms.
    All I made so far is only for data-entry which is done by our HR-department.

    All the other users are only supposed to see the data they need.
    So I was thinking of creating a form on which they can select the desired fields, add criteria to this fields for filtering, sort and group the results and at last generate a report based on all the selections.

    BUT... at the moment I have no idea how I can work this out.
    Does anyone have an example?

    Thanks
    I think I know where you're coming from on this. I have set up several search forms that do like you are describing. What I did was to create a query that contained all the fields that you would want to display on your report/s in the grid. As long as the tables contained in the query have the fields that you want to filter you can use them in Where clauses.

    In my search forms I set up two combo boxes. Since many of the fields that I wanted to filter were from lookup tables, I would set the first combo box to a fixed list of items that the user would want to filter and when one of them was selected, the other combo box would then contain the items that applied to that field. I also included freeform text searches. By selecting text for a text field or a memo field the user could add whatever text they wanted in the second combo box.

    In code, I incrementally created an SQL statement starting with the selection of all from the query that I had created originally. Each criterea that was added continued to build the where clause of the original statement. As the statement was building, I stored each subsequent addition to the statement in an array. This allowed me to create BACK and FORWARD buttons to go back to previous point in the statement. The results of each addition to the statement were added to a listbox and a count of the records retrieved was stored in a label control. For my own purposes, I also stored the building SQL statement in a textbox to view my syntax and give the user an idea what was happening.

    The form became quite complex actually. Not in the sense that each part was difficult to accomplish but that there were a lot of search criterea and a lot of pages of text to look for errors in.

    Probably more than you want but thought it might give you some ideas.

    Gregg

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This is where I'm coming from ... Following is actual code for my filtering mechanism:

    Code:
    Function ConstructFilter() As String
        Dim FilterString As String
        
        FilterString = " WHERE "
        If SearchTxt.Value & "" <> "" Then
            FilterString = FilterString & "("
            Select Case SearchOnGroup.Value
                Case 1  ' Vendor
                    FilterString = FilterString & "Suppliers.Name"
                Case 2  ' Item Description
                    FilterString = FilterString & "Items.Description"
                Case 3  ' P.O. #
                    FilterString = FilterString & "[Purchase Orders].[PurchaseOrder #]"
                Case 4  ' Tracking #
                    FilterString = FilterString & "[Purchase Orders].[Tracking #]"
                Case 5  ' Blanket #
                    FilterString = FilterString & "[ItemsSupplier].[Blanket PO#]"
                Case 6  ' W.O. #
                    FilterString = FilterString & "[PO Items Detail].WorkOrderNumber"
            End Select
            If MatchGroupBox.Value = 1 Or SearchOnGroup.Value = 3 Or SearchOnGroup.Value = 4 Or SearchOnGroup.Value = 5 Then ' Exact Match or P.O., Tracking #, or Blanket #
                If SearchOnGroup.Value = 4 Then
                    FilterString = FilterString & "=" & SearchTxt.Value
                Else
                    FilterString = FilterString & "='" & SearchTxt.Value & "'"
                End If
            Else ' Partial Match
                FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
            End If
            FilterString = FilterString & ")"
        End If
        If FilterString = " WHERE " Then FilterString = ""
        
        ConstructFilter = FilterString
    End Function
    I have multiple selection criteria for the user to select from and I create the appropriate query string (you can't see ..) to be run ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •