Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping problem (Access03)

    I have the following code that almost works and I am getting dizzy from going around in circles. I want to report out one line for each different credential listing, RN, LPN, Nursing Assistant, etc., and the number per that credential for the month. I also need to be able to date limit the return.

    ' To capture class dates
    strWhere3 = "(DateOfClassStart " & [Forms]![frmReports]![txtCriteria] & "#" & _
    [Forms]![frmReports]![txtRosterStart] & "#)"

    'To capture class registrations between two dates
    strWhere4 = "(DateOfClassStart Between " & "#" & [Forms]![frmReports]![txtRosterStart] & _
    "# And #" & [Forms]![frmReports]![txtRosterEnd] & "#)"

    'Compile report based on criteria set for date reporting
    If Forms!frmReports!frmDates = 7 Then
    strWhere = ""
    ElseIf Forms!frmReports!frmDates = 6 Then
    strWhere = strWhere4
    Else
    strWhere = strWhere3
    End If

    stDocName = "rptWhoOrientedCount"
    DoCmd.OpenReport stDocName, acPreview, , strWhere

    I have one make-table query (I used the query grid, becaue I could not get SQL to deal with the DatePart, ignorance on my part.)

    SELECT tblLearners.Credential, tblCombinedRegistration.DateOfClassStart, tblCombinedRegistration.ClassName, DatePart("m",[DateOfClassStart]) AS [Month], Format([DateOfClassStart],"mmmm") AS Mon INTO tblWhoOriented
    FROM tblCombinedRegistration INNER JOIN tblLearners ON tblCombinedRegistration.LearnerID = tblLearners.LearnerID
    ORDER BY DatePart("m",[DateOfClassStart]);

    The report uses the following query, again on a grid.

    SELECT tblWhoOriented.Month, tblWhoOriented.Mon, tblWhoOriented.Credential, Count(tblWhoOriented.Credential) AS CountOfCredential, tblWhoOriented.DateOfClassStart
    FROM tblWhoOriented
    GROUP BY tblWhoOriented.Month, tblWhoOriented.Mon, tblWhoOriented.Credential, tblWhoOriented.DateOfClassStart
    ORDER BY tblWhoOriented.Month;

    My problem if I use the DateOfClassStart on the grid I get multiple entries per credentials. For example if I run four classes in the month for RN and have 6 people I get a separate listing for each class such as 1,2,2,1. What I want to see is RN 6. If I take DateOfClassStart off the grid I get all classes unrestricted by date.

    Any and all help would be appreciated. Thank you.

    Fay

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

    Re: Grouping problem (Access03)

    Which field is RN?

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping problem (Access03)

    Credential

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

    Re: Grouping problem (Access03)

    You could group the report on Credential (in the Sorting and Grouping window), and put the controls in the group header instead of in the detail section. You can use text boxes with control source

    =Count([FieldName])

    or

    =Sum([FieldName])

    depending on what you want to display.

Posting Permissions

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