multiple record info queried into one record

[Max Moor]

Hi All,

I have two tables. One holds family data, like the address. The
second holds individual family member's data, like ages. They are related
by an autonumber field in the family table, stored with each "member's"
record.

What I'd like to do is get a query that gives me a record with the
last name (from the family table) in one field, and all the first names
(from the members table)s in one other field form via some expression. I'm
sending out a letter. The records might look like...

Smith John & Susan
Nguyen Lee & Mi
etc

How do I form the field with the two first names in it? I assume I
need to do some query within a query. I'm stumped as to how to get the
first names out of multiple fields, and into my one field.

 

[Allen Browne]

Use VBA to concatenate the first names together into a string.
Details:
http://www.mvps.org/access/modules/mdl0004.htm

 

[Max Moor]

Use VBA to concatenate the first names together into a string.
Details:
http://www.mvps.org/access/modules/mdl0004.htm

Thanks for the pointer, Mark. It works like a charm. Doing 1000+ runs to
build the report does take a while though. Do you know any tricks that
moght speed the code up? It looks pretty clean to me. Maybe it's just the
nature of function calls.

 

[Sidney Linkers]

Hi Max,

I still don't know if it can be done with pure SQL, but i got it working.
The trick is using two queries and a bit of VBA code to build up a recordset
with a recordkey and a populated textfield. Just join to this query through
the recordkey to get the populated textfields. It works faster than looping
through records in VBA.

1. The base query "qryYourBaseQueryForPopulating" GROUPS the Recordkey
GROUPS the Textfield you want to populate. In a 3th column a calculated
textfield as EXPRESSION calls a public function fnPopulateTextfield which
takes to params (Recordkey and Textfield).


RecID
tblYourSecundairyTable
GROUP BY

SomeTextfieldYouWantToPopulate
tblYourSecundairyTable
GROUP BY

PopulatedText: fnPopulateTextfield([RecID];[SomeTextfieldYouWantToPopulate])

EXPRESSION

The second column is needed because without it the query does not recognizes
that "SomeTextfieldYouWantToPopulate" is part of a statistic function! The
side effect is that it will group on every "SomeTextfieldYouWantToPopulate",
but the final query will correct this!

2. The finishing query "qryYourFinishingQueryForPopulating" based on the
above query GROUPS again on the Recordkey and takes the LAST record of the
calculated populated textfield.

RecID
qryYourBaseQueryForPopulating
GROUP BY

PopulatedText
qryYourBaseQueryForPopulating
LAST

3. The function fnPopulateTextfield uses two STATIC vars (long and string).
Every time it sees a different recordkey it reinitializes the STATIC vars
(Long var gets new recordkey and the String var gets empty). Next it adds
the Textfield to the STATIC string var seperated by a comma and then returns
the STATIC string var.

Lets say you have a secundairytable "tblYourSecundairyTable" like the next
one :

RecID SomeTextfieldYouWantToPopulate
1 Cat
1 Dog
2 Fish
3 Lion
3 Birth
3 Snake
4 Dog
4 Crock
4 Hyena
4 Fish

After the base query "qryYourBaseQueryForPopulating" you will get:

RecID PopulatedTextfield
1 Cat
1 Cat, Dog
2 Fish
3 Lion
3 Lion, Birth
3 Lion, Birth, Snake
4 Dog
4 Dog, Crock
4 Dog, Crock, Hyena
4 Dog, Crock, Hyena, Fish

What the f*#%! Don't worry after the final query
"qryYourFinishingQueryForPopulating" you will get:

1 Cat, Dog
2 Fish
3 Lion, Birth, Snake
4 Dog, Crock, Hyena, Fish

Voila! Now it's just a matter of joining to this query from your
primarytable.

My own code was too complex to show the basic principle, sorry!

Good luck and if you believe hard enough it will happen!

Sidney.

 

[Max Moor]

Hi Max,

Thanks, Sidney. I'll give this a try, and comare it to the code version.
Even if it isn't any faster, it will likely be a better solution for me. I
want to be able to use the resulting query for a mail merge, and Word
doesn't seem to like the function call being in the query. Thanks again
for all the info. I appreciate all the time you put into answering me.

- Max

 

[Sidney Linkers]

Hi Max,

Somehow ODBC doesn't support function calls in MSAccess databases. I also
have used Mail Merge from MSAccess a few times and i use textfiles (comma
delimited) and works just fine. U need to use a filter for double quote
characters and replace them with single quote, if you allow users to put
double quotes in text or memo fields else MSWord gets nasty.

Just write a query to textfile transform routine and tell MSWord to get the
mergedata from this textfile. You can use something like below (not tested
and needs error handling).

-----------------------------------------------
Function ExportQueryToTxtFile(strFile As String, ByVal strYourQuery As
String) As Boolean

Set db = CurrentDb()
Set rst = db.OpenRecordset(strYourQuery, DB_OPEN_DYNASET, DB_FORWARDONLY)


intFileNo = FreeFile()
Open strFile For Output As #intFileNo

For i= 0 To rst.Fields.Count - 1
If strHeader = "" Then
strHeader = strHeader & Chr(34) & Nz(rst.Fields(i).Name, "") &
Chr(34)
Else
strHeader = strHeader & ", " & Chr(34) & Nz(rst.Fields(i).Name, "")
& Chr(34)
End If
Next
Print #intFileNo, strHeader

Do While Not rst.EOF
strRecord = ""
For i= 0 To rst.Fields.Count - 1
If strRecord = "" Then
strRecord = strRecord & Chr(34) & Nz(rst.Fields(i).Value, "") &
Chr(34)
Else
strRecord = strRecord & ", " & Chr(34) & Nz(rst.Fields(i).Value,
"") & Chr(34)
End If
Next
rst.MoveNext
Print #intFileNo, strRecord
Loop

Close #intFileNo
If Not (rst.BOF And rst.EOF) Then
ExportQueryToTxtFile = True
End If

rst.Close
db.Close
--------------------------------------

By the way even my solution wouldn't work through ODBC connection because it
calls a userdefined VB function.

Good luck!

Sid.