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

    reports (access2000)

    I seem unable to think these days-- can anyone give me a push in ther right direction? I have 4 queries for 4 different age levels Infant (0-11mo) Toddler(12-23 mo) Twos(24-26 mo) and preschool(36-60 mo) I need to have a report that takes information from all of these and displays the totals. (actually, I need to show the number who were given the test during a time frame(my queries work here) and then need the number who did not pass-- That would mean 8 queries for each report unless I am not thinking correctly. I have no idea how to get all the results to show in one report.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: reports (access2000)

    Are all four queries off one table? If yes, then just make a report based on the table that is grouped by the different ages, rather than using the queries. Does this help?

  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)

    actually, there are many tables-- child info; table for each kind of testing and one for health records another for the daycare where the children attend and another for the family info-- the main form uses a query that takes info from the centers; child and family and then uses buttons to open the forms for each kind of testing. I do fairly well at organizing the data-- setting up forms and queries; but am weak on reports [img]/forums/images/smilies/sad.gif[/img] (remember, I am only self taught so I could develop the database to track all the records for our resource center and this daycare one started out to be a database to help a friend who was overwhelmed with records for an agency that tracks kids in daycare) -- now, my non profit is receiving some funding from the daycare people because I help them with the database. But I feel stuck for the moment. Do you think it could have something to do with the fact that MY agency is in the middle of providing Christmas for 1500 kids [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: reports (access2000)

    I assume that all the children of all ages start from one main table. You could write a query that would have all the children of all ages, and all the info that you need for the report. Then you could group and sort the report as needed. Does this help? If not maybe post a version of the database with very little data that has been scrambled, so we can help

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

    Re: reports (access2000)

    The child table has all the children; the query that the entry formm is based on has information from child, family, and center information. Each of the testing areas and the progress notes are tables and have forms of their own-- all the information pertaining to each child is related by the social security number. Do I write 4 or 8 individual reports and then combine them somehow? If I do that, will the person pulling up ther report be able to enter the dates only once (so that these reports can be universal and can be about any time frame that is entered)

  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)

    I just thought of something-- is there a way to write in VBA a condition under age in months to divide the group into the age groups I need-- group 0-11 mo; 12-23mo; 24-35mo and 36-60 mo
    I dont actually need the months designation since the database already calculates the age in months
    Then the query itself would sort the children and count the totals-- oh , please one of you bright people; tell me this can be done!

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

    Re: reports (access2000)

    You could add a field to the query that calculates the age group. You wrote that you already have the age in months. Let's say this field is called AgeMonths. The definition for the new field could be:

    AgeGroup: Switch([AgeMonths]<12,0,[AgeMonths]<24,1,[AgeMonths]<36,2,[AgeMonths]<60,3,[AgeMonths]>=60,4)

    which will return a number, or if you prefer to see the descriptions of the age groups:

    AgeGroup: Switch([AgeMonths]<12,"0-11 mo",[AgeMonths]<24,"12-23 mo",[AgeMonths]<36,"24-35 mo",[AgeMonths]<60,"36-59 mo",[AgeMonths]>=60,"60+ mo")

    You can group and count on this field.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: reports (access2000)

    If all the reports have the same information in them and the only difference is the age, then write 1 report and group it by the different ages. You can write a function that can be used in a query to seperate the ages as you desire. Use that function as one of the fields in the query and use that query as the basis for the report grouped on that field.
    Below is a function that divides into three groups under 1 month , till ten years, and adult. You can modify it to your needs easily. There are some issues in this code, but modify it as you need.



    Public Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
    'Purpose: Return the Age in years or days.
    'Arguments: varDOB = Date Of Birth
    ' varAsOf = the date to calculate the age at, or today if missing.
    'Return: depends on result if less than a year returns days
    Dim dtDOB As Date
    Dim dtAsOf As Date
    Dim dtBDay As Date 'Birthday in the year of calculation.
    Dim intResult As Integer


    On Error GoTo HandleErr

    Age = Null 'Initialize to Null

    'Validate parameters
    If IsDate(varDOB) Then
    dtDOB = varDOB

    If Not IsDate(varAsOf) Then 'Date to calculate age from.
    dtAsOf = Date
    Else
    dtAsOf = varAsOf
    End If

    If dtAsOf >= dtDOB Then 'Calculate only if it's after person was born.
    dtBDay = DateSerial(year(dtAsOf), month(dtDOB), day(dtDOB))
    intResult = DateDiff("d", dtDOB, dtAsOf) + (dtBDay > dtAsOf)

    End If
    End If

    If intResult < 31 Then
    Age = DateDiff("d", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
    Age = "Infant"
    ElseIf intResult < 3650 Then
    Age = DateDiff("m", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
    Age = "Child"
    Else
    Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
    Age = "Adult"
    End If

    ExitHere:
    Exit Function

    ' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
    ' Automatic error handler last updated at

  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)

    almost! but I want it to show the totals for the whole month or whole quarter depenting on what dates are entered and it still breaks them down by date --here is the query
    SELECT Switch([Age]<12,"0-11 mo",[Age]<24,"12-23 mo",[Age]<36,"24-35 mo",[Age]<60,"36-59 mo",[Age]>=60,"60+ mo") AS AgeGroup, Count(TrackingSystemInfo.ChildID) AS CountOfChildID
    FROM TrackingSystemInfo INNER JOIN tblASQtesting ON TrackingSystemInfo.ChildID = tblASQtesting.ChildID
    GROUP BY tblASQtesting.DateOfScore, Switch([Age]<12,"0-11 mo",[Age]<24,"12-23 mo",[Age]<36,"24-35 mo",[Age]<60,"36-59 mo",[Age]>=60,"60+ mo"), tblASQtesting.[This is Retest]
    HAVING (((tblASQtesting.DateOfScore) Between [enter beginning date] And [enter ending date]) AND ((tblASQtesting.[This is Retest])=False));

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

    Re: reports (access2000)

    Well, GROUP BY tblASQtesting.DateOfScore means that you group by individual dates. You state that you want to "show the totals for the whole month or whole quarter depenting on what dates are entered". I don't think you can do that in one query. You either group by month, or you group by quarter. Moreover, you haven't indicated how the distinction should be made.

    If you want to group by month, you should add YearOfScore: Year(tblASQtesting.DateOfScore) and MonthOfScore: Month(tblASQtesting.DateOfScore) to the query, and group on those instead of on tblASQtesting.DateOfScore itself (the latter should have "Where" in the Total row).

    If you want to group by quarter, you should add YearOfScore: Year(tblASQtesting.DateOfScore) and QuarterOfScore: (Month(tblASQtesting.DateOfScore)+2)3 to the query, and group on those instead of on tblASQtesting.DateOfScore itself (the latter should have "Where" in the Total row).

    In all cases, you should change the Total option for tblASQtesting.[This is Retest] to Where; there seems to be no need to group on it.

  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)

    THATS IT!! I always forget about "where"
    SELECT Switch([Age]<12,"0-11 mo",[Age]<24,"12-23 mo",[Age]<36,"24-35 mo",[Age]<60,"36-59 mo",[Age]>=60,"60+ mo") AS AgeGroup, Count(TrackingSystemInfo.ChildID) AS CountOfChildID
    FROM TrackingSystemInfo INNER JOIN tblASQtesting ON TrackingSystemInfo.ChildID = tblASQtesting.ChildID
    WHERE (((tblASQtesting.DateOfScore) Between [enter beginning date] And [enter ending date]) AND ((tblASQtesting.[This is Retest])=False))
    GROUP BY Switch([Age]<12,"0-11 mo",[Age]<24,"12-23 mo",[Age]<36,"24-35 mo",[Age]<60,"36-59 mo",[Age]>=60,"60+ mo");

    it works just fine-- you have saved my sanity--now, I think I can finish the entire thing and have the reports up and running-- will do some research on sub reports <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Hey, I told you that reports were my weakest area (and didnt do so well with the query either this time)

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

    Re: reports (access2000)

    ok--it works--had to make a few modifications as I did not want any children over age 60 months-- sooo glad you reminded me about "where" --- now, one more question and I will stop bothering you-- BTW cant tell you how much this has reduced my stress level-- I need to have the report show all the children needing testing (field asqtesting) for each age group and the number tested (which is the SAME number!--hey , I didnt write the reporting system) and then the number at risk-- which is the number who failed. When I use this query to show failed-- it splits the totals -- do I need two queries for that report or can the report itself add the two back together? <img src=/S/smile.gif border=0 alt=smile width=15 height=15> bet you will be glad when this project is done.
    SELECT Switch([Age]<12,"0-11 mo",[Age]<24,"12-23 mo",[Age]<36,"24-35 mo",[Age]<61,"36-60 mo") AS AgeGroup, Count(TrackingSystemInfo.ChildID) AS CountOfChildID, tblASQtesting.ResultID
    FROM TrackingSystemInfo INNER JOIN tblASQtesting ON TrackingSystemInfo.ChildID = tblASQtesting.ChildID
    WHERE (((TrackingSystemInfo.Age)<61) AND ((tblASQtesting.DateOfScore) Between [enter beginning date] And [enter ending date]) AND ((tblASQtesting.[This is Retest])=False))
    GROUP BY Switch([Age]<12,"0-11 mo",[Age]<24,"12-23 mo",[Age]<36,"24-35 mo",[Age]<61,"36-60 mo"), tblASQtesting.ResultID;

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

    Re: reports (access2000)

    Sorry, I don't understand your question. Could you try again, a little less breathlessly?

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

    Re: reports (access2000)

    trying again [img]/forums/images/smilies/smile.gif[/img]
    When I developed the database for these folks, they told me what they wanted to get back in the way of reports; since then, new director, they want completely different reporting. As children requalify for subsidised daycare, they are tested developmentally and they have certain health records that are needed. I had allowed for keeping all this information within related tables so I think I have all the information needed. Now, I need to have VERY user friendly reports. ( the two girls who enter data barely know how to turn on the computer) The Daycare overseerer is the one that I am working for. (they are "paying me" by giving money to the non-profit agency that I direct (very needed funds if we are to keep going)

    The report sheet they want me to fill with data is a grid with spaces for those 4 age groups-- they want to know the number of children needing testing (which happens to be the same number as the number who are tested so is useless information) and the number "at risk" which is the number who failed the first test. So I need the total number tested for each age and then the number who failed for each age. There are several more other pieces of information I will need to get into the report; but if I know the right way to get those two (three if you count the same figure twice) , then I think I can do all the rest.

    Should I have two queries? one to get the total children tested? and one to get the number failed? (when I changed the query to get number failed, it obviously split the total) OR can the report feature add the two figures back together in the report and I will only need the one query? [img]/forums/images/smilies/smile.gif[/img] oh yes, and if it will add them back, how do I set it up in report?

    Was that stated any better?

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

    Re: reports (access2000)

    You could turn your query into a crosstab query (from the Query menu). Set the Age Group as row heading, the ResultID as column heading, and the count of ChildID as value. You can then add another instance of ChildID, set the Total option to Count, and the Crosstab option to Row Heading. This will display the total count. So you'll get a count of failed, a count of passed, and a total count.

Page 1 of 2 12 LastLast

Posting Permissions

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