How to write field values next to each other?

[ppataki]

Dear All,
I have a table that looks like the following:

Name ID
Parking 1
Eating 2
Parking 3

I would like to get the below, if possible with SQL, if not then with VBA:

Name ID
Eating 2
Parking 1;3


Your help would be very much appreciated!
Many thanks in advance!

 

[dbDamo]

You will have to be clearer with what you want.

If you just want a query to display the information this way then you can perform a simple concatenation in query design view, i.e.
NewQueryFieldName: [TableName]![Parking 1]&";"&[TableName]![Parking 2]

If this is not what you want to achieve then please provide more information

 

[ppataki]

I have tried what you suggested but I have no such fields as Parking 1 or Parking 2
There are 2 fields: Name and ID
I would like to write the values of ID grouped by Name next to each other separated with a semicolon, like 1;3
So based on the above example end result should look like this:
Name ID
Parking 1;3
Eating 2

Thank you!

 

[Kryst51]

You want it to appear like that in your table? If so that is a normalization issue, and not the right way to go. If not, then please excuse my question

 

[ppataki]

I am attaching a sample database, it contains the "before" table and the "after" table that I would like to create either with the help of SQL or VBA

I know that doing so the table will not be normalized, but still for me it would be very important to do so

Many thanks in advance!!

 

[dbDamo]

My apologies, I thought you had displayed your table's design. I don't think I can help you with this but I will look into it.

 

[Kryst51]

I have to question why it is important. If it is for reports, that should be handled in a different part, like a query or at the report level, if for a form, same thing, through a query.

If it is for exporting, then I would imagine that there is code you can implement so that it exports the way you want.

I can't think of any reason not to have a normalized database. You will not get much out of your db if it is not normalized.

Unfortunately, I don't know how to answer your question, but would ask you to reconsider your need for such a thing, and to maybe explaing the reason and ask if anyone can help you come up with a solution that sticks to sound db design rules.

 

[ppataki]

Anyone else could help with the code please?

Thank you very much in advance!

 

[Kryst51]

Check this site out for more info on normalization and its purposes.

 

[dcb]

fConcTB

strTIn, In Table
strTOut, Out Table
strGCol, Group By Column Name
strCCol, Column to be con
strDelim, Deliminator

Havent got a clue why you would want to do this but there we go

 

[dbDamo]

''DCB funny concatenation
''Why the hell not

Brilliant. I am impressed, but like you I struggle to see how this could be remotely useful. You will be asked about the duplicate rows though...

 

[dcb]

Brilliant. I am impressed, but like you I struggle to see how this could be remotely useful. You will be asked about the duplicate rows though..

I have used temp tables in the past that have looked like this where another application requires a special type of delimited data import.
Sometimes it can make sense to step out of the "rules" and would use a text field to indicate many PK's (I can feel the guns being drawn as I type)

An example would be
tbl1
pk | name
a01 | area1
a02 | area2
a03 | area3 etc.

tbl2
gumtree |a01,a05,a02,a07
bluetree |a03,a01,a05

you would extract this with a sql "LIKE" - only thing to be careful with is a1 is like a11 therefore the a01

Duplicates: I can only assume the temp table would be cleaned

Cheers

 

[ppataki]

Thank you very much!!!!
The reason is the same as dcb mentions, data needs to be transferred to another application
Many thanks again!