Access Concatenation Crazy!

July 25, 2012 5:52 PM

In Access 2010 I've come across a need to concatenate fields from one column in one table into a single, comma delmited value in another field in the same table (or in another table if I absolutely must).

I've been working on an access database and have hit a wall after MANY hours of work and being THAT close to wrapping up my work.

I've read much on how todo this and it seems Allen Browne has it licked I've written the module as instructed and that comiles just fine. I've had many other different errors that I've gotten past and am down to getting a syntax error.

I have a table called tbl_phoneSources. The table is currently laid out like this:
Phone LastName Sources
__________________________
5552515392 Gates Source4
5552515392 Gates Source2
5552515392 Gates Source1
5552201356 Ellison Source3
5552201356 Ellison Source1
5652201356 Jobs Source4
5652201356 Jobs Source1

I want it to become:
Phone LastName Sources
_________________________________________
5552515392 Gates Source4, Source2, Source1
5552201356 Ellison Source3, Source1
5652201356 Jobs Source4,Source1

After creating the module I have finally come down to using this expression in a fourth AS field in my query. Despite seeing MANY examples illustrating very much the same thing I want to do I just can't get past my syntax errors.

Here's what seems to work for most who also have issues:
ConcatRelated("source","tbl_phoneSources","[phone] = #" & [phone] & "# AND [lastName] = '" & [lastName] & "'")

Did I make a typo? Do I just not understand this?

posted by horseblind to computers & internet (13 answers total)

Just reading that page, I'd check the data type on your phone number field.

In the description of the arguments it says:

3.Thirdly, supply the filter to limit the function to the desired values. This will normally be of the form:
"[ForeignKeyFieldName] = " & [PrimaryKeyFieldName]
If the foreign key field is Text (not Number), include quote marks as delimiters, e.g.:
"[ForeignKeyFieldName] = """ & [PrimaryKeyFieldName] & """"

...and I normally store phone numbers as text - which would require (escaped) double quotes.
posted by pompomtom at 6:03 PM on July 25, 2012

Here's the verbose syntax error. I meant to include it but forgot.

Error 3075: Syntax Error In date in query expression'[phone]=#5652201356# AND [lastName]=".

All relevant fields have been stored as text.
posted by horseblind at 6:27 PM on July 25, 2012

OK, so try replacing each # with ""
posted by pompomtom at 6:32 PM on July 25, 2012

The above suggested solution now yields an empty destination field for the concatenation and a new error:
Error 3075: Syntax Error (missing operator) query expression'[phone]=#5652201356# AND [lastName]=".
posted by horseblind at 6:36 PM on July 25, 2012

We have a winner! Thanks! Big, big thanks! Will I do the same for a field populated by other text (not numbers as text, but plain text)? There will be one more field I'll have to concatenate in a similar manner?
posted by horseblind at 6:40 PM on July 25, 2012

Yeah, I think you'll want the escaped double quotes for all the text things. AFAIK "#" is only for dates.

(though I'm a bit unsure, as the single quotes you've used for lastname have obviously worked, and, on reflection, should work).
posted by pompomtom at 6:42 PM on July 25, 2012

I figured out the reason I should be using double quotes in a last name is that Names like O'Connor that include a single quote (or apostrophe) cause a syntax error. Just FYI.
posted by horseblind at 7:52 PM on July 25, 2012

Oh, I hate those names...
posted by pompomtom at 8:20 PM on July 25, 2012

Concatenating multiple values into a single column introduces a violation of 1NF (#4):

1) There's no top-to-bottom ordering to the rows.
2) There's no left-to-right ordering to the columns.
3) There are no duplicate rows.
4) Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
5) All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].
—Chris Date, "What First Normal Form Really Means", pp. 127–8[4]


You can easily avoid this by adding a child table 'tblSource' where each row has a foreign key to 'tbl_phoneSources'. I'm guessing the primary key in 'tbl_phoneSources' is lastname? Then a join gets you to a nice result.
posted by j_curiouser at 11:15 PM on July 25, 2012

As j_curiouser points out, if you're doing this to retain the concatenated data as a field in a database then you're doing it wrong and it will cause more problems than you are solving. And what happens when someone wants to attach a note to each source, or detect duplicate sources? Your data cries out for a multi-table approach, phone and lastname on one table (call it person, say) and source in another table with a foreign key to person. You can then use joins to present all sources for a person in the desired form.

It sounds like someone wants to summarise all sources for a person on a single line of a report, that is a report formatting problem, not a database structuring problem. Get a query which returns all sources for a given person and the task of concatenating those separated by commas is trivial.
posted by epo at 12:25 AM on July 26, 2012

Thanks for the tips. Agreed that this is a report formatting issue. I'll keep these approaches in mind in the future.

I'm aware of the issues in regards to 1NF as well. I protested. My Employer cares not about these things. This is a 4 BILLION dollar company where Excel spreadsheets are considered databases.

I'll be keeping the original tables intact so they should still meet 1NF requirements. The output of the discussed concatenation will be appended to a different table (in addition to a report they wanted a table saved in the database). This will be in a vendor specified format and used in a one time deal third party vended solution system reload (say that three times fast). The concatenation meets a requirement given to me by my PM, which was given to him by our vendor. There are few enough examples of the need for this concatenation that I suggested editing those few records by hand since the existence of other issues is known (those pesky " O' " names, for instance - btw, I was told getting rid of apostrophes in a name is not acceptable since people should see their own name on a screen in the manner they prefer - something tells me O' people are plenty accustomed to having the apostrophe nixed). Editing names (and other fields that are disparate and not easily automated) by hand seems to be acceptable but for some reason I was told to figure out the source field concatenation.

To top it off I find out my employer has me doing this as they are negotiating having the vendor do the very same work so the vendor will "certify" the data to be loaded. This is the busiest period in our project (that slipped its date three years ago) yet we apparently have time to waste. Grrr. /kvetching
posted by brokeaspoke at 6:51 AM on July 26, 2012

FYI, I posted the previous comment on behalf of horseblind as he can't get to metafilter at the moment.
posted by brokeaspoke at 6:56 AM on July 26, 2012

Ok. I've had to return to this. It mostly works out but causes syntax errors (you may have entered an operand without an operator) when a record contains a name with an apostrophe (single quote). I've read Allen Browne's write up on quotes and still can't figure it out. In those cases with a single quote the source field comes up empty. This also occurs in the case where the last name field is empty (according to my employer this is valid in some cases).

The code for this is as follows:
REMARK - Listing ID: "SOURCE: " & ConcatRelated("source","tbl_phonesorces","[phoneNo] = """ & [phoneNo] & """ AND [lastName] = '" & [lastName] & "'")

When I have tried to replace the single quote with double quotes in a manner described by Allen Browne I get no syntax error but the Origination Source field contains one of the possible sorces repeated many many times in nearly all of the records.

Code:
REMARK - Listing ID: "ORIGINATION SOURCE: " & ConcatRelated("source","tbl_phonesources","[phoneNo] = """ & [phoneNo] & """" AND "[lastName] = """ & [lastName] & """")

Can anyone help with this?
posted by horseblind at 4:39 PM on July 29, 2012