Appending column text

[polo]

hello all,

I have a table with the following columns

ContractID
CostValue
CostType - string

The contractID is not unique.

What I am trying to do is for each contractID I want the cost type

For example if I had 4 rows
ContractID CostType Cost
1 a
1 b
1 c
1 d

I want the following returned from a select statement:
Contact ID CostType Cost
1 a,b,c,d


Can anyone suggest on how I could do this?

Thanks for your help

 

[polo]

Anyone got any ideas on this? I am completely stuck on this and not sure what to do.... is there some function I can use to append text?
any info appreciated

 

[polo]

*bump* Sorry to post again but I'm sooo stuck on this... any info apprecated! I think a search on site but couldnt find anything to help!

to re-hash what I said above:

I have a table that looks somewhat like this:

clientNo Cost type
0628 Lighting
0628 Gas
0628 Elec
0628 buildings

There are several hundred listings of clients and I need an output of clientno, cost type

ie. for above example I need:
0628 Lighting,Gas,Elec,buildings

I need that sort of output for every clientno listed in the table.... its a nightmare!

Please if anyone has suggestions please respond

 

[Dembrey]

You need to look at Crosstab Queries in order to do what you are asking.

HTH.

 

[polo]

Thanks Dembrey for the response I am unfamiliar with CrossTab Queries but had a look online.... I don't see how this will give me the result I need... instead I can get the number of cost types per customer but not the result of the cost types being appending into one column.... any ideas?

 

[Jon K]

You will need to use VBA code. Take a look at this thread:
How to combine records in a table
http://www.access-programmers.co.uk/forums/showthread.php?t=64251
.

 

[polo]

Thank you jonK for the link

I've been working on using a similar method but find it is incredible slow. This is probably due to the fact that it is pulling information from a query rather than a table... is there anyway you can import the data a query produces into a table?

Any info appreciated

 

[polo]

Scrap last message!

Ignore above post....

Ok thank you very much Jon that link was great help!

In the return set now it returns several of the same instances of a cost type ( I know this data is so frustrating!!!!) is there a way I could build in that I only want unique cost types?

Thanks again for all your help!

 

[Jon K]

You can build a query to retrieve a unique list of clientNo, Cost type:-

Select Distinct clientNo, [Cost type] from [table/query name]

and work from that unique list.
.