ConcatRelated

[orange]

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?

I'm not sure what you are saying exactly.
concatenation eg Field1 &Field2 would result in Field1Field2 ---simple string concatenation
ConcatRelated is a function that produces a variant

If you are going to use ConcatRelated, you have to have it in a module.
A you can call it from a query as I did.

Perhaps you can describe more about what you're asking. A picture/example might help.

[bbilotta]

Sure, let me try to clarify.

In our newly created file i'm trying to create a query and concatenate the newly created Concatenated_Title and RegisteredName. I'm expecting to see FC AFC CFC My Dog Spot (Just as one would typical do with FirstName/LastName). But that concatenation will not work for some reason.

Take a typical FirstName/LastName concatenation. I would simply write FirstName &" "& LastName and have Access produce an output of John Doe. In my original file, without the new Module and new queries you so generously created for me, i can concatenate from the table DogsTbl such as RegisteredName &" "& CallName and i get an expected result. However, in the file you returned with the ConcatRelated function installed i can no longer seem to concatenate fields related to dogs at all.

I even tried to create a simple query using only the table DogsTbl and entering RegisteredName &" "& CallName: and it fails asking me for a parameter input. I can still concatenate FirstName LastName from the table PeopleTble, but for some reason not Dogs.

[orange]

??? I just opened the database I was working with and created query1 as per attached jpg. It has module1 and the ConcatRelated function.


Here is some output of the query showing the concatenation.

[bbilotta]

Really? That's interesting. I'm going to try again by downloading your zip and see if i can get the same result. Not sure why my file here would be any different, but it won't let me do exactly what you just did.

[bbilotta]

Ok, I got it. I think however the previous example you posted took the Title from the table DogsTbl, which is an old text field left for reference. I tried several combinations of tables with error before getting to this one that worked.



It produces this (note the FullNameTitle field):

:

I'm not sure why earlier attempts at concatenating anything wouldn't work. I deleted those files and downloaded a fresh copy.
in other iterations of this combination i was getting an error that Access didn't know which table to pull the DogID from.

In any event, i have them together now and thank you again for your help.

Next step for me is figuring out how to get the user to see the appropriate information on their form when entering a new dog. I really like the way the Title choices are presented when using the Access modified lookup field from a table (a list of Titles with checkboxes appears only when you click on the drop down). But of course, the modified lookup is not a good approach and causes many other problems. So now i need to learn how i can present those choices in "drop-down" fashion to the user. Subform would not seem to work here as the choices will always be visible.

[orange]

Let me know how you do.


Update:
Just read your last post. I think I recall altering your table relationship - you had the set up backwards as i recall.
I'm attaching a jpg of the relationships in the database I used




Also, I think the Order of your Titles may not be the order you wanted. See Post #15 for the OrderBy parameter for the ConcatRelated function.

[bbilotta]

Ultimately, this is what i was looking to create at this phase:

[bbilotta]

Yes, thanks. i had inserted the order parameter, and removed the "," separator value.

I see that relationship in the relationship window. However, in your previous post (#18) it appears as though you took "Title" from the DogsTbl which would be the old text field. I have since renamed it TitleOldText for clarity.

DogRebuild3.zip

[orange]

It's important to get yur tables and relationships set up and tested before getting too deeply into physical database.

TitleTbl contains all titles and an associated ID.
DogsTbl has info about Dogs and only Dogs
DogsTitleJTbl is a Junction table that holds the Ids of Dog and the Title IDs belonging to that dog. You use a query to get the Title(text) based on DogsTbl, the junction table and the Titles table. You don't put the Title (text) in the Dog tbl if there will ever,ever,ever be more than 1 Title per Dog. That's fundamental to relational database. Info stored in 1 location.

Junction table also called Linking Table, Associative table.... see this for more info

[bbilotta]

Yes, thanks, completely understand. The text values is what I inherited. i kept them in place in text form so i'd have something to refer to as i went along for comparative purposes. That text field will be deleted now that i have the JTable working the way i want it and i have confirmed the values match (that i didn't make any mistakes when i did my Excel work in getting the Title IDs associated with each dog). I know there are queries one can write to solve that problem, but i already know excel very well and it was easy to run the Vlookups.

The same holds true for the Obedience titles. Now that i know how to do it I can construct those also.

I have some clean up to do on the dogs table and on the people table before i get too much further along. I've learned the lesson of getting it right on the front end several times now

Thank you for looking out.

[orange]

Good stuff!

[bbilotta]

I do have a design question, and if this is silly i apologize. When you set up a query that uses a table more than once, and you rename that table within the query, do you use any special format? For instance, If i use my table PeopleTbl in a query to combine dogs, their owners, co owners, Breeders and co-breeders, would you name those PeopleTbl instances something like "OwnerVT (Virtual Table)? Or just Owner?

[orange]

Not a silly question.

Suppose you have a tblDog, and a table tblPeople, and you want to use the values in that table to identify Owners (of Dogs). Further suppose you want values from tblPeople to represent Breeders, and further suppose you wanted to record the Sire and Dame of each Dog.
I'm attaching a jpg to show the concept (not details).
When you add a second copy of a table, Access puts a numeric suffix on that table. You can rename that table in your code and call it a name that has meaning to you. IN the jpg, consider TblPeople_1 to be the Breeder--but the values come from a second copy (an alias of the table needed so the underlying SQL is consistent).

A statement like Select * from TblPeople_1 as Breeder.
You can update the OwnerId in TblDog with the PersonID from tblPerson as Owner

In fact since the Sire and Dame are all Dogs, you could make copies of TblDog and use them to get values for Sires and Dames.

That's the concept.



When you build a query in design view, you can right click on a table to set its Alias, as I did below. There is no data in any table but you can see a mock up of how you might proceed with such a set up.

[bbilotta]

OK. Thanks. That's what i've been doing for my self-join on the dogs (DogsTbl, Sire, Dam) and for the PeopleTbl i used Owner, CoOwner, Breeder, CoBreeder. I'm guessing that since i probably won't reference these "virtual" tables anywhere else but inside the query there's no need to distinguish them with something "VT" (Virtual Table) on the end of the name.