Concatenate Function - Having Issues with DConcat

[qcjustin]

I am trying to use the DConcat (or any other function;there are several DConcat found at http://eileenslounge.com/viewtopic.php?p=48878#p48878) to group and concatenate the data.
The data is in this format:

ColumnA	ColumnB
3-4514707656	3C00005
3-4514707656	4G3-2113
3-4514707656	4H3-0412
3-4514707656	6C90000R

What I would like to see is

ColumnA	ColumnB
3-4514707656	3C00005,4G3-2113,4H3-0412,6C90000R

Right now i have this in my query: PartsUsed: DConcat("ColumnB","Table1",[LowEndTable].[SR #]=[Table1].[ColumnA])

Even with a join and the operator [LowEndTable].[SR #]=[Table1].[ColumnA],

The output is putting ALL part #s in one field, regardless of the order#.
What am i missing?

[pbaldy]

I don't think you got the third argument correct. Try

PartsUsed: DConcat("ColumnB","Table1","[SR #]=" & [ColumnA])

[qcjustin]

This now gives me Run Time Error 3061. Too Few Parameters. Expected 1.
I have it selecting the first field, and the second field in the query is now this:

PartsUsed: DConcat("ColumnB","Table1","[SR #]=" & [ColumnA])

[pbaldy]

First guess is that something is spelled wrong.

[qcjustin]

I dont think thats the case however. Here is a link to the test copy of the DB.
When i make the change you mention in a new test environment, it seems to freeze.
When i switch back, it runs quickly, but using all part numbers, not just the ones on this order (4 total)

http://www.filedropper.com/testdb

[Homegrownandy]

You have a lot more issues to deal with before trying to concatenate.

Low end table needs an auto number as the primary key. Then all of the numbers/text you add there is assigned a number which will be used as a reference in your database.

you have numbers that don't exist in the low end table in a linked field in table 1.

ColumnA	ColumnB
1	3C00005
1	4G3-2113
1	4H3-0412
2	6C900006

That's what it should be like in my opinion then you add the long numbers only once in the other table. (providing you set up the relationships.)

also I would name your columns as descriptively as possible whilst keeping them short. Column A should be SR_ID and you also need that field in LowEndTable

[qcjustin]

The SR# is the primary key for Low End Table.
Its OK that i have numbers that don't exist, that was the test.
The only SR I want results for in the test is the one in LowEndTable.
That one SR should concatenate these 4 parts.

ColumnA	ColumnB
3-4514707656	3C00005
3-4514707656	4G3-2113
3-4514707656	4H3-0412
3-4514707656	6C90000R

The naming of the columns should not matter, someone has to have a simple answer for this.
If the attached sample could me remedied, i would appreciate it. I have spent hours already on this with no avail.
No one seems to explain it in a matter that makes sense to me.
In my mind, it seems VERY simple.
Group by this field, and split the other field into multiple dynamic outputs separated by a comma.
The reverse matching back to the key seems to be the only problem.
The "key" would be SR #. The other things are part numbers.

[Homegrownandy]

Okay but 75% the records in your column A don't exist in the Low end table. You can have long names as unique ID numbers but when you add more data and fields surely 1 is better than 11111111111 for all of your data.

This is your choice and if you don't hold a lot of data it wont matter. Its just not good practice.

Did you want all records in table 1 to be linked to lowendtable?

If so you need to properly set up the relationship and ensure all your SR# are in the table.

[InsuranceGuy]

Column A is a text value so you need to quote the test condition.

PartsUsed: DConcat("ColumnB","Table1","[SR #]='" & [ColumnA] & "'")

See:http://www.techrepublic.com/blog/mic...s-in-vba-code/


Jeff

[qcjustin]

I made this correction, and with only 1 record to match, it freezes. Any ideas? Can someone look in the sample DB?

[pbaldy]

You'll have better luck with people looking at the sample if you attach it to a post here (compact and then zip). Many people don't like going to other sites to download.

[ssanfu]

You have a lot more issues to deal with before trying to concatenate.

I have to agree with Homegrownandy on this issue.

You shouldn't use spaces, punctuation or special characters (underscore is the exception) in object names.... only letters and numbers.
FYI, the hash sign (#) is a date delimiter in Access.


You might check out Allen Browne's code at :
Concatenate values from related records
http://www.allenbrowne.com/func-concat.html