Concatenate multiple fields in access table

[handsrfull]

I am trying to concatenate multiple fields into one based on criteria. I have no idea as to whether it is possible to do this in VBA or SQL or both.

I have a table with:

ItemNo as one field
Tool1 as another field
Tool2 as another field
Tool3 as another field
Tool4 as another field

I want to have the concatenate show up something like this:

ItemNo (Tool1), (Tool2), (Tool3), (Tool4)

However, if any of those fields are Null then, I DO NOT want to show them as:

ItemNo (), (Tool2), (Tool3), (Tool4)

I would rather show them as:

ItemNo (Tool2), (Tool3), (Tool4)

Any insight would be helpful.

 

[HarleyQuinn]

thread701-1444471

This might help you.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[handsrfull]

That post selects multiple records from one column. I am trying to select data from multiple columns and pull together to form one string.

 

[HarleyQuinn]

Sorry, had slightly misunderstood the question, this should work for you:

SELECT ItemNo & " " & IIf(Tool1<>"","(" & Tool1 & "), ","") & IIf(Tool2<>"","(" & Tool2 & "), ","") & IIf(Tool3<>"","(" & Tool3 & "), ","") & IIf(Tool4<>"","(" & Tool4 & ") ","") AS ConCatField
FROM tblYourTable;

As it's a quick example you'll have to play about with some of the comma's if the fields aren't there but it's not fun if you don't have to do something

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[fneily]

First, post in one of the seven Access forums such as Microsoft: Access Queries and JET SQL or Microsoft: Access Tables and Relationships.

If what you presented is a table with the stated fields, your table is not normalized. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Your table fails one part of the first normal form, repeating column headings. You have Tool1, tool2, etc. Drop the number you get tool, tool, tool. This is your problem. You'll have blank cells under some of the column headings. This creates variable length records which are not allowed in relational databases. Thus your concatenation problem.

Your table should be:
ItemNo. Tool
124 Ax
124 hammer
543 Ax
654 Awl
etc.

Then you won't have any blank fields and you can then concatenate all related records regardless of how many tools there are.

For any other responses, post correctly.

 

[fneily]

HarleyQuinn - what happens when 127 more tools are added?

 

[HarleyQuinn]

HarleyQuinn - what happens when 127 more tools are added?

The DB becomes un-usable.

I agree that on the face of it this isn't a well designed DB, but I also understand (as I'm sure you do too) sometimes people have to work with what they are given (hence my posting a solution).

While I'm 100% for normalisation (as anyone who design's DB's should be), this isn't my DB so I'm in the 'work with what you've got camp' at the minute, I know what will happen if 127 more tools are added and thankfully it's not my problem to sort the thing out.

Sorry, the above isn't meant as a rant



HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[fneily]

HarleyQuinn - I don't mind rants. That what makes Tek-tips fun, to me anyway. Debating different strategies and philosophies.
Yeah, I guess I should feel sorry for those that HAVE to deal with the database given to them. But then again, they can still learn the correct way it should be done and maybe relate to the original creator that they may have made a bad table. Oh well.

 

[HarleyQuinn]

fneily - I certainly agree with what you're saying about hopefully teaching people to take better practices forward in the future.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.