Grouping strings

[Fre]

I'm trying to create a simple matrix that lists airlines in columns, officer positions as row header, and the officers in the matrix.



This is a sample of how the data is received:





I want to make it so that all CEO's are on one line, all CFO's on the next, etc. Example:



Is there an aggregate function that can handle strings? I tried using FIRST, thinking that I could GROUP BY Officer Type and that it would select the first row with value, but that didn't work. Any suggestions would be really appreciated.

[TheShabz]

what do you mean by one line?
like
CEO
CEO
or
CEO CEO

if former, just sort. if latter, why would you want to do that IN your table? that should be something you do on a report or some sort of output.

[Fre]

what do you mean by one line?
like
CEO
CEO
or
CEO CEO

if former, just sort. if latter, why would you want to do that IN your table? that should be something you do on a report or some sort of output.

I want the end result to look like this:

[TheShabz]

Nvm, the second picture didn't load for me the first time. My apologies. I'm gonna pass on this and let the others answer it.

[maximus]

Cross Tab query is something that you might want to look into. It will be available in your query options.

Select OfficerType as Row Header
Select Airline as Column Header
Select Name as Value and in the Groupby Select First.

Assuming you will only have a single officer in a specific type for a particular Airline.
This is the syntax for a CrossTab Query:

TRANSFORM First(Table1.Name) AS FirstOfName
SELECT Table1.OfficerType
FROM Table1
GROUP BY Table1.OfficerType
PIVOT Table1.Airline;

if this solves your problem mark the thread solved.
Note use the query wizard to make your querys if you are not very familiar with Sql syntax.

[maximus]

I am attaching a sample mdb for you.

[Fre]

Cross Tab query is something that you might want to look into. It will be available in your query options.

Select OfficerType as Row Header
Select Airline as Column Header
Select Name as Value and in the Groupby Select First.

Assuming you will only have a single officer in a specific type for a particular Airline.
This is the syntax for a CrossTab Query:

TRANSFORM First(Table1.Name) AS FirstOfName
SELECT Table1.OfficerType
FROM Table1
GROUP BY Table1.OfficerType
PIVOT Table1.Airline;

if this solves your problem mark the thread solved.
Note use the query wizard to make your querys if you are not very familiar with Sql syntax.

To the contrary I'm pretty familiar with SQL. I should have put my query in to begin with:

SELECT [Officer Type], First(Airline1), First(Airline2), First(Airline3)
FROM Table1
GROUP BY [Officer Type]

This gets me pretty close to my desired outcome EXCEPT that the FIRST function chooses the first value even if it is empty. The solution is either going to be in the form of a function that performs like MAX (selecting the largest value) for strings, or I suppose there might be a way to test whether the value is empty before selecting FIRST, or it's going to take some VB, but that last one's definitely beyond my expertise.

Thanks for your help though. I really appreciate you taking the time.

[Fre]

Nvm, the second picture didn't load for me the first time. My apologies. I'm gonna pass on this and let the others answer it.

I updated my original post after getting your response. Thanks anyhow.

[maximus]

First of all I find the Note very handy for people who are not so profound as you are in SQL. They try to copy the code directly into the SQL window without making modifications to table names etc.

What is you problem ?

1) Do you have officertype assigned without a name?
2) Do you have more than one person in the OfficerType for a particular Airline.
3) You dont want blank values for an officertype for a particular Airline if not assigned.

[Fre]

First of all I find the Note very handy for people who are not so profound as you are in SQL. They try to copy the code directly into the SQL window without making modifications to table names etc.

What is you problem ?

1) Do you have officertype assigned without a name?
2) Do you have more than one person in the OfficerType for a particular Airline.
3) You dont want blank values for an officertype for a particular Airline if not assigned.

The main problem is that I get the data in that odd format to begin with. In my first post you'll see that the data is already in crosstab form. I'd expect the airlines to be listed in a column as you had it, but unfortunately that is not how the data is delivered.

