Grouping Question

Those rotten brackets in Access are always out to get you :)

SELECT SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.SType, Sum(SUBQ.CountOfRID) AS CountOfRID
FROM (

SELECT GetGroupIDName(IIf([Course] In ("Physics","Chemistry"),"Physics, Chemistry",[Course])) AS GroupID, IIf([Course] In ("Physics","Chemistry"),"Physics, Chemistry",[Course]) AS CourseGroup,  tblSType.SType, 1 AS CountOfRID
FROM ((tblMain INNER JOIN tblCourse ON tblMain.CourseID = tblCourse.CourseID) inner join tblSType on tblSType.STypeID = tblmain.STypeid)
WHERE (((tblMain.Appdate)>=[forms]![frmReportDateRange]![BeginDate] And (tblMain.Appdate)<=[forms]![frmReportDateRange]![EndDate]) AND ((NZ([tblMain].[CCon],False))=True))
GROUP BY GetGroupIDName(IIf([Course] In ("Physics","Chemistry"),"Physics, Chemistry",[Course])), IIf([Course] In ("Physics","Chemistry"),"Physics, Chemistry",[Course]),  tblSType.SType
UNION
SELECT GetGroupIDName(IIf([Course] In ("Physics","Chemistry"),"Physics, Chemistry",[Course])) AS GroupID, IIf([Course] In ("Physics","Chemistry"),"Physics, Chemistry",[Course]) AS CourseGroup,  tblSType.SType, Count(tblMain.RID) AS CountOfRID
FROM ((tblMain INNER JOIN tblCourse ON tblMain.CourseID = tblCourse.CourseID) inner join tblSType on tblSType.STypeID = tblmain.STypeid)
WHERE (((tblMain.Appdate)>=[forms]![frmReportDateRange]![BeginDate] And (tblMain.Appdate)<=[forms]![frmReportDateRange]![EndDate]) AND ((NZ([tblMain].[CCon],False))=False))
GROUP BY GetGroupIDName(IIf([Course] In ("Physics","Chemistry"),"Physics, Chemistry",[Course])), IIf([Course] In ("Physics","Chemistry"),"Physics, Chemistry",[Course]),  tblSType.SType

) AS SUBQ
GROUP BY SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.stype;

Oh, almost forgot....

GetGroupIDName

Needs to change to reflect either  ((NZ([tblMain].[CCon],False))=False  or  ((NZ([tblMain].[CCon],False))=True

So, might be easiest to create two
one to include  "AND ((NZ([tblMain].[CCon],False))=False " which is used for the second query (ie after the union)
one to include  "AND ((NZ([tblMain].[CCon],False))=True " which is used for the first query (ie before the union)

Otherwise the list of courseID is the same regardless...

Hmmmm... But more involved than it looks...

Can you please explain the importance and grouping of CCon
And why the union ?

What does that first query achieve ? Especially when we are overriding the count to be a 1...

Here are the results I can get :

GroupID      CourseGroup       SType      CountOfRID
ASL      Math              OnLine            1
ASL, STU      Math             OnLine            5
CFT      Eat             OnLine            1
LT, STU      GYM             OnCampus      12
STU      Math             OffCampue      2
STU      Math             OnCampus      18
STU      Physics, Chemistry       OffCampue      3
STU      Physics, Chemistry       OnCampus      19

mark_wills:
Your suggested new Query result was good.
Question:?
Quick question before closing this post.

With a slighly modified function in the module  (see the attached new sample db version 2 in the zip file), Can you assist with a query that will  list Physics separately with it's own count and Chemistry separetely with it's own count ?
As per the info you requested about the [CCon] logic, I have also attached word document that explains it (see the attached  zip file)
You said...
".....Needs to change to reflect either  ((NZ([tblMain].[CCon],False))=False  or  ((NZ([tblMain].[CCon],False))=True...

So, might be easiest to create two
one to include  "AND ((NZ([tblMain].[CCon],False))=False " which is used for the second query (ie after the union)
one to include  "AND ((NZ([tblMain].[CCon],False))=True " which is used for the first query (ie before the union)

If you could improve on the [CCon] logic for efficiency and accuracy, it would be great.
 
Regards
Bobby

GroupingSample---v2.zip

OK, think I have got it now....

Changes -
1) seperate out Chemistry and Physics
2) fix counters - count 1 for ccon = Yes and group and course and type is same, else count rows
3) no longer use union query
4) include a break by type


