Is it possible to combine or merge text data into one field in a query 2

[Johnnycat1]

This question is a bit more complex than just doing a simple =[text1] & [text2]

What I am looking for is the ability to perform an aggregate function or something that acts like an aggregate on the same field when it contains text. I expect that there is some SQL that will do this but I have been unable to find it.

In a query that has the following fields
Bid Date Bid Info
5/19/2005 Centennial Plaza Retail Center - John O
5/19/2005 CHURCH - John O
5/19/2005 Church Site Work - John O
5/24/2005 Emergency Essentials - John O
5/26/2005 Camp Williams JLTC Bldg-2 - John O
5/26/2005 Bear River Health Department - John O

Help is greatly appreciated!

I need to combine data where the date is the same into the same cell and have a new line inserted into the cell when there is more than one item per date.

The result would be something like this:
Bid Date Bid Info
5/19/05 Centennial Plaza Retail Center - John O
CHURCH - John O
Church Site Work - John O
5/24/05 Emergency Essentials - John O
5/26/05 Camp Williams JLTC Bldg-2 - John O
Bear River Health Department - John O

 

[PHV]

Seems like a report issue, with grouping on [Bid Date].
Anyway, you may have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Johnnycat1]

PHV

I get a compile error when I use this module. It errors on
"As New ADODB.Recordset"
I should tell you that I am stuck trying to make this work in Access97. I don't know if that is the hang-up but I suspect that it could be.

Any ideas???

 

[PHV]

You have to references the Microsoft ActiveX Data Objects 2.x Library.
When in VBE menu Tools -> References.
You may however want to play with DAO if you're more confident with this sort of recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Johnnycat1]

PHV,
I have reverenced the Microsoft ActiveX Data Objects 2.8 Library and the DAO 3.51.

I am still getting errors in either the DAO mode or the ADO.

I am starting to wonder if the way I am writing the query is the problem.

The query is as follows

SELECT [Bid Date], concatenate("SELECT Job Name FROM Job # Tbl
WHERE Bid Date =" & [Bid Date]) AS [Bid Info]
FROM [Job # Tbl]
WHERE ((([Job # Tbl].[Bid Date]) Is Not Null))
ORDER BY [Job # Tbl].[Bid Date];

What do you think?

 

[PHV]

Perhaps this ?
SELECT [Bid Date], concatenate("SELECT Job Name FROM [Job # Tbl] WHERE [Bid Date]=#" & [Bid Date] & "#") AS [Bid Info]
FROM [Job # Tbl]
WHERE [Bid Date] Is Not Null
ORDER BY [Bid Date];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Johnnycat1]

Nope still not working. the module compiled fine but when I run the query, it wants to debug the module on each line.

Does that indicate a module problem?

 

[PHV]

Any error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Johnnycat1]

Yes, it gives me

Runtime Error 424
Object Required

when I hit "Debug" it stops on

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

 

[PHV]

Why not posting your whole concatenate function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Johnnycat1]

This is it:

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

 

[Johnnycat1]

PHV,

I have to log off for now. I would seriously appreciate any further help that you can offer. I will check your posts first thing tomorrow MST.

I am sure that you have me going in the right direction but something is still getting hung up...

Thanks again.

Johnnycat1

 

[PHV]

Seems you're playing with an old access version lacking the Application.CurrentProject property.
I suggest you follow the DAO way.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Johnnycat1]

PH,

You were right about the DAO getting rid of the error message but I am still struggling with the SQL.

With the DAO setting it runs up to the line "set rs = db.openRecordset(pstrSQL)". I copied the code below and I inserted the comment that it shows regarding the SQL when I hover over it as follows:

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)
'this line is the break point and when I hover over it, it shows the following
'pstrSQL = "SELECT Job Name FROM [Job # Tbl] WHERE [Bid Date]=#5/17/2005#"
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


Any Ideas???

 

[PHV]

And the problem is ?
Hopefully [Bid Date] is defined as DateTime in [Job # Tbl] ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Johnnycat1]

Yes, both date fields are Date/Time formats.

I have rewritten the SQL to the following and now I get no errors but it doesn't return any data either.

SELECT [Job # Bid Dates].[Bid Date], concatenate("SELECT [Job Name] FROM [Job # Tbl] WHERE [Job # Tbl].[Bid Date] =" & [Job # Bid Dates].[Bid Date]) AS BidNames
FROM [Job # Bid Dates];

I do not see why it is not bringing the Job Name data in.

 

[Johnnycat1]

PH,

I just wanted to thank you for the help... I found my error and the function is working awesome! For the record, the SQL should read:

SELECT [Job # Tbl].[Bid Date], concatenate("SELECT [Bid Info] FROM [Job # Tbl] WHERE [Bid Date]=#" & [Job # Bid Dates].[Bid Date] & "#") AS [Bid Info]
FROM [Job # Bid Dates] INNER JOIN [Job # Tbl] ON [Job # Bid Dates].[Bid Date] = [Job # Tbl].[Bid Date]
GROUP BY [Job # Tbl].[Bid Date], concatenate("SELECT [Bid Info] FROM [Job # Tbl] WHERE [Bid Date]=#" & [Job # Bid Dates].[Bid Date] & "#")
HAVING ((([Job # Tbl].[Bid Date]) Is Not Null))
ORDER BY [Job # Tbl].[Bid Date];

This is a bit more complex than the way the help file wrote it.

The only thing that I could not do was that I would like the concatenate function to put a new line in after the text in stead of a comma or a semi-colon.

How difficult is that???

Thanks again.

 

[PHV]

you may try this:
SELECT [Job # Tbl].[Bid Date], concatenate("SELECT [Bid Info] FROM [Job # Tbl] WHERE [Bid Date]=#" & [Job # Tbl].[Bid Date] & "#", Chr(10)) AS [Bid Info]
FROM [Job # Bid Dates] INNER JOIN [Job # Tbl] ON [Job # Bid Dates].[Bid Date] = [Job # Tbl].[Bid Date]
WHERE [Job # Tbl].[Bid Date] Is Not Null
GROUP BY [Job # Tbl].[Bid Date], concatenate("SELECT [Bid Info] FROM [Job # Tbl] WHERE [Bid Date]=#" & [Job # Tbl].[Bid Date] & "#", Chr(10))
ORDER BY [Job # Tbl].[Bid Date];


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Johnnycat1]

Your SQL inserts something but it is not recognized as a new line by anything.

Perhaps this could be done in the module.

The first line of the ModConcatenate reads...

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Is there code that I can replace ", ") with to have the concatenate function insert the new line in stead of the SQL?

What do you think.

 

[PHV]

Use Chr(13) & Chr(10) instead of simply Chr(10)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886