Concatenating Same Form Field Values

[Lrn2Code]

Hello Everyone,

I'm trying to concatenate a form field - course category - when the course name and course id are the same but the course has two or more course categories associated with it.

I found Allen Browne's concatenation function and I don't understand it, so can't figure out how to craft it for this issue. Is there a way to either concatenate in the query the sub form is running from, or put concatenated values elsewhere and pull them into the sub form field when the form is originally pulling from a different record source?

Have attached a screenshot to show the issue. There should only be one listing for kindergarten and both categories should be on one line.

Have been searching the Web about this issue and not finding much. Maybe there isn't a way to do this.

Thanks for any guidance you can provide, and have a great day.

 

[vbaInet]

Lrn2Code, could you show Sample Records and also show Desired Result so we know exactly what you're talking about. The screenshot doesn't really help. Something like this would:

I would like this:

[B]ID      Country[/B]
1       France
1       U.K.
2       Germany
2       Sweden
3       Mexico

To be:

[B]ID      Country[/B]
1       France, U.K.
2       Germany, Sweden
3       Mexico

 

[ajetrumpet]

vbaInet is right. this aint clear, but then again, he's not as good as I am.

I guess I'll add too that you're showing us a continuous form. I'm sure you realize that? There is nothing you can do (easily) to concat the records in a verticle manner like you want without making a new table.

 

[highandwild]

Would it be appropriate to base the form on a query showing the first category and then using dlookup from a calculated field in the query to the second category record in the same table.

I'm not sure what data you have for the criteria.

I don't like using dlookup in a query but sometimes it is the only way.

 

[Lrn2Code]

Thanks for your replies and sorry that I wasn't clear.

The form is running from a query and I've attached a snapshot of the query.

You can see where the courseid for Kindergarten has a course category of both Elementary Ed and Computer Science. (I can't figure out why the items are duplicating. I've tried adding distinct and making more connections between the tables - have attached the SQL too - but that is another issue.)

The main thing I'm trying to accomplish is to have the classes listed as follows -

Kindergarten Elementary Education-General (K-6), Computer Science

However many categories a course has need to be listed on the same line of the form as the course name so if there was an Art course with 3 categories it should be listed -

Art Art, Graphic Design, Design & Technology Education


If a course has just one category then obviously it would list as

Art Graphic Design

Thanks again for your replies and any help you can provide. I'm still learning to code but this problem seems way above my understanding right now.

 

[vbaInet]

You can see where the courseid for Kindergarten has a course category of both Elementary Ed and Computer Science. (I can't figure out why the items are duplicating. I've tried adding distinct and making more connections between the tables - have attached the SQL too - but that is another issue.)

In your query, one of the fields isn't producing unique results hence the reason why you feel the DISTINCT predicate isn't working. EducatorID is not unique in the results for example.

For a solution on the subject of this thread, here's a link for the most widely used approach:
http://allenbrowne.com/func-concat.html

Here's an example of how it was implemented (sample db in post #12):
http://www.access-programmers.co.uk/forums/showthread.php?t=190273

Here's a thread from ajetrumpet discussing how to perform vertical concatenation:
http://www.access-programmers.co.uk/forums/showthread.php?t=189359&highlight=vertical

 

[Lrn2Code]

Thank you, vbaInet. I will check the links and see if I can make sense of how the problem is solved.

I appreciate your time and guidance.

Have a great day!

 

[vbaInet]

Let us know how you get on.

Have a good day too.

 

[Lrn2Code]

My apologies for taking so long to post a solution to this problem (a co-worker helped me finish it up) - but I have been swamped with other applications (am sure you all know how that is).

I ended up creating a new table to store the final data. That data is pulled from a query and analyzed for course names and their categories then concatenating the categories when multiple categories are listed for one course name.

In looking again at Allen Browne's guide I guess I ended up doing something very similar but I still don't understand some of the coding he used. (Am mostly self taught and I can't tell you how much of a help this site has been in my coding journey. It's amazing and am so glad I found it!)

Thanks again to all who offered guidance..your time and expertise are much appreciated.

The code I used follows - I hope it may be a help to someone else dealing with such a problem.

Have a great day!

Lrn2Code

Sub CreateReportTablecats()
[COLOR=SeaGreen]
'''to create a table where all course categories are in one record so that reports don't
'''contain multiple entries for the same course/teacher/grade range combo[/COLOR]

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strsql As String
Dim lngCourse As Long
Dim lngTeach As Long
Dim strCat As String

