Can I do this with a query?

Kevin Tipple

unread,

Jan 24, 1997, 3:00:00 AM1/24/97

to

I've got a question for those in the know. I'm using v7.0 and will attempt to
visualize my problem below:

I'm trying to go from this query result (mutiple presenters possible for each
conference session, with session and presenter tables in a 1-to-many
relationship by Session#):

... to a table or query like this (preserving the original tables):

_____________________________
Session# Presenter
_____________________________
1 | Fred, Barney, Wilma
2 | Betty
3 | Bam Bam, Dino

Is this possible through the QBE grid, or do I have to go to SQL or write some
code for this puppy?

Many thanks in advance from a tired, idea-less programmer who's been sleeping
very little lately.

Kevin

Sherry Rascal Thiele

unread,

Jan 24, 1997, 3:00:00 AM1/24/97

to

kti...@soemadison.wisc.edu (Kevin Tipple) wrote:

>Kevin

Ok Kevin,

To get you started, I believe that it's a CrossTab query. Maybe you
can read up on it and learn it.

I must admit that 3 years with Access and previous to that Paradox and
I don't have a handle on Cross tabs. Sigh.

I can't dive either, or bowl, but I can shoot darts!

--Sherry

Larry Tubbs

unread,

Jan 24, 1997, 3:00:00 AM1/24/97

to

Hello Kevin,

I don't see an easy way to do this with the QBE or SQL, but it is very easy
to do with VBA/Access Basic. What you need to do is make a recordset based
on your first table, make sure you sort it the way you have it listed
below:

Dim db as Database
Dim rs as Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Table1.* FROM Table1 ORDER BY
[SessionNo]")

Next, walk the recordset, building a string for each set of records with a
like SessionNo and when the SessionNo changes, you can write that string
out to a secondary table:

Dim rsTarget as Recordset
Dim strString as String
Dim lnPrevSession as Long

Set rsTarget = db.OpenRecordSet("tblTarget") ' target table
strString = ""
lnPrevSession = 0
For Each rcd in rs
If lnPrevSession <> 0 Then
If lnPrevSession <> rcd![SessionNo] Then
rsTarget.AddNew
rsTarget![SessionNo] = lnPrevSession
rsTarget![Presenter] = Left (strString, Len(strString) -2)
rsTarget.Update
strString = ""
End If
End If
lnPrevSession = rcd![SessionNo]
strString = strString & rcd![Presenter] & ", "
Next
rs.Close
rsTarget.Close
Set rs = Nothing
Set rsTarget = Nothing

