Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    report based on query with multiple conditions (Acces 97 - SR1)

    Hi all,

    I have a database with many tables, containing the data on "accidents at work" that employees have suffered from.
    I would like to create a query that is based on the following conditions :
    - a time period should be entered (ie from 01 jan 2004 until 06 dec 2004) by a user
    - only those accidents that caused "days not being able to work" should be taken in account (There is a field containing the number of days)

    On this query I would like to generate a report that gives me a list of all "accidents at work" that caused "days not being able to work" for the entered period of time, listing the date and employee involved, and giving me a total of the "days not being able to work". But the "days not being able to work" may not be taking into account for the "accidents at work" identified as "happened on the way to and from work" (field weg_werk in tbl_AANGIFTE_ONGEVAL), although the report does need to mention the date and employee involved for those.

    I have added a stripped down database (it is in Dutch I'm afraid) where the the accident details are in tbl_AANGIFTE_ONGEVAL, the employees are in tbl_PERSONEEL. The table tbl_AANGIFTE_ONGEVAL is linked to the other tables, but I think this is not important for this problem.

    In the added database this means I would get a list with 2 records on it : 1 and 3. Record 2 has no "days not being able to work" and record 4 has the field weg_werk checked. The report also list the total of 9 "days not being able to work" (4 + 5).

    As I mentioned before, the database is set up in Dutch, so I guess this more or less asks for HansV have a look at it <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> . Of course this does not mean that I do not appreciate any help from any other lounge member. <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

    MTIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    Don't forget <!profile=Francois>Francois<!/profile>! Sint Niklaas isn't very far from Kontich.

    You don't need a query for this, although it would be possible. In the attached version, I have created a query that does not contain any selection criteria. The report is based in this query, and if it is opened by itself, it will display all records.

    There is a form in which the user can enter the start and end dates. Clicking OK will open the report with a where-condition. See the code behind the OK button to see how the where-condition is constructed.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    Dear Hans,

    you are quit right ! I should have thought about my fellow countryman Fran

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    I'm sorry, you did mention that in your post, I didn't read it carefully enough. Here is a new attempt.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    Thanks Hans,

    It works (I did not have a doubt about it !). <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    But, in your <post#=433442>post 433442</post#> you said that I should take a look at the code behind the OK button. I did, but I could not find this where-condition you mentioned. The code I see is :

    Option Compare Database
    Option Explicit

    Private Sub cmdCancel_Click()
    DoCmd.Close acForm, Me.Name
    End Sub

    Private Sub cmdOK_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport "rpt_Overzicht", acViewPreview, , _
    "datum_ongeval BETWEEN #" & Format(Me.txtDateFrom, "mm/dd/yyyy") & _
    "# AND #" & Format(Me.txtDateTo, "mm/dd/yyyy") & "#"
    DoCmd.Close acForm, Me.Name
    Exit Sub

    ErrHandler:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub


    Am I looking in the wrong place ? <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    I am not so familiar with programming Access, you know.
    Could you please explain this a little bit more in detail ?

    MTIA

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    DoCmd.OpenReport "rpt_Overzicht", acViewPreview, , _
    "datum_ongeval BETWEEN #" & Format(Me.txtDateFrom, "mm/dd/yyyy") & _
    "# AND #" & Format(Me.txtDateTo, "mm/dd/yyyy") & "#"


    The part in Italics is the where-condition argument to DoCmd.OpenReport. It restricts the records to those whose datum_ongeval is within the range specified on the form. The other condition has been placed in the query.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    OK Hans,

    I understand now. I am trying to follow each bit of code in the query, the form and the report, but I can't figure out how you started on solving my problem. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Would you mind explaining me (and maybe other loungers "who always wanted to know, but were afraid to ask ... <img src=/S/smile.gif border=0 alt=smile width=15 height=15> ) how you came to the solution to the problem. Where did you start ? With the Report, the Form or the Query ? What is the most logical way to tackle a problem like mine ?

    Any reply would be most appreciated.
    Best regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    I started by looking at your tables, to find out where the data are stored that you want to display in the report. It turned out we needed only the main table tbl_AANGIFTE_ONGEVAL and the employees table tbl_PERSONEEL (to get the employee name). For the latest version I decided to put the criteria for dagen_verlet and weg_werk in a query, since they are static. I also put a calculated field in the query, to set the number of missed days to 0 if the accident occurred on the way to or from work.
    Next, I used the report wizard to create a report based on the query. I simplified the layout (I don't like the formatting that the Wizard creates), and added a text box in the report footer to sum the days;its control source is
    =SUM([verlet])
    I decided that a form was more user-friendly than parameter prompts to get the date range, so I created a simple form with two text boxes and two command buttons. The code behind the OK button is fairly standard. Sample databases such as the Northwind database that comes with Access have similar forms.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    Hans,

    Thank you very much for your clear explanation. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    I noticed that you refer to the Northwind database, but I do not have this database installed. And I do not have access to the original Office CD, it is kept by the IT/IS department, and they are reluctant to give the CD to me or even install the database on my computer.... <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

    Is there a way to get hold of the Northwind database (for Access 97), and if so, does there exist some kind of tutorial based on the Northwind database ? Or is it just the standard Help-file that is referencing to the Northwind database <img src=/S/question.gif border=0 alt=question width=15 height=15>

    Best regards

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    Thank you for the information Hans.

    Just to let you know : the link to the Sample queries is no longer valid. It connects to the Micro$oft site, but says that the page requested is not available. Do you know were the sample queries can be found ? All the other links were valid.

    Best regards

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    I put in one slash too many in that link. I have corrected it, so it should work now. Sorry for the confusion.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: report based on query with multiple conditions (Acces 97 - SR1)

    (Edited by HansV to correct one of the links.)

    You can download the English language version of the Northwind database here. It comes with its own help file. Microsoft doesn't seem to offer the Dutch/Flemish language version (Noordenwind.mdb) for download any more.

    There are other useful downloads for Access 97, e.g. Sample queries, Sample forms and Sample reports

Posting Permissions

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