Somewhat complex 'group by' query

[Miroslav]

Hi everybody,

there's an issue i had a problem with and i still don't know how to
solve it, so could somebody help me 'cause i'm missing something big.
In sql server i can solve this with the CASE statement, but it doesn't
work the same way in access 2007.

I have one table with these columns: RowID, Priority, Country,
Subject, Date.
RowID is of type Number (and it's primary key); Priority, Country and
Subject of type text; Date of type Date/Time.

What i need is query that gives this information:
number of rows per country, priority, month and group.
Group is defined by some string somewhere inside Subject field and
must be derived from it...
E.g. subject can be the following: "AAA subject number one", or
"Subject that has BBB inside it", or "Third subject of CCC group".
The groups are then : AAA, BBB, CCC and if it isn't set inside
subject, then the group is DDD.

As i see the problem, i need a procedure that returns which string is
inside subject and return it.

So the resulting table should contain the following columns:
country, month, priority, group, count_of_rows

How can this be done... if it can be done at all?

Thanks in advance.

 

[Brian]

Create a public function FindGroup in a module.

Public Function FindGroup(Subject As String) As String
If InStr(1, Subject, "AAA") > 0 Then
FindGroup = "AAA"
Exit Function
End If
If InStr(1, Subject, "BBB") > 0 Then
FindGroup = "BBB"
Exit Function
End If

If InStr(1, Subject, "CCC") > 0 Then
FindGroup = "CCC"
Exit Function
End If
FindGroup = "DDD"
End Function


In your query, enter SubjectGroup: FindGroup([Subject]). Group by this value.