ConcatRelated() - query or tables

Question

I have similar issue to this question Combine values from related rows into a single concatenated string value.

I've got two queries:

This is what it looks like now without ConcatRelated():

I need to get return:

I tried to use this SQL:

SELECT DISTINCT
Q_Fakt1.FakturaID,
Q_Fakt1.DatumVystavenia,
Q_Fakt1.DatumSplatnosti,
Q_Fakt2.Pismeno,

ConcatRelated(
"pismeno",
"Q_Fakt2",
"FakturaID = '" &  [Q_Fakt1]![FakturaID] & "'"
) AS Letters

FROM Q_Fakt1 INNER JOIN Q_Fakt2 ON Q_Fakt1.FakturaID = Q_Fakt2.FakturaID;

Result is 7× popup:

ConcatRelated() Error3464: Data type mismatch in criteria expression.

I did the same with Tables but I have little bit more complicated Relations so...

https://i.stack.imgur.com/TM7Cu.png

SQL:

SELECT DISTINCT
Faktury.FakturaID,
Kategorie.Oznacenie,
Faktury.DatumVystavenia,
FakturujemVam.FakturujemVamID,
FakturyDetaily.FakturujemVam,
[DatumVystavenia]+[splatnostFaktury] AS DatumSplatnosti,

ConcatRelated("Oznacenie","kategorie","FakturaID = '" & [FakturaID] & "'") AS Letters

FROM Kategorie INNER JOIN (Faktury INNER JOIN (FakturujemVam INNER JOIN FakturyDetaily ON FakturujemVam.FakturujemVamID = FakturyDetaily.FakturujemVam) ON Faktury.FakturaID = FakturyDetaily.Faktura) ON Kategorie.KategoriaID = FakturujemVam.Kategoria;

Result is 6× popup:

ConcatRelated() Error3061: Too Few parameters. Excepted 1.

Where did I go wrong? Thank you for Help

score 1 · Answer 1 · answered Jan 07 '18 at 21:52

1

That's because you're using string delimiters when you're not using a string.

Remove those delimiters, and it will work fine:

ConcatRelated("Oznacenie","kategorie","FakturaID = " & [FakturaID] ) AS Letters

answered Jan 07 '18 at 21:52

Erik A

31,639
12
42
67

Thanks for your answer. With 2 queries - removing delimeters doesn't work, but with tables works almost fine. but it shows for example 2, 9, 9. Because in table KATEGORIE is "a" under key 1, and "z" is under 9. any chance to change it to shows second column? for better understand: [link]https://i.imgur.com/J59Qphz.png – Miroslav Daniš Jan 08 '18 at 20:33
Sorry, I don't understand what you're trying to say. If you're filtering by a column with letters, you do need the delimiters. – Erik A Jan 08 '18 at 20:37
I'm sorry I make mistake: With two queries Q_fakt1 & Q_fakt2 - it works (without those apostrophes) This is what I mean: [link]https://i.imgur.com/DnblSts.jpg - Is there any chance you'll help me via skype, or TeamViewer ? .. I'm begginer so I know, I do simple things more complicated then they really are. But I'm kinda lost in this point. – Miroslav Daniš Jan 08 '18 at 21:12
I'm sorry, but I don't do personal help other than helping on Stack Overflow. With these current screenshots, I'm at a loss too, it looks like it should work. I don't know where the function is getting those numbers from, and why it isn't returning letters. – Erik A Jan 08 '18 at 21:19
OK. I created one more database, based on the previous one, but with more simple titles then original. Will you please look at it ? Will you try find solution for me? I'm totally lost. I tried to change Key from num, to Short Text, but it didn't work anyway. If you cannot / or you don't know, do you know someone who might know how to solve this? Thanks. – Miroslav Daniš Jan 09 '18 at 17:21
I truly don't know what's causing this problem. You can try asking a new question about this. As far as I'm concerned, I fixed the error you were receiving, and I don't know why the function isn't working as intended with the error gone. – Erik A Jan 09 '18 at 17:34

Miroslav Daniš · Accepted Answer · 2018-01-09T20:22:27.593

SOLVED:

STEP 1

Create Query to merge more tables in one

Datasheet View

Design View

STEP 2

Create another Query & Use ConcatRelated()

Design View

SQL:

SELECT
Q_Part_Bill_Num2.NumBill,
Q_Part_Bill_Num2.C_Mark,

ConcatRelated(
     "C_Mark",
     "Q_Part_Bill_Num2",
     "Q_Part_Bill_Num2!NumBill = " & [Q_Part_Bill_Num2]![NumBill]
     ) AS PartBillNum2

FROM Q_Part_Bill_Num1 INNER JOIN Q_Part_Bill_Num2 ON Q_Part_Bill_Num1.NumBill = Q_Part_Bill_Num2.NumBill;

STEP 3 - Optional

Edit MODULE to delete / change separator ", "

STEP 4

Create One Last Query to Concatenate everything together. Design View

SQL:

  SELECT DISTINCT

Q_Part_Bill_Num1.PartBillNum1,
Q_Part_Bill_Num3.PartBillNum2,
[PartBillNum1] & [PartBillNum2] AS [Full]

FROM 
(T_Bills INNER JOIN Q_Part_Bill_Num1 ON T_Bills.Bills_ID = Q_Part_Bill_Num1.Bills_ID)
INNER JOIN (Q_Part_Bill_Num2 INNER JOIN Q_Part_Bill_Num3
ON (Q_Part_Bill_Num2.NumBill = Q_Part_Bill_Num3.NumBill)
AND (Q_Part_Bill_Num2.C_Mark = Q_Part_Bill_Num3.C_Mark))
ON Q_Part_Bill_Num1.NumBill = Q_Part_Bill_Num2.NumBill;

Use DISTINCT to avoid duplicates. I Hope this will help someone.

Thank you all, for your time :)

ConcatRelated() - query or tables

2 Answers2

STEP 1

STEP 2

STEP 3 - Optional

STEP 4