I am creating a table using the following

SQLStg LStg = "SELECT DISTINCT Member.MemSurName,
CombineFirstNames([Member]![MemAddID]) AS Name, " SQLStg = SQLStg &
"LstAddress.*,
ForeNames([MemSurName],[Member]![MemAddID],[MemHeadOfHouseID],30) AS [Names],
" SQLStg = SQLStg & "CombineEMailAdd([Member!MemAddID]) AS EMail,
Member.MemPhone, " SQLStg = SQLStg & "Member.MemMobile , "
SQLStg = SQLStg & "XNames([Member]![MemHeadOfHouseID]) AS FormalNames, "
SQLStg = SQLStg & "Member.MemHeadOfHouseID, "
SQLStg = SQLStg & "Member.Indicator, Member.MemHead, "
SQLStg LStg = SQLStg & "Emax('MemJoinDate','Member','MemAddID = ' &
Member.MemAddID) AS LastToJoin, " SQLStg = SQLStg &
"Ecount('MemberID','Member','MemAddID = ' & LstAddress.MemAddID) AS
NoAtAddress " SQLStg = SQLStg & "INTO " & NewTable & " IN '" & OtherDBName &
"'" SQLStg = SQLStg & "FROM [Year] INNER JOIN (LstAddress INNER JOIN Member "
SQLStg LStg = SQLStg & "ON LstAddress.MemAddID = Member.MemAddID) ON
Year.YearID = Member.YearID " SQLStg = SQLStg & "WHERE (((Member.MemHead) =
True) " SQLStg = SQLStg & "And ((Member.MemRetiredID) =
IIf([Year]![MemActivity] = 0, " SQLStg = SQLStg & "[Member].[MemRetiredID],
[Year]![MemActivity]))) " SQLStg = SQLStg & "ORDER BY Member.MemSurName,
CombineFirstNames([Member]![MemAddID]);"

Set MyDb = CurrentDb
MyDb.Execute SQLStg, dbFailOnError

Fine, works OK, but it slow to run as there are a number of calculated
fields.

Is there any way of using a Syscmd(acSysCmdInitMeter ....),
SysCmd(acSysCmdUpdateMeter, ....) and SysCmd acSysCmdRemoveMeter type
sequence to show progress

Thanks

Phil

It's not really possible, since Access doesn't return any feedback as to
where it is in the query, meaning that you would be unable to update the
meter.

"Phil" wrote in message news:iqccn0$42d$1 (AT) speranza (DOT) aioe.org...

I am creating a table using the following

SQLStg LStg = "SELECT DISTINCT Member.MemSurName,
CombineFirstNames([Member]![MemAddID]) AS Name, " SQLStg = SQLStg &
"LstAddress.*,
ForeNames([MemSurName],[Member]![MemAddID],[MemHeadOfHouseID],30) AS
[Names],
" SQLStg = SQLStg & "CombineEMailAdd([Member!MemAddID]) AS EMail,
Member.MemPhone, " SQLStg = SQLStg & "Member.MemMobile , "
SQLStg = SQLStg & "XNames([Member]![MemHeadOfHouseID]) AS FormalNames, "
SQLStg = SQLStg & "Member.MemHeadOfHouseID, "
SQLStg = SQLStg & "Member.Indicator, Member.MemHead, "
SQLStg LStg = SQLStg & "Emax('MemJoinDate','Member','MemAddID = ' &
Member.MemAddID) AS LastToJoin, " SQLStg = SQLStg &
"Ecount('MemberID','Member','MemAddID = ' & LstAddress.MemAddID) AS
NoAtAddress " SQLStg = SQLStg & "INTO " & NewTable & " IN '" & OtherDBName &
"'" SQLStg = SQLStg & "FROM [Year] INNER JOIN (LstAddress INNER JOIN Member
"
SQLStg LStg = SQLStg & "ON LstAddress.MemAddID = Member.MemAddID) ON
Year.YearID = Member.YearID " SQLStg = SQLStg & "WHERE (((Member.MemHead) =
True) " SQLStg = SQLStg & "And ((Member.MemRetiredID) =
IIf([Year]![MemActivity] = 0, " SQLStg = SQLStg & "[Member].[MemRetiredID],
[Year]![MemActivity]))) " SQLStg = SQLStg & "ORDER BY Member.MemSurName,
CombineFirstNames([Member]![MemAddID]);"

Set MyDb = CurrentDb
MyDb.Execute SQLStg, dbFailOnError

Fine, works OK, but it slow to run as there are a number of calculated
fields.

Is there any way of using a Syscmd(acSysCmdInitMeter ....),
SysCmd(acSysCmdUpdateMeter, ....) and SysCmd acSysCmdRemoveMeter type
sequence to show progress

Thanks

Phil

On 11/05/2011 02:10:27, "Douglas J Steele" wrote:
Thanks Douglas

I rather thought that would be the answer., but hoped someone knew something
that I didn't know. Pity

Phil

Hi Phil,

You could split your query in twe parts.
First part to retrieve your distinct members.
Second part to do the heavy job, including calculated fields, sorting,
table creation etc. based on the set om members from the first query.
In this second part you can use the Progress Meter.

Imb.

On 11/05/2011 09:26:03, imb wrote:
Hi Imb

Don't follow your suggestion.
First part would gibe the no of records for the Syscmd(acSysCmdInitMeter ,
RecordCount) The second part is a make table run by MyDb.Execute SQLStg,
dbFailOnError. The making of the table is fast, the slow bit is running the
query where I am calling a number of functions to combine the individual
first names of a family into a single field. So the function
CombineFirstNames([Member]![MemAddID]) AS Name will turn John Smith, Mary
Smith and Bill Jones, all living at the same address inti "John, Mary & Bill"
Possibly I could update the meter each time I call the function?
Phil

On May 11, 12:03*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Hi Phil,

As you said the CombineFirstNames function is a time-consuming one.
When you have three members at the same adress, you must run it six
times (3x for selecting, 3x for sorting), to use it only once.
My suggestion is: select all distinct adresses with the necessary
criteria as the first part. That gives you a table or recordset with
all nece3ssary adresses, so also the total number of adresses.
Use this table or recordset for the extraction of all other
information including one time CombineFirstNames to make the new
table. The count for the Progresse Bar is the number of adresses
processed.

Sorting on CombineFirstNames in the query is not necessary, as you can
always sort your result table on the field [Names].

Am I clear enough now?

Imb.

One thing I would do would be to drop the ORDER BY clause. Since you are
using this to build a table (or populate an existing table), the order by
really serves no purpose.

The WHERE clause might be restructured to
WHERE Member.MemHead =True And
((Member.MemRetiredID = [Member].[MemRetiredID] and [Year].MemActivity = 0)
Or ([Year].Activity <> 0 and [Member.MemRetiredID] = [Year].Activity))

Of course, if you remove all the calculations (for testing purposes) and find
that your original query runs very fast, then all of the above is wasted and
the slowness of the query is almost all attributable to the use of all the
functions.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 5/10/2011 6:03 PM, Phil wrote:

On 11/05/2011 14:15:30, John Spencer wrote:
Thanks for coming back. The speed of the query is largely governed by the 4
functions which get passed the MemAddID, in which case the function runs a
bit of SQL to find who lives at that address and then, depending on the
output required, concatenates the first names of the occupants (with commas
and 'and') for informal letters / emails or extracts initials and titles for
formal letters. Additional complications when there are 2 surnames living at
one address. Fortunately, I only need to run this query occassionally, so it'
mo major hassle. It's just nice to show that the query is doing something,
hence the original question. Imb's solution may work, will have a look at
that later.

Phil