Join cells from two or more rows in one cell

[Maikel]

Dear All,

Is it possible to join cell content from two or more rows into one cell? see below:

Location(key) Sample(key) Property1 Property2
South-America 1 Blue White
South-America 2 Black White
South-Amarica 3 Blue White
Africa 1 Blue Black
Africa 2 Blue White

After query:
Location(key) Sample(key) Property1 Property2
South-America 1(first) Blue-Black White
Africa 1(first) Blue White-Black

thanks in advance,



Maikel

[Robeen]

I have done what you are trying using VBA.
I don't know of a way to do it in a Query

Can you explain exactly what you need as an end product?

What is the meaning of "(first)" here:

South-America 1(first) Blue-Black White
Africa 1(first) Blue White-Black

[Maikel]

Thanks Robeen.

I have a database holding data for an experiment where a maximum of four samples are taken (four records). Each record is indentified by a composite key with made up of 5 fields. In addition to those 5 key fields I have 14 other fields of which some are numbers and some are text fields.

I created a select query in which I use the totals function to calculate means for the Numbers, but I also want bring the text values together in one field. To do that is use the option "Group by first" (not the best option of course) . But since the text values differ i want to be able to just add all of them together in that one field, thus bringing together the maximum of four rows into one row.

[Robeen]

Can you paste your Select Query here so we can look at it?

[Maikel]

SELECT tblLeaf_i.[TRIAL_NO], tblLeaf_i.[BANK_LOC], tblLeaf_i.[ACC_NO], First(tblLeaf_i.[SAMP_NO]) AS EersteVanSAMP_NO, tblLeaf_i.[REP_NO], First(tblLeaf_i.[PLNT_NO]) AS EersteVanPLNT_NO, First(tblLeaf_i.[CHR_DTE]) AS EersteVanCHR_DTE, First(tblLeaf_i.[PLNT_DTE]) AS EersteVanPLNT_DTE, First(tblLeaf_i.[FLOWERS]) AS EersteVanFLOWERS, Avg(tblLeaf_i.[LF_SCARS]) AS GemVanLF_SCARS, Avg(tblLeaf_i.[PT_ANGLE]) AS GemVanPT_ANGLE, First(tblLeaf_i.[APL_COL]) AS EersteVanAPL_COL, First(tblLeaf_i.[FFXL_COL]) AS EersteVanFFXL_COL, First(tblLeaf_i.[PT_COL]) AS EersteVanPT_COL, First(tblLeaf_i.[LV_COL]) AS EersteVanLV_COL, First(tblLeaf_i.[YL_PUBE]) AS EersteVanYL_PUBE, Avg(tblLeaf_i.[PT_LNGTH]) AS GemVanPT_LNGTH, Avg(tblLeaf_i.[LB_NUM]) AS GemVanLB_NUM, First(tblLeaf_i.[CNTR_LS]) AS EersteVanCNTR_LS, Avg(tblLeaf_i.[LB_LNGTH]) AS GemVanLB_LNGTH
FROM tblLeaf_i
GROUP BY tblLeaf_i.[TRIAL_NO], tblLeaf_i.[BANK_LOC], tblLeaf_i.[ACC_NO], tblLeaf_i.[REP_NO];

This is the result of my select query in SQL view. I'm using a dutch version of ms access, so "Eerstevan" just means FirstOf and "GemVan" means AverageOf.

[Robeen]

Hi Maikel!

I read through this entire thread again - just to see if there was something I had missed before. I couldn't find anything that I had missed.

I have done what you are trying to do using a Function in a Module [the Function gets called from a Macro]. The Function uses a SQL query recordset and loops through the records and processes the data to produce results similar to what you need. I would be happy to help you with that.

Meanwhile, here is another way of achieving what you are trying:
http://stackoverflow.com/questions/2...-i-want-to-run

I have not tried the above but it looks like it would achieve what you need even though the way I did it is different.

Let me know if this helps.

[rpeare]

you can do this with a crosstab query if the 'color' field doesn't all have to be in the same field.

That way you could also control the order (alphabetical or some other order if you set it up correctly)

What a crosstab can do is take your original data:

Location      Sample Property1 Property2
South-America 1      Blue      White
South-America 2      Black     White
South-America 3      Blue      White
Africa        1      Blue      Black
Africa        2      Blue      White

And and turn it into this:

Location       Black Blue White
South-America  1     2    3
Africa         1     2    1

Where you can count the number of instances the item occurs in the list as well. Or you can actually show the label

Location       Black Blue White
South-America  Black Blue White
Africa         Black Blue White

And if you had an item that was missing a 'color' there would just be a blank field.

[Maikel]

Well rpeare, i will look at that posibility.

Also Robeen, thanks for the link. You think i could take a look at that code of yours?