Dim dblQty As Double
Dim strPS As String
Dim strBeg As String
Dim strEnd As String
Dim strCourse As String
Dim intRecord As Integer

DoCmd.Hourglass (False)

Set dbs = CurrentDb

strsql = "Delete * from tblCourseCatsforForm"
Set qdf = dbs.CreateQueryDef("", strsql)
qdf.Execute

strsql = "select * from qry_forteachercoursesform Order by CourseID, EducatorID, coursecat"
Set rst = dbs.OpenRecordset(strsql)
rst.MoveLast
rst.MoveFirst
Dim i As Integer
i = rst.RecordCount

[COLOR=SeaGreen]'put first record into table[/COLOR]
intRecord = Nz(rst.Fields("RecordNo"), 0)
dblQty = Nz(rst.Fields("Qty"), 0)
strPS = Nz(rst.Fields("POSID"), "")
strBeg = Nz(rst.Fields("CourseBegLevel"), "")
strEnd = Nz(rst.Fields("courseEndLevel"), "")
strCourse = Nz(rst.Fields("coursename"), "")
lngCourse = Nz(rst.Fields("courseID"), 0)
lngTeach = rst.Fields("EducatorID")
strCat = Nz(rst.Fields("CourseCat"), 0)

strsql = "insert into tblCourseCatsforForm (RecordNo, EducatorID, CourseID, Qty, POSID, CourseBegLevel, CourseEndLevel, coursename, coursecat ) Values " _
& "(" & intRecord & ", " & lngTeach & ", " & lngCourse & "," & dblQty & ",'" & strPS & "', '" & strBeg & "', '" & strEnd & "', '" & strCourse & "','" & strCat & "' )"
'Debug.Print (strsql)
Set qdf = dbs.CreateQueryDef("", strsql)
qdf.Execute dbFailOnError

rst.MoveNext

[COLOR=SeaGreen]'now start looping through rest of file[/COLOR]
Do Until rst.EOF

[COLOR=SeaGreen]'look for additional course categories[/COLOR]
If lngTeach = rst.Fields("EducatorID") Then
  If lngCourse = rst.Fields("CourseID") Then
            If strCat <> rst.Fields("CourseCat") Then
            strCat = strCat & ", " & rst.Fields("CourseCat")
            strsql = "Update tblCourseCatsforForm set Coursecat = '" & strCat & "' where CourseID = " & lngCourse & " "
         End If
     End If
           
[COLOR=SeaGreen]'if there are none, then start new record[/COLOR]
        Else
            intRecord = Nz(rst.Fields("RecordNO"), 0)
            dblQty = Nz(rst.Fields("Qty"), 0)
            strPS = Nz(rst.Fields("POSID"), "")
            strBeg = Nz(rst.Fields("CourseBegLevel"), "")
            strEnd = Nz(rst.Fields("courseEndLevel"), "")
            strCourse = Nz(rst.Fields("coursename"), " ")
            lngCourse = Nz(rst.Fields("courseID"), 0)
            lngTeach = rst.Fields("EducatorID")
            strCat = Nz(rst.Fields("CourseCat"), "")

strsql = "insert into tblCourseCatsforForm (RecordNo, EducatorID, CourseID, Qty, POSID, CourseBegLevel, CourseEndLevel, coursename, coursecat ) Values " _
& "(" & intRecord & ", " & lngTeach & ", " & lngCourse & "," & dblQty & ",'" & strPS & "', '" & strBeg & "', '" & strEnd & "', '" & strCourse & "','" & strCat & "' )"

End If

[COLOR=SeaGreen]'Enter record into table[/COLOR]
Set qdf = dbs.CreateQueryDef("", strsql)
qdf.Execute dbFailOnError

rst.MoveNext
Loop

DoCmd.Hourglass (False)

 

[vbaInet]

Lrn2Code, it would be nice to put your code in Code tags. Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?t=200247

 

[Lrn2Code]

vbaInet -

Thank you for the guidance...that looks similar to using html coding on a Web page. (Using [ ] instead of < > in this case.)

I will remember to use that next time.

Thanks,

Lrn2Code

 

[vbaInet]

vbaInet -

Thank you for the guidance...that looks similar to using html coding on a Web page. (Using [ ] instead of < > in this case.)

I will remember to use that next time.

Thanks,

Lrn2Code

Yes, the same as html. You can still edit your post and add those code tags. The code as it stands is unreadable.