Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reports (access2000)

    reports are my most serious weakness and I am now trying to put together a difficult one-- without the search is there another way to find previous posts on reports? I know sub reports can be used; but I seem to have too many subdivisions of the data. I am wondering if updating to Office XP would help. I have the data on the students by age in months-- now I want to pull it up in 4 groups 0-12 months; 13-24 months 25-36 months and 37-60 months--- since I also want to see how many of each group passed or failed the test (and a few other subdivisions) and the retest-- and how many totally were tested in each group; I am fairly sure I need at least 4 queries. I think I can do the queries; but cant figure out how to combine them on a report-- any help you can give or just to point me in the right direction to find the info will be appreciated

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports (access2000)

    I would write a public function, and include it in the query that's used for the recordsource of the report. Somehting like this
    (air code)

    Public Function MonthGroup (plMonth as Long) as Integer

    MonthGroup = int(plMonth / 12 ) + 1
    if plMonth > 36 then MonthGroup = 4
    if plMonth <= 0 then MonthGroup = 1

    End Function

    This will return a number from 1 to 4 depending on the value of the month that is supplied to the function.


    Next, add a new column to the query that is the recordset for the report. The column would be entered as:
    StudentMonthGroup: MonthGroup(fldMonth)

    where fldMonth is the name of the field that contains the student's age in months. Open the query in the conventional manner, and you should see a new column named "StudentMonthGroup" which will contain the values from 1 to 4 as required.

    Now, go back to your report and use the new column named StudentMonthGroup in the Sorting and Grouping dialog. Each student will automatically be assigned to the correct group. Create subtotals for the groups as required.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports (access2000)

    thank you for your reply-- will try this answer tomorrow when I have access to the data

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports (access2000)

    getting syntax error-- so sleepy tonight am not following well-- plMonth refers to? fldMonth is clear

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

    Re: reports (access2000)

    plMonth is the argument to the function proposed by Jackson. You should create a standard module - switch to the Modules tab of the database window and click New. Then type or paste the MonthGroup function from Jackson's reply into the module. After that, you will be able to use this function in queries etc.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports (access2000)

    did that -- but am getting syntax error and since I didnt understand the reference "plMonth"-- was not sure how to proceed

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: reports (access2000)

    Post your query here so we can have a look.
    Also post the function if you have changed it.

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports (access2000)

    on my way out the door-- will do so as soon as I return this afternoon

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports (access2000)

    here is the query-- just copied and pasted the function so I think that is right <img src=/S/confused.gif border=0 alt=confused width=15 height=20> -- this database is getting more complicated by the day as the people keeping the records spend lots of hours changing their minds-- but the object is to see if we , as a coalition, can get better daycare for kids.
    SELECT Count(tblASQtesting.ChildID) AS CountOfChildID, MonthGroup([TestAge]) AS StudentMonthGroup, qryResultAsq.Result, tblASQtesting.Communication, tblASQtesting.GrossMotor, tblASQtesting.FineMotor, tblASQtesting.ProblemSolving, tblASQtesting.PersonalSocial, tblASQtesting.[This is Retest]
    FROM tblASQtesting INNER JOIN (TrackingSystemInfo INNER JOIN qryResultAsq ON TrackingSystemInfo.ChildID = qryResultAsq.ChildID) ON tblASQtesting.ChildID = TrackingSystemInfo.ChildID
    GROUP BY MonthGroup([TestAge]), qryResultAsq.Result, tblASQtesting.Communication, tblASQtesting.GrossMotor, tblASQtesting.FineMotor, tblASQtesting.ProblemSolving, tblASQtesting.PersonalSocial, tblASQtesting.[This is Retest]
    ORDER BY MonthGroup([TestAge]), qryResultAsq.Result;

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

    Re: reports (access2000)

    You wrote that you get a syntax error. As a test, switch to the Visual Basic Editor (Alt+F11), and activate the Immediate window (Ctrl+G). Type

    ? MonthGroup(25)

    and press Enter. You should see 3 as a result. Is that correct? If you get an error message, what piece of code is highlighted?

  11. #11
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports (access2000)

    Hello Hans-- I followed your instruction did not get 3-- got error-- the first line is highlighted in yellow and the last line is written in red
    I had run in to change clothes and then off to teach and am just getting back now-- (the reason I didnt respond earlier) Hans? do you ever sleep? LOL seems you reply to things written at all hours of day and night. Thank you for trying to help here.

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

    Re: reports (access2000)

    Perhaps you made a mistake while copying the function - even the smallest typo will prevent code from running.

    Check very carefully against the code below, or delete the code you have now, then copy and paste the code from this post into your module:

    Public Function MonthGroup(plMonth as Long) as Integer
    MonthGroup = Int(plMonth / 12 ) + 1
    If plMonth > 36 Then MonthGroup = 4
    If plMonth < = 0 Then MonthGroup = 1
    End Function

    The next to last line contains a "smaller than" sign < followed by an "equals" sign = (some browsers don't display that correctly)

    If you still get an error, what is the exact text of the error message you get, and exactly which part is red?

  13. #13
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports (access2000)

    That was it !!!
    I had copied and pasted and missed that it put ; = instead of < = I should have printed it and typed it in --then I might have caught it myself-- thank you so much
    Now to do some major study on sub reports-- I need to figure the way to display all of this--thank you again

Posting Permissions

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