Grouping fields toegther in a query (1 Viewer)

mounty76 · Oct 14, 2017

Hello!

I have the below query that groups all courses with a count of less than 20 together and calls them 'Others'

SELECT IIf([CountOfCourse Name]<20,"Other",[Course Name]) AS Course, Sum([sub1-5].[CountOfCourse Name]) AS CourseTotal
FROM [sub1-5]
GROUP BY IIf([CountOfCourse Name]<20,"Other",[Course Name]);

I want this query to also do some other grouping.....In addition to the above I want it to also group any course names A and B and C together and call them 'ROPAX Courses', additionally I'd like it to also group Z and X and Y and call them 'Basic Training'

I've tried a few things and just cannot make it happen.....any ideas

:banghead:

Thanks in advance!

isladogs · Oct 14, 2017

You could use additional IIf statements though the query could get messy.

Another way could be to create a separate 'lookup' table with 2 fields: CourseLetter / CourseName then include that table in your query

OR you could use the ASCII character set for A-C & X-Z. See e.g. http://ee.hawaii.edu/~tep/EE160/Book/chap4/subsection2.1.1.1.html

arnelgp · Oct 14, 2017

SELECT Switch([Course Name] In ("A","B","C"),"ROPAX Courses",[Course Name] In ("X","Y","Z"),"Basic Training",[CountOfCourse Name]<20 And [Course Name] Not In ("A","B","C","X","Y","Z"),"Other",True,[Course Name]) AS Course, Sum([CountOfCourse Name]) As CourseTotal FROM [sub1-5] GROUP BY Switch([Course Name] In ("A","B","C"),"ROPAX Courses",[Course Name] In ("X","Y","Z"),"Basic Training",[CountOfCourse Name]<20 And [Course Name] Not In ("A","B","C","X","Y","Z"),"Other",True,[Course Name])

mounty76 · Oct 14, 2017

arnelgp....works perfectly! Thank you so much

mounty76 · Oct 14, 2017

Do you happen to know how to make a query that takes the 'Other' results and expands (de-groups) them back out to what they were originally?

arnelgp · Oct 14, 2017

The Switch function takes two parameter, the first one the condition , the other one the return value. Find the 3rd condition and remove its together with its pair value.

mounty76 · Oct 14, 2017

Sorry arnelgp, I'm lost a little bit with this....

mounty76 · Oct 14, 2017

I should have maybe been more clear, I want to make a query that only shows the 'Other' courses, but instead of calling them 'Other' I want them to be their original course names

arnelgp · Oct 14, 2017

Syntax:
Switch (cond1,retvalue1,cond2,retvalue2,cond3,retvalue3,...)

Find cond3, delete it together with its return value. Do it also in Group by.

arnelgp · Oct 14, 2017

SELECT Switch([Course Name] In ("A","B","C"),"ROPAX Courses",[Course Name] In ("X","Y","Z"),"Basic Training",True,[Course Name]) AS Course, Sum([CountOfCourse Name]) As CourseTotal SELECT Switch([Course Name] In ("A","B","C"),"ROPAX Courses",[Course Name] In ("X","Y","Z"),"Basic Training",[CountOfCourse Name]<20 And [Course Name] Not In ("A","B","C","X","Y","Z"),"Other",True,[Course Name]) AS Course, Sum([CountOfCourse Name]) As CourseTotal FROM [sub1-5] GROUP BY Switch([Course Name] In ("A","B","C"),"ROPAX Courses",[Course Name] In ("X","Y","Z"),"Basic Training",[CountOfCourse Name]<20 And [Course Name] Not In ("A","B","C","X","Y","Z"),"Other",True,[Course Name])
se Name] In ("A","B","C"),"ROPAX Courses",[Course Name] In ("X","Y","Z"),"Basic Training",True,[Course Name])

mounty76 · Oct 14, 2017

OK cool, I got that. Is there a way in the same query that it can delete/not show any results from the groups ROPAX Courses and Basic Safety Courses and any other course with a count over 20?

It works if I just put a criteria of <20 in the count box but this wouldn't work for when I do a query for say just the ROPAX courses so ideally would want to remove these 'returned groups' altogether if possible?

arnelgp · Oct 14, 2017

you know i am a little bit confused too,
and my Access as well (its hanging up on me, LOL).

nothing personal, i haven't seen your Profile.
are you a she or a he. you are always changing
your mind.

saved your query to a different name and for
sure, if you change your mind again, you have
something to go back with.

seriously what is the condition >20 or <20?

are we applying it to the "whole" group or
individually (eg. for A only, for B only,
for X only, etc).

arnelgp · Oct 14, 2017

you know i am a little bit confused too,
and my Access as well (its hanging up on me, LOL).

nothing personal, i haven't seen your Profile.
are you a she or a he. you are always changing
your mind.

saved your query to a different name and for
sure, if you change your mind again, you have
something to go back with.

seriously what is the condition >20 or <20?

are we applying it to the "whole" group or
individually (eg. for A only, for B only,
for X only, etc).

mounty76 · Oct 14, 2017

hahaha! I'm a he but the amount of times I'm changing my mind I could be a she!

Sorry....I made the db to initially be very simple but you know how these things go, I then made a report to show pie charts but the db only had 4 months data in, when I added the rest of the data for the year the pie chart wasn't readable! So hence I've had to filter my results down and then create 4 pie charts to show the data sensibly

I was having a melt down....so my last question was rubbish, I've done it now, thanks again for all your help

Cheers
Paul

Grouping fields toegther in a query (1 Viewer)

mounty76

Registered User.

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

mounty76

Registered User.

mounty76

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

mounty76

Registered User.

mounty76

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

mounty76

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

mounty76

Registered User.

Similar threads

Users who are viewing this thread