Query (new)

SELECT SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.SType, sum( SUBQ.CountOfRID ) AS CountOfRID
FROM (

SELECT GetGroupIDName(tblcourse.Course,tblmain.stypeid) AS GroupID,[Course] AS CourseGroup,  tblSType.SType,   iif( NZ([tblMain].[CCon],False)=True,-1,tblmain.rid) as ccongroup, sum( iif( NZ([tblMain].[CCon],False)=True,0,1)) + max( iif( NZ([tblMain].[CCon],False)=True,1,0))  AS CountOfRID, tblmain.groupid as individual_groupid
FROM ((tblMain INNER JOIN tblCourse ON tblMain.CourseID = tblCourse.CourseID) inner join tblSType on tblSType.STypeID = tblmain.STypeid)
WHERE tblMain.Appdate >=[forms]![frmReportDateRange]![BeginDate] And tblMain.Appdate<=[forms]![frmReportDateRange]![EndDate]
GROUP BY  GetGroupIDName(tblcourse.Course,tblmain.stypeid),[Course],  tblSType.SType,   iif( NZ([tblMain].[CCon],False)=True,-1,tblmain.rid), tblmain.groupid

) AS SUBQ

GROUP BY SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.stype;

Function (new)

Public Function GetGroupIDName(courseGroup As String, stypeid As String) As String
    Dim db As Database
    Dim rs As Recordset
    Dim sql As String
   
    sql = "SELECT tblcourse.Course AS CourseGroup, tblMain.GroupID,tblmain.stypeid " & _
    " FROM tblMain INNER JOIN tblCourse ON tblMain.CourseID = tblCourse.CourseID " & _
    " WHERE tblcourse.course=""" & Replace(courseGroup, """", """""") & """" & _
    " AND   tblmain.stypeid =""" & Replace(stypeid, """", """""") & """" & _
    " GROUP BY tblcourse.Course, tblMain.GroupID,tblmain.stypeid;"

   
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql)
   
    Dim i As Integer
    Dim returnString As String
    returnString = ""
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        For i = 0 To rs.RecordCount - 1
            If i <> rs.RecordCount - 1 Then
                returnString = returnString & rs!GroupID & ", "
            Else
                returnString = returnString & rs!GroupID
            End If
            rs.MoveNext
        Next i
    End If
       
    GetGroupIDName = returnString
End Function

GroupingSample-mw-ee.mdb

Mark:
Thank you for the additional posting. I will check the computation and report back to you my findings.
Problem
I conducted a thorough check on your very first query under ID:26138644 and fond a minor error.

To simulate it I place a different data in tblMain and ran your query. I found out that the reult for one of the groups was wrong.
GroupID                     CourseGroup        SType               CountOfRID
ASL, STU                       Math                   OnCampus            20 <------- This is Wrong. It should be 19.
In order to simulate the same problem, I have uploaded a new sample with different data for tblMain.

To test,
.............please type in (07/01/09 and 07/31/09 as Begin and End Dates)
 
IN order to facilitate your understanding of the problem, I have added an EXCEL file for the  breakdown of the "new tblMain"
and the only result that is wrong is:
 ASL, STU - Math, ONCAMPUS  (which I indicated on the EXCEL file in "red".

The test result counts in "deep blue" are correct.
Please see the attached zip file containing a "new db" with your query on it.,
...............please run "Qry_ID26138644" to simulate the problem.
 
I am not sure where the problem is...Is it in my module function or your Query?, I am not really sure.
Any further assistance on the subject will be appreciated.


Regards
Bobby

ProblemSample.zip

Mark:
Further explanation:
If you look at tblMain, it has the following:
Note:   This three records will count as "1" record
           for the following reasons:
          ***.  The 3 records all have similar characterristics
                   that is the GroupID, CourseID and STYpeID are similar
                  and the three records all have "Yes" indicated in their [CCon]

Also take note that recordID 43 does not belong to this group simply because the "STypeID" is different  - it's "CM" not the same as the other three records.

RID       GroupID       CCon       CourseID      STypeID      Appdate
3               ASL             Yes            MTH                ST            7/28/2009
5              ASL              Yes            MTH                ST            7/28/2009
28            ASL             Yes            MTH                 ST            7/8/2009
 
RID       GroupID       CCon       CourseID      STypeID      Appdate
43             ASL            Yes            MTH               CM               7/7/2009

This record will count as "1" record on it's own due to the above explantion - the "STypeID" is "CM" which is different
from the other three records with "ST" in their "STYPEID"
SUGGESTION:
I am of the impression that in order to get accurate count of the records with a query, the following table fields:
GroupID, CourseID and STypeID (WHERE [CCon] =True   "MUST"  be taken into consideration
 
What do you thinK?

Mark:
I just tested your last posted Query, the result is very similar to the problem I experienced in my first test of your first Query.:

GroupID                     CourseGroup        SType               CountOfRID
ASL, STU                       Math                   OnCampus            20 <------- This is Wrong. It should be 19.

Think you have missed out RID = 43 altogether in your spreadsheets...

For Oncampus Maths we have
RID 43   (Yes) ASL           (count as 1)
RID 32   (blank) ASL

RID  2   (blank) STU
RID  6   (blank) STU
RID  7   (blank) STU
RID 10   (blank) STU
RID 11   (blank) STU
RID 12   (blank) STU
RID 13   (blank) STU
RID 14   (blank) STU
RID 15   (blank) STU
RID 16   (blank) STU
RID 29   (blank) STU
RID 30   (blank) STU
RID 33   (blank) STU
RID 34   (blank) STU
RID 35   (blank) STU
RID 40   (blank) STU
RID 41   (blank) STU
RID 45   (blank) STU

So shouldnt that look like ASL,STU  Maths  OnCampus  20   ?

Have applied the updated queries (named QueryByType) and function (GetGroupIDNameType) to your "Problems MDB" including a new column 'V003' to show query version so we can keep in synch.
Have also included a pivot query showing raw counts. As it happens there is only one combo where multiple "Yes" count as if it were 1 (and not the three individuals) and that is "ASL Maths Online" which has been verified as working.

So, maybe there is still a misunderstanding on my part, or, hopefully RID 43 has been forgotten in your spreadsheet....


ProblemSample-.zip

mark:
Thanks. for your response.
I tested the sample and I am sure that the problem I reported earlier  for (ASL, SU), Math  ........... still exist.
GroupID        CourseGroup     SType              CountOfRID
ASL, STU       Math                   OnCampus              20       ..... '<---- It should be 19 based on the [CCon] logic.
I am briging forward the logic again from my notes under ID:26149758.
The following three records from tblMain should count only as "1" record "only because the 3 records have similar data in these fields:
GroupID      CourseID      STypeID  in addition to the fact that ([CCon] = True)

RID       GroupID       CCon       CourseID      STypeID      Appdate
3               ASL             Yes            MTH                ST            7/28/2009
5              ASL              Yes            MTH                ST            7/28/2009             <--------- All these three records will only count as "1" record
28            ASL             Yes            MTH                 ST            7/8/2009
***************
RID       GroupID       CCon       CourseID      STypeID      Appdate
43             ASL            Yes            MTH               CM               7/7/2009    <-------------- Thi will also count as a separate record because "STypeID = "CM"
**************
The above is where the problem is.
.... and I believe that these fields should be used in the counts (GroupID,CourseID, STypeID  WHERE ([CCon] = True))
This will definitely determine records with similar characteristics.

Yep kinda understand that... But where is RID 43 in your spreadsheet ? It is not in there anywhere and believe that is the difference between 19 and 20.

If you could please highlight in your spreadsheet where RID 43 has gone, either counted discretely, or part of a '1' count/group or whereever it is...


RID       GroupID       CCon       CourseID      STypeID      Appdate
3               ASL             Yes            MTH                ST            7/28/2009
5              ASL              Yes            MTH                ST            7/28/2009             <--------- they are counting as "1" record
28            ASL             Yes            MTH                 ST            7/8/2009
***************

Agree that the next row is different to the above and needs to be counted differently - in fact it is being counted and think it is missed in your calculations.

RID       GroupID       CCon       CourseID      STypeID      Appdate
43             ASL            Yes            MTH               CM               7/7/2009    <-------------- Yes, but it is not in your spreadsheet - meaning the 19 should be 20.
**************

mark:
It was left out by mistake.
Click the "tblMain" SHEET TAB in the spreadsheet you'll find the RID 43. Plus

tblMain table in the sample db however contains RID 43

So, are we OK with the count being 20 ?

If not, then, I do need a lot more help understanding what is happening. I have gone through half a dozen times manually, and keep getting 20.

Yes, I am aware that RID 43 is in tblmain in the database.

Yes, I can see it on the tblMain worksheet

No, it is not included in any counter on tblMain_Breakdown worksheet

Manually counting - either the database or the tblmain worksheet I get 20. You say it shoud be 19 (Item 1d + 1e) but 1d or 1e does not account for RID 43. 1d + 1e do add up to 19, but where is RID 43 ?

I am not sure where the "disconnect" is...   My manual count from above is :

For OnCampus Maths we have
RID 43   (Yes) ASL           (count as 1) and I believe is missing

RID 32   (blank) ASL        (count as 1) as per item 1d

RID  2   (blank) STU         (counts as 18) as per item 1e
RID  6   (blank) STU
RID  7   (blank) STU
RID 10   (blank) STU
RID 11   (blank) STU
RID 12   (blank) STU
RID 13   (blank) STU
RID 14   (blank) STU
RID 15   (blank) STU
RID 16   (blank) STU
RID 29   (blank) STU
RID 30   (blank) STU
RID 33   (blank) STU
RID 34   (blank) STU
RID 35   (blank) STU
RID 40   (blank) STU
RID 41   (blank) STU
RID 45   (blank) STU

So what is wrong with my manual count ? Why do I get 20 and you get 19 ? Please let me know where we should be accounting for RID 43 - or why it should be missing ?

mark:
I take all that back. It's my fault. I did the spreadsheet manually and in the process I missed out RID 43 and such could not visualize it when I was
tabulating the counts.
I have attached a new spreadsheet
All seems to be correctly counted but I willl appreciate it much better if we could use these "matches" of the following to count
GroupID, CourseID, STypeID  in addition to the fact that ([CCon] = True)
 

ASL-STU-CM-Count-Amended.xls

Phew, that is a huge relief, I went over that stuff quite a few times and thought I was going batty :)

OK,

the more recent queries :

1) now shows cell K8 as just STU   - because it is the only groupid for Maths + OnLine - should it also be showing all the different groups for maths ?
2) cell L10 - now shows Physics and Chemistry seperately per your request ID:26142210 - did I misunderstand that, and do you want them joined back ?