So trying to work with what I get, I want to simply group values by officertype. I won't have more than one person in the OfficerType for a particular Airline, so that's not an issue. Essentially, I need an aggregating function like FIRST that will grab the first value that is not empty and set that as the value in the matrix. Unfortunately, FIRST will take an empty value and place it in the matrix, and thus leave an officers name out of the matrix, otherwise that functionality would be ideal.

[maximus]

First of all I find the Note very handy for people who are not so profound as you are in SQL. They try to copy the code directly into the SQL window without making modifications to table names etc.

What is you problem ?

1) Do you have officertype assigned without a name?
2) Do you have more than one person in the OfficerType for a particular Airline.
3) You dont want blank values for an officertype for a particular Airline if not assigned.

is this the problem.

so if you receive the data in a cross tab format then my question is can't you then just sort the data by OfficerType.

[Fre]

Just to be clear about the problem I'm experiencing with the FIRST function, here is the original data



and here is the result of using GROUP BY...FIRST() on the data above:



as you can see, Steve and Dan were not included. The reason is because the first value access sees when it groups CEO under Air Canada and Air Tran is empty.

Thus, I either

a) need an aggregate function that can evaluate the value of a string and select the max value
b) a clever set of conditionals that can test to see if the value is not empty before selecting it as FIRST
or c) vb script that can solve this procedurally

[maximus]

Just Upload a sample i need some data to test out can you do that. upload in access 2000 format.

[Fre]

The data is sensitive, but I am attaching the data contained in the screen shot above.

If that can be turned into the desired outcome I should be able to take that and apply it broadly.

[maximus]

After looking into the spreadsheet that you had sent I understood the problem more clearly. You are were right some how we need to select the First Value if it is not null.

The nature in which the file is formated makes it very difficult. Dlookup could have been an option but it will select the first value even if it is Null.

a) need an aggregate function that can evaluate the value of a string and select the max value
b) a clever set of conditionals that can test to see if the value is not empty before selecting it as FIRST
or c) vb script that can solve this procedurally

I followed on this lines and this is what I have done:

Option Compare Database
Dim MyTable As String
Dim MySearchFld As String
Dim MyCriteria As String
Dim strSQL As String
Dim intColumnNumber As Integer
'************************************************* **************************************
'*Scope of this function is to Select the First value from a Table for a given Criteria*
'*if No Record is Found "" is assigned to the String
'*Written by Siddthartha Majumdar 04/24/2010
'************************************************* **************************************

Function IntelSearch(MyTable, MySearchFld, MyCriteria, intColumnNumber) As String
'using an SQL to populate the Recordsey
strSQL = "Select * From " & MyTable & " Where " & MySearchFld & "='" & MyCriteria & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then 'If the Record count is more than 0 the only Loop started.
rs.MoveFirst
Do While Not rs.EOF
If rs.Fields(intColumnNumber) <> "" Then 'The Loop Exits
IntelSearch = rs.Fields(intColumnNumber) 'When a value is detected IntelSearch is populated
Exit Do 'Exits from the Loop
End If
rs.MoveNext 'Moving to the Next record if value is null.
Loop
Else
IntelSearch = "" 'If Record count is zeo them intelsearch =""
End If

Set rs = Nothing
End Function


This Function Runs through the table and selects the First Value if it is not Null.
Syntax of the Function:

IntelSearch(MyTable, MySearchFld, MyCriteria, intColumnNumber)

The Function Opens a recordSet based on a SQL and Runs through it. When it Finds a Null Value it Moves to the Next Record and continues this till it detects a value which is then displayed.

almost all the criteria are self explanatory
MyTable is the Table Name here =Sheet1
MySearchField is Officer_Type
MyCriteria wil be either CEO, CFO, EVP


This using this function I create Three Queries arranging the CEO, CFO, EVP in the order that I want to in one Row, and then use a Union query to combine them to display all information

Please open Query4 the union query to see the Final effect

I have attached a mdb for you reference.

if this solves your problem mark the thread solved