Identifying available sizes

[geocan2006]

Hi, I am new in this form and I hope someone helps me to resolve my problem,
I have a table containing Model name including produce sizes after hyphen "-", but each model can contain in many different sizes
Example

Model
SH001-24
SH001-36
SH001-48
SH004-12
SH004-18
SH004-20
SH004-22

What I want as query is

Model	Size
SH001	-24, -34, -48
SH004	-12, -18, -20, -22

So basically the task is to remove all before dash, and list as unique record and combine all available size in same model in Size Column
Thank you in advance.

[CJ_London]

your data is not really set up correctly to do what you want - I don't know what business you are in but I would have expected the model and size to be in different columns, perhaps even in separate tables.

The first thing you need to do is to split your field into the two elements. Based on the examples provided the model part is all the same length of 5 chars so to get this part use the left function - Left(model,5)

and your sizes are all two characters, so use the right function - Right(model,2)

So create a query called say ModelSplit

SELECT Left(model,5) AS ModelName, Right(model,2) AS ModelSize
FROM myTable

to get the sizes to appear as one field, you will need to use the concatrelated function which you will find here http://allenbrowne.com/func-concat.html. Copy and paste it into a new module

now create another query which will be something like

SELECT DISTINCT ModelName, Concatrelated("ModelSize","ModelSplit","ModelName= '" & ModelName & "'") AS Sizes
FROM ModelSplit

If your model name and/or size has varying lengths then you will need to use a different method for extracting them. Not prepared to go into that now because it may not be necessary and before offering a solution we would need to know the entire range of possibilities

[Micron]

Retracted.
Duplicate information.

[geocan2006]

Thank you for respond, However as I instructed I created a module, Copy and paste it and compile it but the function dasn't appear anywhere, it identicates as Unidentified function 'Concatrelated" in expression. is it possible to email a functioning access file?
Thank you so much

[Micron]

You can post a db here. See if this helps you to do that
https://www.accessforums.net/showthread.php?t=70301

[orange]

geocan,

AJAX gave you the link to the ConcatRelated material.

[geocan2006]

I created 2 sets of tables, one is my sample the other one is based on the instructions Allen Browne's
Thanks

[CJ_London]

you should read my advice more closely. This works

SELECT DISTINCT Query1.Modele, concatrelated("Size","Query1","Modele='" & [modele] & "'") AS Sizes
FROM Query1;

and produces this

Modele	Sizes
SH001	24, 36, 48
SH004	12, 18, 20, 22

[geocan2006]

My problem is the concatrelated function dasnt work and I don't know why it's not working

[geocan2006]

Can you please check if the module is working and attach it?
Thanks

[CJ_London]

the file you sent in post #7 works, including the module and function. I didn't change anything other than write the query.

Perhaps it is a setting on your computer which is preventing code from running?

[geocan2006]

Thanks, that was the setting.
Best!!