That should do it. The For Each ... Next loop will loop through each
record in Table1 building the Presenter string as it goes. Whenever the
SessionNo field changes value (lnPrevSession <> rcd![SessionNo) then the
record with the Presenter string (strString) is inserted in the target
table (tblTarget, rsTarget) and reset the string to "". Notice that the
line:

rsTarget![Presenter] = Left (strString, Len(strString) -2)

strips the last 2 characters of strString. This is to take care of the
trailing ", " characters. This should work for you. Be sure of a couple
of things, the table that you are appending into ("tblTarget" in my example
above) should be empty. You can run a delete query on it before you run
this code if you want to be sure. Also, the table needs to be the proper
format (the same as the first table), but be sure that there is enough room
in the Presenter field for the combined string. The field lenght defaults
to only 50 characters, and the max is 255 for a Text datatype.

I hope this helps,

Larry Tubbs, MCPS, MCSD
Silverleaf Vacation Club
mailto:ltu...@flash.net
http://www.flash.net/~enigma/

Kevin Tipple <kti...@soemadison.wisc.edu> wrote in article
<5caqna$1c...@news.doit.wisc.edu>...

Gary Bender

unread,

Jan 24, 1997, 3:00:00 AM1/24/97

to

Sherry "Rascal" Thiele <she...@pnetsys.com> wrote in article
<5cb3sp$m...@news.jump.net>...

> kti...@soemadison.wisc.edu (Kevin Tipple) wrote:
>
> >I've got a question for those in the know. I'm using v7.0 and

> [ ... ]

>
> To get you started, I believe that it's a CrossTab query. Maybe you
> can read up on it and learn it.
>
> I must admit that 3 years with Access and previous to that Paradox and
> I don't have a handle on Cross tabs. Sigh.
>
> I can't dive either, or bowl, but I can shoot darts!

Sherry,
Perhaps you should present a paper at the next Access Developers Conference
on the interaction of side interests and query design.

You have found a perfect 1.000 correlation coefficient! I bowl 165 (last
year in a league), was a diver in high school, and cannot throw darts.
But, I am pretty good with CrossTab Queries.

-- Gary

Jonathan P. Lynch

unread,

Jan 26, 1997, 3:00:00 AM1/26/97

to

Sherry,

If you want it on a report, you could mess around with format events.
That's probably the most efficient (well, quickest runtime, anyway), but
you'd be far better off writing a little bit of VBA code to do the trick.

Alternatively, use my DConcat function (attached below). It works just
like Dlookup, except it doesn't just return the first item found in the
recordset. Instead, it returns a string containing the concatenation of
the value of the supplied expression for all records which meet the
supplied criteria. For example, for your data:

?DConcat ("[Presenter]","Source Query","[Session#] = 1")

would return the string:
Fred, Barney, Wilma

where "Source Query" is the name of the Query returning your data.

If using it in the QBE or a SQL statement, you would want:

DConcat ("[Presenter]","Source Query","[Session#] = " & [Session#])

The arguments it takes are as follows:

DConcat (EXPRESSION, TABLE or QUERY, CRITERIA, DELIMITER)

(just like Dlookup, but with a fourth optional argument which defaults to
",")

It's really useful for summarizing the many records of a one-to-many
relationship on queries and reports.

Hope you find it useful.

Jonathan

--
Jonathan P. Lynch
email: ly...@medgen.iupui.edu
Web: http://medgen.iupui.edu/~lynch

Function DConcat(ByVal strExpr As String, ByVal strTable As String, ByVal
strCriteria As String, Optional varDelimiter As Variant) As Variant

Dim db As DATABASE
Dim rst As Recordset
Dim varReturn As Variant

If IsMissing(varDelimiter) Then
varDelimiter = ","
End If

If IsNull(varDelimiter) Then
varDelimiter = ""
End If

Set db = CurrentDb
If strCriteria = "" Then
Set rst = db.OpenRecordset("SELECT " & strExpr & " From " &
strTable, DB_OPEN_SNAPSHOT)
Else
Set rst = db.OpenRecordset("SELECT " & strExpr & " From " &
strTable & " Where " & strCriteria, DB_OPEN_SNAPSHOT)
End If
If rst.RecordCount <> 0 Then
While Not rst.EOF
varReturn = varReturn & varDelimiter & rst(0)
rst.MoveNext
Wend
varReturn = Mid(varReturn, Len(varDelimiter) + 1)
Else
varReturn = Null
End If
DConcat = varReturn
rst.Close
Exit Function
End Function
-----

Sherry "Rascal" Thiele <she...@pnetsys.com> wrote in article
<5cb3sp$m...@news.jump.net>...
> kti...@soemadison.wisc.edu (Kevin Tipple) wrote:
>

> Ok Kevin,

>
> To get you started, I believe that it's a CrossTab query. Maybe you
> can read up on it and learn it.
>
> I must admit that 3 years with Access and previous to that Paradox and
> I don't have a handle on Cross tabs. Sigh.
>
> I can't dive either, or bowl, but I can shoot darts!
>

> --Sherry
>
>

Colsoft Pty Ltd

unread,

Jan 27, 1997, 3:00:00 AM1/27/97

to

Gary Bender wrote:
>
> Sherry "Rascal" Thiele <she...@pnetsys.com> wrote in article
> <5cb3sp$m...@news.jump.net>...
> > kti...@soemadison.wisc.edu (Kevin Tipple) wrote:
> >
> > >I've got a question for those in the know. I'm using v7.0 and

> > [ ... ]

> >
> > To get you started, I believe that it's a CrossTab query. Maybe you
> > can read up on it and learn it.
> >
> > I must admit that 3 years with Access and previous to that Paradox and
> > I don't have a handle on Cross tabs. Sigh.
> >
> > I can't dive either, or bowl, but I can shoot darts!
>

> Sherry,
> Perhaps you should present a paper at the next Access Developers Conference
> on the interaction of side interests and query design.
>
> You have found a perfect 1.000 correlation coefficient! I bowl 165 (last
> year in a league), was a diver in high school, and cannot throw darts.
> But, I am pretty good with CrossTab Queries.
>
> -- Gary
>
>

But your sense of humour seems to be lacking (or maybe just
'different').
--
Bob Collinson Colsoft Pty Ltd
col...@tplex.com.au http://www.tplex.com.au/~colsoft

Alan Edwards

unread,

Jan 28, 1997, 3:00:00 AM1/28/97

to

In <5cb3sp$m...@news.jump.net> she...@pnetsys.com (Sherry "Rascal" Thiele) writes:
>kti...@soemadison.wisc.edu (Kevin Tipple) wrote:

>>I'm trying to go from this query result (mutiple presenters possible for each
>>conference session, with session and presenter tables in a 1-to-many
>>relationship by Session#):

>>... to a table or query like this (preserving the original tables):

>>_____________________________
>> Session# Presenter
>>_____________________________
>> 1 | Fred, Barney, Wilma
>> 2 | Betty
>> 3 | Bam Bam, Dino

If you can go for:

_____________________________
Session# Presenter
_____________________________
1 | Fred

| Barney

| Dino

you can do it with a report. Set up the report to group on the session
number and set up the group footer with a small gap. Somewhere there is a
property that suppresses duplicating values (i.e. 1 and 3 in the example) but
I can't find it at the moment.

>>Is this possible through the QBE grid, or do I have to go to SQL or write some
>>code for this puppy?

>>Many thanks in advance from a tired, idea-less programmer who's been sleeping
>>very little lately.

>>Kevin

>Ok Kevin,

>To get you started, I believe that it's a CrossTab query. Maybe you

>can read up on it and learn it.

>I must admit that 3 years with Access and previous to that Paradox and
>I don't have a handle on Cross tabs. Sigh.

>I can't dive either, or bowl, but I can shoot darts!

>--Sherry