ConcatRelated

[bbilotta]

I'm not a proficient Access user.


I have a table of Dogs. Dogs have titles (FC AFC CFC ....). A dog may have no titles while others may have many.
I have a Title table, and i have a DogTitle junction table.

I've built a query (and a subquery) that provides a list of all of the dogs and titles associated with those dogs that have titles. My problem is that dogs with multiple titles are now listed many times depending on how many titles they have.
Apparently what i need is some VBA that tells Access to concatenate the titles of those dogs with more than one title. And it is my understanding that the code I need is called ConcatRelated. My problem is i have no idea where to begin with getting this VBA written into my application.

I would like to see "Title", "Title", "Title", DogName. This should be trimmed of course, and i can certainly live without the commas between the Titles. For instance FC AFC CFC My Dog Spot

Can anyone help me get this done? And please don't be afraid to speak slowly, you won't offend me.

[orange]

Are you using this by Allen Browne? It has example code.
Where is your work/database?

You may find more examples if you use Google with ConcatRelated Allen Browne

[bbilotta]

Orange,

First, Thanks for the reply. Yes I have seen the Allen Browne stuff. But i have no clue where to begin with it. I've spent my entire life working with Excel, never Access. Though i'm slowly figuring out Access, and i've come around to using proper structure, i'm clueless right now with respect to VBA. I've gathered that i need to open a module and perhaps insert the Allen Browne code in the module????? And i'm kind of getting the idea it has to be a "PUBLIC" function as opposed to PRIVATE ??? Then i have to build a query using SQL that calls the query i've already built and tells it to ConcatRelated ....... I'm not entirely sure. I'm hoping someone can say "yes, do this, do this do this". Once i see it put together i'll be able to backward engineer my way into figuring out how I got there, but since i have no concept of how VBA works, and little concept of how Access works in its entirety, i'm a little lost right now.

The work is located on my pc for now. i have future plans to make it public to a small group.

Thanks again.

[orange]

Welcome to vba.

You concatrelated stuff might be better answered if you could post a copy of your database.

As for vba, here are a couple of sources of info

Access and vba
Crystal's Access Basics
Advanced vba playlist by Steve Bishop

Private and Public relate to scope

Good luck with your project

[orange]

duplicate post?? by me

[bbilotta]

Thanks for the additional links Orange, and please know i am working toward being self sufficient. i found some other links you posted for others in my similar situation and i'm watching some of Bishop's YouTube lessons now. I'll keep working at it until I can do everything i need to do, but right now i'm stuck on this and would like to get past it so I can keep working.

Here's a copy of the file. I've removed the original data and structure that i was given originally and left only the tables i'm working to build correctly now.

DogRebuild.zip

I appreciate the help.

I must clarify - The table DogTbl contains extraneous information that will eventually be deleted. you will see that Titles are listed as text strings and as foreign keys. The text is only there so i have a reference to the original data, it is not intended to be used.

[orange]

I created a query JDogTitles that uses your DogTitleJTbl junction table and the Titles table to get the DogId and Title

SELECT DogTitleJTbl.DogID, DogTitleJTbl.TitleID, TitlesTbl.Title
FROM TitlesTbl INNER JOIN DogTitleJTbl ON TitlesTbl.TitleID = DogTitleJTbl.TitleID
ORDER BY DogTitleJTbl.DogID;

and used this JDogsTitle query and the ConcatRelated function in this query JConcatenatedDogsAndTitles

SELECT DogsTbl.[DogId]
, DogsTbl.[RegisteredName]
,  ConcatRelated("Title","JDogTitles","DogId=" & [Dogid]) AS Concatenated_Title
FROM DogsTbl;

Sample result is attached jpg:
I have added this material to your zip which I am returning.

I think you should identify how the Events and Obedience Tables fit in your "business".

Hope this is helpful.

[bbilotta]

Oh, this is beautiful. Now let me see if i understand it.

