Row headers instead of Column headers

  • Thread starter Thread starter Robert Neville
  • Start date Start date
R

Robert Neville

I desire to create a report that includes contact names grouped by
jobtitle ROW HEADERS; as oppose to using column header. The report
would help me consolidate information existing as a sub-report on
larger report. The following data serves as an example.

Sales Representative: John Smith - Larry Johnson - Nancy Davolio -
Janet Leverling - Margaret Peacock - Suyama Michael - Robert King
Vice President, Sales: Andrew Fuller
Sales Manager: Steven Buchanan - Anne Dodsworth
Inside Sales Coordinator: Laura Callahan

Sales Representatives would be the job title label; and the hyphen
would delimiter the Contact name (FirstName & " " & LastName)
VBA code may accomplish this aim, yet I am at odd where to start
having not seen anything similar. Let me know if you have any thought
about my goal.
 
You can use this concatenate function. Paste the code into a new module and
save it as "basConcat". There are comments in the code on how to use.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
Duane,

Whoa, thanks for the excellent function and for including both DAO and
ADO. I am still learning ADO, so your well commented code will help
with the transition. I completely understand the code, yet need
additional coaching on its usage in a query.

My usage may be slightly different then your commented example. Let me
give you some details so you may give me your thoughts. Basically,
the query stems from two table; first question; should I create a
query and with both tables and use this query as the base of the
Concatenate query?

Here's the tables necessary and their fields.

Tables: tblCont, trelContProj

Fields:
tblCont: ContID, FirstName, LastName, Profession, Department,
Salutation, ContNote

trelContProj: ContID, ProjID, Jobtitle

I want to create a query that list all jobtitles by projects; with
full contact names delimited by " - " in a adjacent field. It would
look like the following

ProjID, Jobtitle, ContactName - ContactName - ContactName _
ContactName
ProjID, Jobtitle2, ContactName - ContactName - ContactName _
ContactName
ProjID, Jobtitle3, ContactName - ContactName - ContactName _
ContactName
Next ProjID, Jobtitle, ContactName - ContactName - ContactName

My SQL skills are not great, so I could use additional coaching to
create this query. Here's my rough draft, yet it is obviously
incorrect without ProjID in the select statement.

SELECT trelContProj.ContID, Concatenate("SELECT [LastName] & ' '
[FirstName] As ContactName FROM tblCont
WHERE ContID =" & [ContID]) AS ContactNames
FROM trelContProj;

Again, you have the idea with your function; now the function
parameters become the question. Please give several more examples.


You can use this concatenate function. Paste the code into a new module and
save it as "basConcat". There are comments in the code on how to use.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
Click to expand...
 
Seems like you are missing a table of Projects. Your main query should be
built from the Project table. I would also create a query fomr tblCont and
trelContProj to use in the Concatenate function.
SELECT trelContProj.ProjID, JobTitle, FirstName & " " & LastName as
ContactName
FROM tblCont INNER JOIN trelContProj on tblCont.ProjID = trelContProj.ProjID
Use the above query in your concatenate() sql.
--
Duane Hookom
MS Access MVP


Robert Neville said:
Duane,

Whoa, thanks for the excellent function and for including both DAO and
ADO. I am still learning ADO, so your well commented code will help
with the transition. I completely understand the code, yet need
additional coaching on its usage in a query.

My usage may be slightly different then your commented example. Let me
give you some details so you may give me your thoughts. Basically,
the query stems from two table; first question; should I create a
query and with both tables and use this query as the base of the
Concatenate query?

Here's the tables necessary and their fields.

Tables: tblCont, trelContProj

Fields:
tblCont: ContID, FirstName, LastName, Profession, Department,
Salutation, ContNote

trelContProj: ContID, ProjID, Jobtitle

I want to create a query that list all jobtitles by projects; with
full contact names delimited by " - " in a adjacent field. It would
look like the following

ProjID, Jobtitle, ContactName - ContactName - ContactName _
ContactName
ProjID, Jobtitle2, ContactName - ContactName - ContactName _
ContactName
ProjID, Jobtitle3, ContactName - ContactName - ContactName _
ContactName
Next ProjID, Jobtitle, ContactName - ContactName - ContactName

My SQL skills are not great, so I could use additional coaching to
create this query. Here's my rough draft, yet it is obviously
incorrect without ProjID in the select statement.

SELECT trelContProj.ContID, Concatenate("SELECT [LastName] & ' '
[FirstName] As ContactName FROM tblCont
WHERE ContID =" & [ContID]) AS ContactNames
FROM trelContProj;

Again, you have the idea with your function; now the function
parameters become the question. Please give several more examples.


You can use this concatenate function. Paste the code into a new module and
save it as "basConcat". There are comments in the code on how to use.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
Click to expand...
Click to expand...
 
Duane,

Thanks for you response, yet I am more confused now. I have spent a
good portion of my day on trial & error. My toils have begun showing
progress, yet I need additional insights on creating SQL statements on
the fly.

This SQL statement seems to work, yet it returns duplicates for each
ProjID and Jobtitle. The duplicates repeat for each concatenated
contact return by the function; meaning that if a Project has three
managers the query returns the concatenate string with the three
managers repeated three times.

SELECT trelContProj.ProjID, trelContProj.JobTitle, Concatenate("SELECT
FirstName & ' ' & LastName FROM tblCont INNER JOIN trelContProj on
tblCont.ContID = trelContProj.ContID WHERE[trelContProj].[ProjID]=" &
[ProjID]," - ") AS ContactNames
FROM tblCont INNER JOIN trelContProj ON tblCont.ContID =
trelContProj.ContID;

Let me know how to improve this SQL and prevent this duplicate
occurrence.

FYI The project table is not necessary here since this query links the
sub-report with the concatenate string to the project main report
through ProjID.
 
Back
Top