In another postibg I was querying the speed of DLookup, and was told that the
performance was good.

I have a simpllifies Table - Member, with about 700 records
MemberID AutoNumber
MemSurName
MemAddID Long Indexed duplicates allowed

A number of people live at the same address, most have the same surnames, but
occasionally there are 2 or more surnames at 1 address

This query basically looks at each surname, and if it is not already listed,
shows it in the nect column Ideally I would like to increase the number of
lookups to give Expr_4 ... Expr8, but.... This takes some 10 seconds to run
until I can scroll down to 700th record If I add the next logical line to
give Expr_5, basically the query gives up. Access reports that it not
responding, then running, then not responding, then running etc. Sometimes it
does run OK, but takes some 20 seconds till I can get to the last record.

SELECT Member.MemAddID, DLookUp("MemSurName","Member","MemAddID = " &
[MemAddID]) AS Expr_0, DLookUp("MemSurName","Member","MemAddID = " &
[MemAddID] & " AND MemSurName <> '" & [Expr_0] & "'") AS Expr_1,
DLookUp("MemSurName","Member","MemAddID = " & [MemAddID] & " AND MemSurName
<> '" & [Expr_0] & "'" & " AND MemSurName <> '" & [Expr_1] & "'") AS Expr_2,
DLookUp("MemSurName","Member","MemAddID = " & [MemAddID] & " AND MemSurName
<> '" & [Expr_0] & "'" & " AND MemSurName <> '" & [Expr_1] & "'" & " AND
MemSurName <> '" & [Expr_2] & "'") AS Expr_3,
DLookUp("MemSurName","Member","MemAddID = " & [MemAddID] & " AND MemSurName
<> '" & [Expr_0] & "'" & " AND MemSurName <> '" & [Expr_1] & "'" & " AND
MemSurName <> '" & [Expr_2] & "'" & " AND MemSurName <> '" & [Expr_3] & "'")
AS Expr_4 FROM Member
ORDER BY Member.MemSurName;


The equivalent Elookup function from Allen Brown

SELECT Member.MemAddID, Member.MemSurName,
ELookUp("MemSurName","Member","MemAddID = " & [MemAddID]) AS Expr_0,
ELookUp("MemSurName","Member","MemAddID = " & [MemAddID] & " AND MemSurName
<> '" & [Expr_0] & "'") AS Expr_1, ELookUp("MemSurName","Member","MemAddID =
" & [MemAddID] & " AND MemSurName <> '" & [Expr_0] & "'" & " AND MemSurName
<> '" & [Expr_1] & "'") AS Expr_2, ELookUp("MemSurName","Member","MemAddID =
" & [MemAddID] & " AND MemSurName <> '" & [Expr_0] & "'" & " AND MemSurName
<> '" & [Expr_1] & "'" & " AND MemSurName <> '" & [Expr_2] & "'") AS Expr_3,
ELookUp("MemSurName","Member","MemAddID = " & [MemAddID] & " AND MemSurName
<> '" & [Expr_0] & "'" & " AND MemSurName <> '" & [Expr_1] & "'" & " AND
MemSurName <> '" & [Expr_2] & "'" & " AND MemSurName <> '" & [Expr_3] & "'")
AS Expr_4, ELookUp("MemSurName","Member","MemAddID = " & [MemAddID] & " AND
MemSurName <> '" & [Expr_0] & "'" & " AND MemSurName <> '" & [Expr_1] & "'" &
" AND MemSurName <> '" & [Expr_2] & "'" & " AND MemSurName <> '" & [Expr_3] &
"'" & " AND MemSurName <> '" & [Expr_4] & "'") AS Expr_5,
ELookUp("MemSurName","Member","MemAddID = " & [MemAddID] & " AND MemSurName
<> '" & [Expr_0] & "'" & " AND MemSurName <> '" & [Expr_1] & "'" & " AND
MemSurName <> '" & [Expr_2] & "'" & " AND MemSurName <> '" & [Expr_3] & "'" &
" AND MemSurName <> '" & [Expr_4] & "'" & " AND MemSurName <> '" & [Expr_5] &
"'") AS Expr_6 FROM member
ORDER BY Member.MemSurName;

Does 2 more lookups in much the same time

Now for mail merge to work reliably, I can not use "User defined functions"

Has anyone any ideas using built in functions how to extract the different
surnames living at 1 address (Unique surnames only)

Or

Is there any way of kidding MS Word that a User defined function is a built
in one.

Thanks

Phil

Phil wrote:

Not when used in loop operations or queries.

Without spending a bunch of time looking at that mess it sort of looks like
maybe a crosstab query could be a better choice? At any rate when trying to
pull data from rows and display it in columns that is generally the tool
that will do it.

In all my years using Access I have never had a situation that forced me to
use a domain function inside a query. There is almost certainly a better
way.

Have you tried doing this in two stages? I think Rick is on the right
track here. I had a similar requirement recently and broke the
solution into two parts, first was the SELECT DISTINCT that grabbed
all the 'raw' data I required, then I crosstabbed that to provide the
layout I wanted. Using joins across the tables is much faster than a
DLookup (which I tried first). My query time went from 20mins to six
seconds across 500K rows - a huge improvement.

Give it a try :-)

The Frog

On 24/09/2010 09:11:57, The Frog wrote:
Thanks for coming back.
The CrossTab solution works to get the individual names, currently giving me
9 columns of individual names (Maximum no of people living in 1 house). As I
said, most of these names will occur numerous times (same family living in
the same house)

I then need to reduce this normally to just 1 surname, but in the CrossTab
which gives some 500 rows there are about 30 rows with 2 names and 2 rows
with 3 names. Again using built in functions like IIf() and Instg(), I can
extract names from the first 5 columns, but then I run into "Query too
complex" problems if I try to check all 9 columns.

My CrossTab produces 9 columns of individual surnames and a combined column
of all the surnames separated by a # space

Result Result wanted Blackburn# Blackburn# Blackburn# Blackburn# Blackburn#
Blackburn Brooke# Brooke# Brooke# Edwards# Brooke# Brooke# Brooke & Edwards
Jefferies# Priestley# Jeffries & Priestley Tierney-Garneys# Garneys#
Tierney-Garneys & Garneys (Need to check length as well as Instr() Smith#
Smith Mason# Mason# Sale# Sale# Mason & Sale Douglas# Allott# Douglas#
Douglas# Douglas & Allott King# Hodges# Harvey# Harvey# King, Hodges & Harvey

Again I stress I seem to be limited to inbuilt functions to avoid problems
with mail merge.

Phil

Phil wrote:
A function would be far easier to debug than massive IIFs()

Salad wrote:

Bob Barrows wrote:

Hmmm...could code be written from Word to open an instance of Access,
run some code to output to a temp table, and use the temp table as the
merge source?

On 24/09/2010 16:47:42, Salad wrote:
Am already using a temp table to get over the problem, using inbuilt
functions, but the risk is it's out of date. The solution I am hoping for is
to use queries. The main Crosstab query gets the number of columns from the
maximum number of people living at one address, but the design only changes
if the maximum number of people living at one address changes. This is a rare
occurrance, likely to happen once a year if a large family either join or
leave the club.

The other queries depending on the CrossTab query are built on the fly
because I need to join 1 member table to each of the columns in the Crosstab.

This code creates the first query "bbb"
Member table is given an alias such as Mem_0, .... Mem_8

Function CreateQueryNew(StrQName As String, NoAtAddress As Integer) As
Integer '?CreateQueryNew("bbb",9)

' ' Creates a query linking CrossTab query "XAllNames" to the correct number
of Member Tables ' and names the Mem_0, Mem_1...... Mem_8 etc
Dim MyDb As Database
Dim MyQdf As QueryDef
Dim SQLStg As String
Dim i As Integer

On Error GoTo CreateQueryNew_Err

SQLStg = "SELECT XAllNames.*, "
For i = 0 To NoAtAddress - 1
SQLStg SQLStg = SQLStg & "Mem_" & CStr(i) & ".MemSurName, Mem_" & CStr(i) &
".MemFirstName, " SQLStg = SQLStg & "Mem_" & CStr(i) & ".MemTitle, Mem_" &
CStr(i) & ".MemTitleOption, " SQLStg = SQLStg & "Mem_" & CStr(i) &
".MemPhone, Mem_" & CStr(i) & ".MemEMail, " SQLStg = SQLStg & "Mem_" &
CStr(i) & ".MemMobile, Mem_" & CStr(i) & ".MemHeadOfHouseID, " Next i
SQLStg LStg = Left$(SQLStg, Len(SQLStg) - 2) & " " ' Remove last comma and
add space

SQLStg = SQLStg & "FROM "
For i = 1 To NoAtAddress - 1
SQLStg = SQLStg & "("
Next i

SQLStg = SQLStg & "XAllNames "

For i = 0 To NoAtAddress - 1
SQLStg = SQLStg & "LEFT JOIN Member AS Mem_" & CStr(i)
SQLStg SQLStg = SQLStg & " ON XAllNames.[" & CStr(i + 1) & "] = Mem_" &
CStr(i) & ".MemberID) " Next i

SQLStg LStg = Left(SQLStg, Len(SQLStg) - 2) & ";" ' Remove last bracket & add
;

With CurrentDb
For Each MyQdf In .QueryDefs
If MyQdf.Name = StrQName Then ' Does Query exist
.QueryDefs.Delete (StrQName) ' Yes, delete it
Exit For
End If
Next
Set Set MyQdf = .CreateQueryDef(StrQName, SQLStg) ' CreateQueryNewdef command
line follows .QueryDefs.Refresh
Set MyQdf = Nothing
.Close
End With

DoCmd.SelectObject acQuery, StrQName, True
CreateQueryNew = NoAtAddress

Exit Function

CreateQueryNew_Err:
MsgBox Err.Description

End Function

End result is to be able to do mail merge letter with a choice of formal
names such as Mr. & Mrs. J. Smith
Mr. & Mrs. John Smith
Mr. J. Smith & Mrs. M Smith
Mr. J. Smith & Mrs. M Jones
Mr. J. Smith & family ' If there are a lot of them

and
Dear John & Mary ' that bit is OK


Phil

Phil wrote:

Sub Qtest()
Dim r As Recordset
Dim i As Integer
Set r = CurrentDb.OpenRecordset("YourQueryName", dbOpenSnapshot)
If r.RecordCount > 0 Then
r.MoveLast
r.MoveFirst
Do While Not r.EOF()
'get number of records and fields.
MsgBox "There are " & r.RecordCount & " records and " & _
r.Fields.Count & " fields."
For i = 0 To r.Fields.Count - 1
MsgBox r.Fields(i).Name & " " & r.Fields(i).Value
Next
'could process names here and insert into a table.
r.MoveNext
Loop
End If
r.Close
Set r = Nothing
End Sub

Is the mailmerge being called from Word or Access? If Access, I think
you could run some code to process the records then call something like
Albert Kallal's mailmerge routine to finish the job up.

On 24/09/2010 19:35:02, Salad wrote:
Salad
Thanks for your code, but as I said in my original posting I am already using
a temporary table to get over the problem. Because there are a number of VBA
routines used to generate the various combinations of names, it takes about
20 seconds to run. So using that approach, when do you do it - every time you
change a member's record? Far too slow. So at the moment I settle for doing
it when I close the Db.

That is why I would like to use a query to extract and combine the names as
the design of the query only needs to change if the maximum number of people
living at one address changes, and this will happen vary rarele, only when a
new large family are added or removed.

Currently I run the mail merge from word. The young lass in the office can
write her letter, pick the query that gives various options for people's
names and do the mail merge.

Will re-visit Albert's mailmerge routine

Thanks

Phil