You used pure SQL to combine the DogIDs and associated titles. In Access i did this with a query. Correct?
Then you again used SQL and the ConcatRelated command (not sure if that's the right nomenclature for ConcatRelated) to combine the RegisteredName and related titles being pulled from the first query.
Am I correct in assuming that in Access i could have opened a new Query, switched to SQL View and manually written that string of commands?
And the second query is stating SELECT the DogID and RegisteredName FROM the Table DogTbl - and ConcatRelated Titles from the first query where DogID in that query matches DogID from the table DogTbl and that it knows we're asking it to match DogID from the query because we're specifying the query inside the ConcatRelated () and that we're asking it to match DogID from the table DogTbl because that's what is shown in the SELECT and FROM??? And you're naming that result Concatenated_Title with the AS statement?

From there one could concatenate the Concatenated_Title and Registered name (which is where i ran into trouble to begin with in trying to use a modified lookup with multiple values and then trying to concatenate that field - Access won't do it apparently - and poor design regardless.

It would seem i was reading too much into needing a "Module", or am i missing something in my interpretation?

Can you tell me if the commas between the titles are automatically generated or if there's a way to eliminate them?

I really appreciate your help with this.


FYI - in one of your earlier posts to another user i saw you recommended some YouTube lessons from Dr. Dan Soper. i'm working through them now. Although i'm now somewhat familiar with the lessons he begins with, it is providing a more fundamental basis for me instead of the shotgun approach i've been taking thus far. So thank you for that as well.

[orange]

Access and most databases use SQL. The query design view in Access is a convenience for users- a special interface to let users deal with a graphic - but it is creating SQL behind the scene.

ConcatRelated is a function. It takes input parameters, and outputs a value. I copied the function from Allen Browns site and included it in module1 in the database. Since the function returns a value, and that value is the concatenated Titles, it is just a function call for each record in the DogTbl. If there is no title, the value is NULL. And, yes I name the value from the ConcatRelated
function Concatenated_Title.

If you open the query window in SQL View and type in the SQL I gave, you can open the query design window and see the graphic.
Note that some queries can not be displayed in the graphic/design mode. They are only available in SQL view.

The ConcatRelated function

Public Function ConcatRelated(strField As String, _
                              strTable As String, _
                              Optional strWhere As String, _
                              Optional strOrderBy As String, _
                              Optional strSeparator = ", ") As Variant

indicates the default separator is a comma.

Normally, you could adjust the call to the function and use whatever separator you want. However, I tried a few times and got syntax error repeatedly. So, I went to the function definition and changed the line (orange) by removing the comma and substituting a space.

Public Function ConcatRelated(strField As String, _
                              strTable As String, _
                              Optional strWhere As String, _
                              Optional strOrderBy As String, _
                              Optional strSeparator = "  ") As Variant

Dr Soper and Steve Bishop videos are very good. Also 599CD has some very good youtube material.
Once you're somewhat comfortable with the relational stuff from Dr. Soper, look for some videos featuring
Dr.Jennifer Widom --she's excellent, but very fast.

Happy to help.

[bbilotta]

Thanks again Orange.
I will continue to digest this.
I will look for Dr. Widom also.

[bbilotta]

Orange,

I didn't answer your other questions:
Obedience is akin to Titles. They are "suffix" titles that appear at the end of the Dog's name as opposed to Titles that appear at the beginning.

I belong to a dog club that trains and trials English Springer Spaniels in Field Trials. There are Events (trials), and dogs are entered in these events. The dogs have owners (PeopleTbl) and they have Handlers (PeopleTbl). Dogs also have Sires and Dams (both a self-join to the DogTbl).

There are various Stakes at each trial, fees for each stake and dogs are entered into one or more stakes. Not your typical "starter project" I'm sure.

On another note, is there a way for me to dictate the order of the Titles inside the ConcatRelated result? It's rather important that they appear in the correct order. While this is not the case for Obedience, it is so for the Title.

[orange]

My comments on your tables was related to your relationships window. If you have tables in your model, they will be associated with some other table. Your model looked incomplete because those 2 tables were "orphans".

In what is the order should the Titles appear?
You can sort ascending/descending, but if there is some other sequence, you might consider a Sequence Order field on you Titles table.

[bbilotta]

So after opening the file you returned i had to laugh. I have an IQ of 170 and it completely escaped me that the ENTIRE section of code on Browne's site had to be copied and pasted. So there's no wonder i had no chance of getting it to work. LOL I was way to wrapped around the axle on this one. It certainly helps to be able to laugh at yourself once in a while.

Re. orphaned relationships - Yes, there have been many "start overs" in this project for me. I get moving along then discover I used a defective approach, or a poor design and start over. On the plus side however, I'm getting plenty of practice .

The order for the Titles would be the key field TitleID. I can include a sequencing field if necessary, but assumed the ID field would suffice.

[bbilotta]

Orange,

Can you see any reason, after creating what you created, i would not be able to do a simple concatenation in a query? I was trying to concatenation the Concatenated_Title you created and RegisteredName with no luck - could NOT figure out why. Then i tried to do a simple concat of RegisteredName and CallName and it keeps asking for a parameter value for CallName (apologies for the left-over : at the end of CallName: BTW, oversight).

When i remove the Module, and the two queries you built concatenation seems to work fine, but with the work you did i can't seem to concatenate the fields in the DogsTbl. Any idea why or what i need to do differently?

[orange]

We've all done some things that make you laugh when you look back.
The secret is to learn and remember. And practice can help with that.

Here is the sql for the Concatenated stuff with the Titles in the appropriate order.

Orderby is a parameter for the ConcatRelated function.

In fact, when I use the OrderBy, I can now specify the separator character(s).
The field to OrderBy is TitleID

SELECT DogsTbl.DogId
, DogsTbl.RegisteredName
, ConcatRelated("Title","JDogTitles","DogId=" & [Dogid],"TitleID"," ") AS Concatenated_Title
FROM DogsTbl;

Good luck.