Looking for a clean way to do something not in the SQL lexicon...

I'm building an Excel comparison file, mirroring what somebody built
in Excel but I'm using an Access cursor of data. No problems
inserting into Excel, no binding issues.

Where I get hung up is concatenating multiple values to one cell --
which is good, right? That's terrible practice in DB. However, I'm
just outputting for visual comparison.

Example: if Garden1 has Tomato, Carrot, Cucumber, and Garden2 only has
Tomato, I need to take this:

locationName itemDesc itemCount
Garden1 Tomato 42
Garden1 Carrot 90
Garden1 Cucumber 12
Garden2 Tomato 14

And output this:
Cell1 Cell2
Garden1 Tomato, Carrot, Cucumber
Garden2 Tomato
(Sorry about the uninspired example -- staring out the window.)

Was thinking I'd write a scalar or subselect in the second cell -- but
then it occurred to me it might be more simple to write a little UDF
with a loop and a DLookup (?) to handle commas a little better (not
that it matters a ton).

Appreciate your insights as always,
Jeff

Hi Jeff,

Your sample data looks like it has been processed a little bit. You are
displaying what you want your data to look like at the end. But I have
no idea what the raw data looks like at the beginning, so it would be
quite difficult to offer any suggestions how to get to the end if you
are already there. Perhaps you could show a sample of what the raw data
looks like. What I am envisioning (guessing - from a relational
standpoint) is that your raw data might look something like this:

Detail Table
locID Item
1 carrot
1 carrot
1 tomato
1 carrot
2 tomato
1 cucumber
1 cucumber
2 tomato
2 tomato
1 tomato
1 carrot
...


Master Table
locId locName
1 Garden1
2 Garden2
3 Garden3
...

from here you could do a group by query to tally up the produce and then
crosstab that result. This is the whole point of a relational system
(RDBMS).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Processed? ;-)

So the data are in two table in 3NF. Roughly. What I'm representing
is well locations and geologic units -- rather than garden veggies.

My query generates:
LocationName LithologyCode
L1210 Tr
L1210 Tf
L1210 Tfsg
L1210 Tfsh
L1210 Tgsb
L1210 Tgu
L1210 Tgo
L1210 Tggc
L1210 Tgwr
L1210 Tgud
L1211 Tpr
L1211 Tr
L1211 Tfsg

....and what I would like to output through the query is a comma-
delimited list of the LithologyCode of each LocationName.
L1210 Tr, Tf, Tfsg, Tfsh, Tgsb, Tgu, Tgo, Tggc, Tgwr, Tgud
L1211 Tpr, Tr, Tfsg

What I suspect I need to write a VBA function to loop through the
records for each LocationName and concatenate each subsequent
LithologyCode with a comma and return that string -- in other words
write a UDF.

Here's where I guess that might go, in place of the "Max" grouping
function...
SELECT tblLocation.LocationName, Max((Select first(LithologyCode) from
tblLithologicDescription U, tblLocation L where U.LocationID =
L.locationid)) AS Lith
FROM tblLocation
GROUP BY tblLocation.LocationName
HAVING (((tblLocation.LocationName) In ("L1210","L1211")));

Anyway, I'm just fumbling my way through VBA, it seems like this is
one of those cats that's been skinned before, and I wondered if Access
already had a tool inside. (Last year wrote a confusing nested scalar
query that's a nightmare to maintain, before realizing that - as long
as I was committed to Jet - all would've been easier with the
DLookup() function.)

Thanks for your thoughts,
jn


On May 4, 10:01*am, Rich P <rpng... (AT) aol (DOT) com> wrote:

OK. I think I am getting your picture straight now. I will make up
some fake data and see if I am on the same page and then suggest a
method

'--this would be all text fields -- varchars
Loc Item
1 a
1 b
1 c
2 d
2 e
2 f
3 g
3 h
3 i

say this data resides in tbl1 -- store the results in tbl2

Dim RS As Recordset, RS2 As Recordset
Dim s1 As String, s2 As String
Set RS = CurrentDB.OpenRecordSet("Select * from tbl1 Order By Loc, Item)
Set RS2 = CurrentDB.OpenRecordset("tbl2")
RS.MoveFirst
s1 = RS(0)
Do While Not RS.EOF
If s1 = RS(0) Then
If s2 = "" then s2 = RS(1)
If s2 <> "" Then s2 = "," & RS(1)
else
RS2.AddNew
RS2(0) = RS(0) & s2
RS2.Update
s2 = ""
s1 = RS(0)
End If
RS.MoveNext
Loop

The other way would be to create a crosstab query of your date and just
loop through the cross tab query and perform the concatenation on the
fly.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

http://www.mvps.org/access/modules/mdl0004.htm
It is a VB function called fConcatChild() that does just what you are
trying to do.

Have fun.

Bob


Jeff <jnorville (AT) gmail (DOT) com> wrote in
news:55517b6c-524e-4713-8460-9e099362615d (AT) z13g2000prh (DOT) googlegroups.co
m:

That's the ticket -- had to be out there somewhere. Thanks all.

On May 4, 2:11*pm, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:

There are lot's of those functions out on the web. I prefer
the one at:
http://www.rogersaccesslibrary.com/f...-26cz2224.html
--
Marsh


Jeff wrote:

Marshall Barton <marshbarton (AT) wowway (DOT) com> wrote in
news:lrj3u59okvcsiv4o4qneihjmu5amt4hdjm (AT) 4ax (DOT) com:

soon prefer it to Dev's.

Thanks.