Sorry for the earlier misunderstanding..it was my fault of omission.
For your #2 quetion first.
2) cell L10 - now shows Physics and Chemistry seperately per your request ID:26142210 - did I misunderstand that, and do you want them joined back ?
   (a).  I want  a solution for "joint" - Physics and Chemistry and ..
    (b).  ...a separete counts Physics and separete count for Chemistry- I do need this for other issues.
Now for question #1.
.."now shows cell K8 as just STU   - because it is the only groupid for Maths + OnLine - should it also be showing all the different groups for maths ?
K8
GroupID            CouseGroup      STypeID            Count         Remarks
ASL, STU           Math                     OnLine               3                Correct  :<--- The ASL and STU were grouped together because of "Online" grouping
                                                                                                                                  This is the correct grouping.
                                                                                                                                 I also ran your different queries on the subject and GroupId were correctly listed.
I am not sure I answered your #1 question correctly...please correct me if I am wrong in my interpretations.

mark:
What does this query group does?
QueryBy_Course_and_Type
I notice that you dont give up on challenges. I can imagine, it's runs in your blood.
I have a follow up question to list with a sample db to be attached and I can link it to this post as a related topic. Would you be willing to take it on?
Excellent job

Excellent !!!

QueryBy_Course_and_Type lists each course seperately - so Physics and Chemistry are individual Courses, rather than transformed into "Physics, Chemistry"

No, I dont give up easily :)) Hoping you dont either ;)

I do this for the challenge and the enjoyment - must have rocks in my head *laughing*

So, if you have another challenge, I will definitely have a look.

Mark:
I will create the question and place it in the late evening today.
Regards
Bobby

Mark:
I have just re-opened a closed question with request attention. The re-oped question is part of this thread question you answered. It's a very simple one I believe.

Here is the link:
https://www.experts-exchange.com/questions/24974921/Syntax-From-Clause-Error.html