Solved

More efficient VBA function

Posted on 2006-07-13
52
544 Views
Last Modified: 2008-01-16
Hello,

I have a query that does what I want it to when it calls this function, but it takes at least 10 minutes to compile right now, and if you scroll then it starts all over again, so the only solution is to copy/paste into a table or excel after the first execution.  If anyone can provide some solutions for more efficient code, that would be wonderful.  You can find a link to my previous problem which the code was provided here: http://www.experts-exchange.com/Databases/MS_Access/Q_21912158.html

Here is the code I have now.
-----------
Public Function Concat(CoyID As Double, fld As String) As String

Dim mydb As DAO.Database, myrs As DAO.Recordset, strSQL As String
strSQL = "SELECT " & fld & " FROM myQry WHERE CompanyID = " & CoyID & ";"
Set mydb = CurrentDb
Set myrs = mydb.OpenRecordset(strSQL)
myrs.MoveLast
myrs.MoveFirst
Do While Not myrs.EOF
  If myrs.Fields(0).Value <> "" Then
    Concat = Concat & Trim(myrs.Fields(0).Value) & ", "
  End If
  myrs.MoveNext
Loop
Set myrs = Nothing
Set mydb = Nothing
If Len(Concat) <> 0 Then
  Concat = Left(Concat, Len(Concat) - 2)
End If

End Function

This is the query SQL:
--------
SELECT myQry.CompanyID, Concat(CompanyID,"ProjMgr") AS ProjMgr, Concat(CompanyID,"ProgMgr") AS ProgMgr, Concat(CompanyID,"Coach") AS Coach, Concat(CompanyID,"ProjAdm") AS ProjAdm, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate
FROM myQry
GROUP BY myQry.CompanyID, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate
ORDER BY myQry.CompanyID, myQry.WSDate, myQry.vchAddress1, myQry.vchCity;

I know that conditionals and loops require a lot of processing time, but I'm not fresh enough on my programming, and don't have too much time to devote to this to improve it myself.  Any help is appreciated, and thank you in advance.

WC
0
Comment
Question by:WarCrimes
  • 15
  • 14
  • 12
  • +3
52 Comments
 
LVL 44

Accepted Solution

by:
GRayL earned 32 total points
ID: 17102972
WC:  VB Editor Help, Answer Wizard , type - set index returns a lot

Index Property
         

Sets or returns a value that indicates the name of the current Index object in a table-type Recordset object (Microsoft Jet workspaces only).

Settings and Return Values

The setting or return value is a String data type that evaluates to the name of an Index object in the Indexes collection of the Tabledef or table-type Recordset object's TableDef object.

Remarks

Records in base tables aren't stored in any particular order. Setting the Index property changes the order of records returned from the database; it doesn't affect the order in which the records are stored.

The specified Index object must already be defined. If you set the Index property to an Index object that doesn't exist or if the Index property isn't set when you use the Seek method, a trappable error occurs.

Examine the Indexes collection of a TableDef object to determine what Index objects are available to table-type Recordset objects created from that TableDef object.

You can create a new index for the table by creating a new Index object, setting its properties, appending it to the Indexes collection of the underlying TableDef object, and then reopening the Recordset object.

Records returned from a table-type Recordset object can be ordered only by the indexes defined for the underlying TableDef object. To sort records in some other order, you can open a dynaset-, snapshot-, or forward-only–type Recordset object by using an SQL statement with an ORDER BY clause.

Notes

You don't have to create indexes for tables. With large, unindexed tables, accessing a specific record or creating a Recordset object can take a long time. On the other hand, creating too many indexes slows down update, append, and delete operations because all indexes are automatically updated.


Records read from tables without indexes are returned in no particular sequence.


The Attributes property of each Field object in the Index object determines the order of records and consequently determines the access techniques to use for that index.


A unique index helps optimize finding records.


Indexes don't affect the physical order of a base tableindexes affect only how the records are accessed by the table-type Recordset object when a particular index is chosen or when Recordset is opened.

As you can see, if you create an index for both Coach and ProjAdm fields, give them names like idxCoach and idxProjAdm, you can use the above code but provide the indexes and change the Move to Seek.  Do you want help with the code change?

0
 
LVL 44

Expert Comment

by:GRayL
ID: 17103269
You mentioned earlier that each project has only one Project Manager and one Program Manager, yet I see you have used the Concat function for each of these.  That is not necessary.  By removing those two concats, you should see an improvement.  
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17104183
This confuses me.  Why are you doing this in a function?  It looks like Concat just queries a recordset using VB and returns a single result to your plain old query.  Shouldn't this be done with just a more complex query, or at most one query that references another?

I'm also not sure why "if you scroll then it starts all over again", unless you mean it runs once for every record in a form and scrolling through records in form view runs it repeatedly.  That would make sense, but we should be able to make a sensible query without needing to programatically loop through recordsets.
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17104225
Yes, the more I look at this the more I think you're just trying to do a self-join query:  


SELECT e1.ename + ' works for ' + e2.ename
"Employees and their Managers"
    FROM emp e1, emp e2   WHERE e1.mgr = e2.empno;

Let me see if I can put together a self-join version of what you're doing...
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17104233
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17104236
GRayL,

I guess the reason I'm having issues with the indexing is because there isn't actually a table that has a list of ProjMgr, ProgMgr, Coach, and ProjAdm.  They are represented in our database by numbers (type of contact where the value tells you what they are), and I'm performing a query to pull certain records.  Each time a record is accessed, only one of the four is listed with it, whichever that person happens to be.  So I'm aggregating the data to match up all types for a specific company.  I'm not sure where in that process I can put an index.  

Should I build this as an append query, then use the table I append it to in order to do the aggregate?  That would allow me to index the fields.  I've never indexed inside a query before, so I'm not sure how to do that, if you can.

I know there shouldn't be more than one ProjMgr or ProgMgr, but just in case, and it does not affect the speed noticeably whether those Concats are in there or not.

Any help with code would be appreciated.  I've used Move's before with ASP pages in order to travers recordsets, but never used Seek.

WC
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17104381
This should give you the data you want, although (since I don't know more about myQry and what you want it to do) I can't promise it'll group it the way you like.  An even faster way of doing this would be to create a query or queries that simply list CompanyID and the fields you want linked to them (ProjMgr, ProgMgr, Coach, ProjAdm) from your source table and use that as myQry2 -- either linking as a separate query, or putting the (SELECT DISTINCT CompanyID, ProjMgr, ProgMgr, Coach, ProjAdm FROM tblSourceTable) query right into your "FROM" statement.

Again, not knowing how your source data is structured, this may need some refinement to work.  But it should work.  What your Concat function does is effectively the same thing, except that doing it right in the query runs the comparison once, and your original runs the comparison four times for every single record in myQry.  Using VB, even.  Some permutation on the below HAS to be faster.


SELECT myQry1.CompanyID, myQry1.ProjMgr, myQry1.ProgMgr, myQry1.Coach, myQry1.ProjAdm, myQry1.vchCompanyName, myQry1.vchAddress1, myQry1.vchCity, myQry1.chRegionCode, myQry1.chCountryCode, myQry1.MinOfFoM, myQry1.MaxOfFoM, myQry1.WSDate
FROM myQry myQry1, myQry myQry2
WHERE myQry1.CompanyID = myQry2.CompanyID
GROUP BY myQry1.CompanyID, myQry1.vchCompanyName, myQry1.vchAddress1, myQry1.vchCity, myQry1.chRegionCode, myQry1.chCountryCode, myQry1.MinOfFoM, myQry1.MaxOfFoM, myQry1.WSDate
ORDER BY myQry1.CompanyID, myQry1.WSDate, myQry1.vchAddress1, myQry1.vchCity;
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17104414
The self-joins are an interesting look at it.  I'll consider how this might work with the original query.  As far as the scrolling issue.  I mean when the window has to refresh, the whole query runs again, whether you scroll, switch to another window, or whatever.  It redraws all the data.
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17104489
Okay, your last comment confirms that this is more complicated than I thought.  There are still SQL-only ways of doing this that will be much, much faster.

For each field that you use concat on, create a single query whose source is myQry, and give each two columns:  CompanyID and the desired field.  Set a criteria that the desired field "is not null".  Save it with whatever name you want.

When those are complete, create another query.  Use as sources myQry and all the queries you just created.  Link myQry to each of them (easiest done in design view, not SQL view) on CompanyID.  Double-click the linklines and select the radio button for "Include ALL records from myQry and only those records from otherqryname where the joined fields are equal".  Complete your query as you did above (you can now shift to sql view and just copy and paste your orignal code), but leave everything you'll see in the FROM clause alone.  The only change you make to your SELECT clause is replacing the 'Concat(....) AS xxx' with otherqry1.ProjMan, otherqry2.ProgMan, etc.

These four new queries will give you the list of Project Managers, Program Managers, etc that you don't have elsewhere, and using the LEFT JOINs will make sure you get only the records you want to see.
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17104518
Self-joins won't work for your structure-- sorry for throwing out a red herring.

As for the scrolling: it has to repaint the screen, but it really doesn't have to redraw the data (meaning requery the sources) unless you're doing something that should change the results.  If you're doing this in a form, we can look at what event you're using to fire the requerying and possibly make it less trigger-happy.

The only time the symptoms you describe should happen in a datasheet view are when the system is so bogged down (either due to complex queries/processes and/or huuuuuuge recordsets) that it only calculates and displays small amounts at a time, and is frantically swapping out memory and dumping previously computed records as it goes.  You can see a less dramatic version of this when you open a large table and see the vertical scrollbar shrinking as it loads more records.
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17104522
Rak,

As to the first idea there, I think you meant to do that with the original query not MyQry.  It might work doing that with the 4 sets of data, which is the idea you have there in the second comment.   I will have to try this tomorrow if I get a chance.  This isn't a high priority issue on my stuff to do, but it's interesting to me, so I'll get to it if I get some free time tomorrow.

Thanks for the ideas.  It's nice having other people stimulate the process.

WC
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17104606
No, I meant what I said about the first suggestion-- I was just dead wrong.  Don't give me too much credit.  :)

In general, functions should be used to analyze and transform data.  Queries should be used to compare.  There are exceptions, and the Recordset object is a darn handy little thing that I put through its paces on a regular basis-- but if you're comparing two big lists of data and looking for a match, it's almost always query-time.
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 31 total points
ID: 17105485
just at first glance, these two lines:

myrs.MoveLast
myrs.MoveFirst

are totally UN_NECESSARY, and accomplish nothing in the context of your code.  They can be removed, and that will help, since it then will NOT take up the time to scroll the recordset to the end and back again.

The lines are un-neeeded since you are stepping through the recordset, one record at a time (myrs.movenext) until you reach myrs.eof.  The lines would only be needed if you were using myrs.RecordCount in the loop.

AW
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17108234
I am with the suggestion for doing this is SQL but if you really need to do this in code ...

you can refine the WHERE statement of your inline SQL to not include bl;ank filed values and you can use the hidden   .Collect method of retrieving a field's value (faster than using .Value) and you can also use the $ version of your string function which are also fater ... please make sure that the CoyID field is indexed. Right, open the recordset as snapshot should save some cursor overhead.

Public Function Concat(CoyID As Double, fld As String) As String

    Dim myrs As DAO.Recordset
    Dim strSQL As String
   
    strSQL = "SELECT " & fld & " FROM myQry WHERE CompanyID = " & CoyID & " AND Len(" & fld & ")>0;"
   
    Set myrs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
   
    Do While Not myrs.EOF
        Concat = Concat & Trim$(myrs.Collect(0)) & ", "
        myrs.MoveNext
    Loop
    Set myrs = Nothing
    If Len(Concat) > 0 Then
        Concat = Left$(Concat, Len(Concat) - 2)
    End If

End Function

Steve
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17108240
forgot to mention you can use DBEngine(0)(0) instead of CurrentDB for faster access and you also do not need to create a seperate variable for it.

Steve
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17108727
Yes, GRayL and stevbe are correct-- indexing Company ID in its source tables should help no matter what route you take (assuming that coyID in myQry is data straight from the tables, and not the result of some expression like concatenation).
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17108981
hmmm ... thinking about this a bit more ... can we somewhow use the builtin ADO recordset's .GetString method?

yup ...

Public Function Concat(CoyID As Double, fld As String) As String

    Dim rst As ADODB.Recordset
       
    Set myrs = New ADODB.Recordset
    rst.Open ""SELECT " & fld & " FROM myQry WHERE CompanyID = " & CoyID & " AND Len(" & fld & ")>0;", CurrentProject.Connection

    Concat = rst.GetString
    Concat= Replace(sRet, Chr(13), ", ")
    If Len(Concat) > 0 Then
        Concat = Left$(Concat, Len(Concat) - 1)
    End If

    rst.Close
    Set rst = Nothing
End Function

Steve
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17110156
steve,

The first function code you provided worked, but no noticeable improvement in speed.  The second one has a bug in the rst.Open statement.  I tried fixing it to no avail.  

As far as indexing CompanyID in its source table, that is not an option.  I'm fairly new here and don't have access to it.  

The redrawing thing.  It's not just that it is redrawing it, it's that is seems to actually be running the recursive function all over again to fill in the records.  I'm wondering if running the code separately would be better.  Maybe use four arrays to store the strings, and then call the individual array position to fill the query?  Will that work?  Can you create a variable with that kind of scope in VBA?  I'm running out of ideas of my own.

It's wierd this query is so harsh.  We run tons of other queries on this database and they all execute in seconds, if not immediately.

GRayL, here's the new SQL code.  It is much more efficient and easy to read.  Thanks for the tips.
--------
SELECT myQry.OnyxCompanyID, Concat(OnyxCompanyID,"ProjMgr") AS ProjMgr, Concat(OnyxCompanyID,"ProgMgr") AS ProgMgr, Concat(OnyxCompanyID,"Coach") AS Coach, Concat(OnyxCompanyID,"ProjAdm") AS ProjAdm, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate
FROM (((myQry LEFT JOIN ProjMgr ON myQry.ProjMgr = ProjMgr.ProjMgr) LEFT JOIN ProjAdm ON myQry.ProjAdm = ProjAdm.ProjMgr) LEFT JOIN Coach ON myQry.Coach = Coach.Coach) LEFT JOIN ProgMgr ON myQry.ProgMgr = ProgMgr.ProjMgr
GROUP BY myQry.OnyxCompanyID, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate
ORDER BY myQry.OnyxCompanyID, myQry.vchAddress1, myQry.vchCity, myQry.WSDate;
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17110203
Sorry, That was Rak who suggested that.  Anyways, still slow when I use the Concat functions to group the columns.  This query even takes forever when trying to save it.  It's definitely an anomoly in our database, that's for sure.
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17110214
nevermind about the saving thing.  I had the vb editor in break state, causing that problem.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17110340
there was an extra " at the beginning of the SQL string and I had niot fixed all my var names ...
I also reformatted the code so it would post here without any confusing line wraps ...

Public Function Concat(CoyID As Double, fld As String) As String

    Dim rst As ADODB.Recordset
       
    Set rst = New ADODB.Recordset
    rst.Open "SELECT " & fld & " FROM myQry " & _
             "WHERE CompanyID = " & CoyID & " AND Len(" & fld & ")>0;", _
             CurrentProject.Connection, _
             adOpenForwardOnly, _
             adLockReadOnly

    Concat = rst.GetString
    Concat = Replace(Concat, Chr(13), ", ")
    If Len(Concat) > 0 Then
        Concat = Left$(Concat, Len(Concat) - 1)
    End If

    rst.Close
    Set rst = Nothing
End Function
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17110460
when you pass the fld variable in I would suggest that you include brackets around it in case there are any spaces ...

"[My Manager]"

hold on a second ... how many times do you call this for each record ... 1 time for each position? lets see if we can do better ...

Steve
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17110604
The slowness is because you aren't just running one query-- every time a field contains a Concat, another query is run to create a recordset, and that recordset is then compared to the current value (which is effectively yet another query).  You run Concat four times for every record, which is (more or less) an extra 8 queries.  They're simple queries, sure, but each one is a new process for Access to create, chew over, and return.  If your SQL returns a mere 20 rows (and I suspect it returns a lot more), that's 160 queries running on top of the query you're deliberately running.  To make things worse, the source data for all these queries is itself a query.

Arrays would work, in the same way that VB works-- but what you're doing by loading each of those arrays is effectively creating a number of programmatic recordsets (recordsets are just multi-dimensional arrays that are exposed to a number of custom properties and methods).  I stand by my recommendation to do away with Concat entirely and just use joined queries.  

To answer the question that you asked:  yes, you can create variables with that level of scope in VB, but generally it's advised to dump big piles of data like that into a table or recordset instead.  This assumes that you're running inside a form; just running your SQL as a query would require some workaround to mimic truly global scope.
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17110610
I just had an idea that might shed some light on this.  When building the queries for ProjMgr, ProgMgr, and ProjAdm, I can use the same FROM statement, but the JOINS for the Coach is for some reason different (JOINS don't find Coaches using same as ProjMgr etc.).  Don't ask me why.  Could this be causing the slowness.  Just a thought.

There are a little over 11000 records in the contactInternal table, and less than 100 in the T3 table it's connected to.  Here are the two kinds of JOINS I had to use.

ProjMgr, ProgMgr, ProjAdm
--------
SELECT dbo_vContactInternal.chUserId AS ProjMgr, [T3: List of Locations with LDI Leaders].CompanyID, [T3: List of Locations with LDI Leaders].WSDate
FROM (([T3: List of Locations with LDI Leaders] LEFT JOIN dbo_vContactInternal ON [T3: List of Locations with LDI Leaders].CompanyID = dbo_vContactInternal.iOwnerId) LEFT JOIN dbo_Projects ON [T3: List of Locations with LDI Leaders].CompanyID = dbo_Projects.iCompanyID) LEFT JOIN dbo_Hours_Recordables ON dbo_Projects.ProjectNo = dbo_Hours_Recordables.ProjectNo
GROUP BY dbo_vContactInternal.chUserId, [T3: List of Locations with LDI Leaders].CompanyID, [T3: List of Locations with LDI Leaders].WSDate, dbo_vContactInternal.iContactTypeId
HAVING (((dbo_vContactInternal.iContactTypeId)=12));


------
Coach
------

SELECT dbo_vContactInternal.chUserId AS Coach, [T3: List of Locations with LDI Leaders].CompanyID, [T3: List of Locations with LDI Leaders].WSDate
FROM (dbo_Hours_Recordables RIGHT JOIN dbo_Projects ON dbo_Hours_Recordables.ProjectNo = dbo_Projects.ProjectNo) RIGHT JOIN ((dbo_vContactInternal INNER JOIN dbo_vIndividual ON dbo_vContactInternal.iOwnerId = dbo_vIndividual.iIndividualId) INNER JOIN [T3: List of Locations with LDI Leaders] ON dbo_vIndividual.iCompanyId = [T3: List of Locations with LDI Leaders].CompanyID) ON dbo_Projects.iCompanyID = [T3: List of Locations with LDI Leaders].CompanyID
GROUP BY dbo_vContactInternal.chUserId, [T3: List of Locations with LDI Leaders].CompanyID, [T3: List of Locations with LDI Leaders].WSDate, dbo_vContactInternal.iContactTypeId
HAVING (((dbo_vContactInternal.iContactTypeId)=53));


Maybe that will help shed some light on this.  I hope at least.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 18

Author Comment

by:WarCrimes
ID: 17110620
Any ideas on how to do the grouping then in SQL and not use VBA?
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17110626
In all fairness, everyone else is answering the question you asked, which is "how do I optimize this VB code".  I'm being a jerk and ignoring that question, saying instead that the best optimization in this case is to throw it out entirely and use straight SQL.  I think I'm right, but it's still pretty presumptuous.  Sorry.  :)
0
 
LVL 4

Assisted Solution

by:Rakafkaven
Rakafkaven earned 31 total points
ID: 17110808
More detail from one of my overly-wordy posts above:

Create four queries:
------------------------------------------------------
  SELECT DISTINCT CompanyID, ProjMgr
  FROM myQry
  WHERE
    ProjMgr Is Not Null AND
    CompanyID Is Not Null;
------------------------------------------------------
Save as "qryProjMgr".  Repeat for ProgMgr, Coach, and ProjAdm, so you now have four queries.  The fifth query that replaces the one with Concat is:

SELECT myQry.CompanyID, qryProjMgr.ProjMgr, qryProgMgr.ProgMgr, qryCoach.Coach, qryProjAdm.ProjAdm, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate
FROM
  (
    (
      (myQry LEFT JOIN qryProjMgr ON myQry.CompanyID = qryProjMgr.CompanyID)
      LEFT JOIN qryProgMgr ON myQry.CompanyID = qryProgMgr.CompanyID
    )
    LEFT JOIN qryCoach ON myQry.CompanyID = qryCoach.CompanyID
  )
  LEFT JOIN ProjAdm ON myQry.CompanyID = ProjAdm.CompanyID
GROUP BY myQry.CompanyID, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate
ORDER BY myQry.CompanyID, myQry.WSDate, myQry.vchAddress1, myQry.vchCity;
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17110821
Although now that I look at that, I kind of wonder if those GROUP BYs are necessary... but I suppose they're not hurting anything.
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17110837
Ack:  that last line above the GROUP BY should be:

LEFT JOIN qryProjAdm ON myQry.CompanyID = qryProjAdm.CompanyID
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 31 total points
ID: 17110874
I would agree (still) that a SQL solution will probably perform better but that ain't my strong suit but aftyer I finishe this last iteration of VBA I will take a quick look at SQL ...

To stop the function from opening a recordset 4 times for every record I am caching all four fields and only chnaging when the CoyID changes and return only the field you asked for ... not sure it will help much ...
and yes I switched back to DAO :-)

Public Function Concat(CoyID As Double, fld As String) As String

    Dim rst As DAO.Recordset
    Static dblID As Double
    Static strProjMgr As String
    Static strProgMgr As String
    Static strCoach As String
    Static strProjAdm As String
    If CoyID <> dblID Then
        dblID = CoyID
        Set rst = DBEngine(0)(0).OpenRecordset( _
                  "SELECT ProjMgr, ProgMgr, Coach, ProjAdm FROM myQry " & _
                  "WHERE CompanyID = " & CoyID)
   
        Do While Not rst.EOF
            If Len(rst.Fields("ProjMgr").Value) > 0 Then
                strProjMgr = strProjMgr & rst.Fields("ProjMgr").Value & ", "
            End If
            If Len(rst.Fields("ProjAdm").Value) > 0 Then
                strProjAdm = strProjAdm & rst.Fields("ProjAdm").Value & ", "
            End If
            If Len(rst.Fields("Coach").Value) > 0 Then
                strCoach = strCoach & rst.Fields("Coach").Value & ", "
            End If
            If Len(rst.Fields("ProgMgr").Value) > 0 Then
                strProgMgr = strProgMgr & rst.Fields("ProgMgr").Value & ", "
            End If
            rst.MoveNext
        Loop
        'cleanup
        rst.Close
        Set rst = Nothing

        'this aint quite right yet.
        If Len(strProjMgr) > 0 Then strProjMgr = Left$(strProjMgr, Len(strProjMgr) - 1)
        If Len(strProjAdm) > 0 Then strProjAdm = Left$(strProjAdm, Len(strProjAdm) - 1)
        If Len(strCoach) > 0 Then strCoach = Left$(strCoach, Len(strCoach) - 1)
        If Len(strProgMgr) > 0 Then strProgMgr = Left$(strProgMgr, Len(strProgMgr) - 1)

    End If
   
    Select Case fld
        Case "[ProjMgr]"
            Concat = strProjMgr
        Case "[ProjAdm]"
            Concat = strProjAdm
        Case "[Coach]"
            Concat = strCoach
        Case "[ProgMgr]"
            Concat = strProgMgr
    End Select
   
End Function

0
 
LVL 39

Expert Comment

by:stevbe
ID: 17110899
forgot to finish this piece ...

        'trim last    ", "
        If Len(strProjMgr) > 0 Then strProjMgr = Left$(strProjMgr, Len(strProjMgr) - 2)
        If Len(strProjAdm) > 0 Then strProjAdm = Left$(strProjAdm, Len(strProjAdm) - 2)
        If Len(strCoach) > 0 Then strCoach = Left$(strCoach, Len(strCoach) - 2)
        If Len(strProgMgr) > 0 Then strProgMgr = Left$(strProgMgr, Len(strProgMgr) - 2)
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17110929
<GROUP BY myQry.CompanyID, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate>

What pieces of this actually uniquely identifies each record ... all other fields could be changed to  FIrst instead of GroupBy which should help the query a bit.

Steve
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17110944
hmmm ..

<ORDER BY myQry.CompanyID, myQry.WSDate, myQry.vchAddress1, myQry.vchCity;>

does the same company id have different addresses and cities? maybe you can drop the extra sorting
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17111071
CompanyID and WSDate are the unique identifiers for the records.  CompanyID can give you the rest of the company data, but the company may have more than 1 WSDate for which we need to collect data.
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17111079
I'm busy on some more critical things today.  This is just kind of a side project here at work, so I may not get to it until Monday, but I am appreciating all the help.  The self-join reccommendation is actually helping with something else.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17112420
Hello all:  Back from having All my EE posts dumped into the Junk folder for the last four days.  Damn Sympatico!  An interesting thread.  For now I'll just read if over several time to make sure I understand everyone's input.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17112431
BTW, it might help to know how many records are in each of the four table in the main query.
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17123449
Rak,

That SQL is perfect in putting the queries together, but it still doesn't concatenate multiple entries for the same field for the same CompanyID and WSDate.  It does make one line for each combination however, which is better than before.

0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17125089
steve,

That VB function is about 2x faster, but all the fields for ProjMgr,ProgMgr,Coach,ProjAdm are blank, which may be a naming issue, and the "redrawing" thing is still an issue.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17129159
I would step back and look at this from a slighlty different perspective ...

I don't think with the structure you have (or any structure for that matter) that this will be anything like speedy ... ever.

I would display the multi-people per role in subform / subreports.
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17147237
I think stevbe might have this right.  I'd misread the problem and not realized that we wanted all results combined on one record; to my knowledge, there is no way to do this by SQL alone.  The most efficient way to do this (that I can think of) is to use the SQL that I provided (with the addition of stevbe's excellent suggestions on eliminating the unnecessary GROUP BY and ORDER BY clauses), which I'll call 'EEquery', and write VB code to produce a second recordset.  Here's a pseudocode mockup:

recordsetSource = EEquery
recordsetTarget = NewRecordset
Do while recordsetSource.EOF = false
  recordsetTarget.NewRecord
  stringCompanyID = recordsetSource[CompanyID]
 
  recordsetTarget.[nonConcatField1] = recordsetSource.[nonConcatField1]
  recordsetTarget.[nonConcatField2] = recordsetSource.[nonConcatField2]
  recordsetTarget.[nonConcatField3] = recordsetSource.[nonConcatField3]
 
  stringConcatField1 = recordsetSource.[ConcatField1]
  stringConcatField2 = recordsetSource.[ConcatField2]
  stringConcatField3 = recordsetSource.[ConcatField3]
 
  recordsetSource.MoveNext
  Do while recordsetSource[CompanyID] = stringCompanyID
    stringConcatField1 = stringConcatField1 & "," recordsetSource.[ConcatField1]
    stringConcatField2 = stringConcatField2 & "," recordsetSource.[ConcatField2]
    stringConcatField3 = stringConcatField3 & "," recordsetSource.[ConcatField3]
  Loop
 
  recordsetTarget.[ConcatField1] = stringConcatField1
  recordsetTarget.[ConcatField2] = stringConcatField2
  recordsetTarget.[ConcatField3] = stringConcatField3  
 
  recordsetTarget.Update
Loop



If you need your output as a table, have recordsetTarget output to a new (or truncated) table; otherwise use it as the datasource for whatever form you're in.  This still requires VB to cycle through every record in a query, but at least it isn't doing table comparisons multiple times for each of those records.

That said, I again agree with stevbe.  This will never be really fast, and you may want to reconsider whether this method of presentation is ideal.
0
 
LVL 4

Expert Comment

by:Rakafkaven
ID: 17147278
One way it actually can be fast (just to make myself a liar): if you know your data will only change once a day (or you don't care about data entered since yesterday), have your version of the above pseudocode output to a new table once a day-- when the database opens, have it check to see whether or the 'table last refreshed' date (which you'll have to create) is today, and if not, truncate it and run your code.  Then everything will just reference your static table, and it will be as fast as accessing any other static table.

Still a lot of work, and it may not be appropriate if you need to see changes on a moment-to-moment basis.  But it's an option.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17148245
another approach would be to make the data entry form do the concatenation and store the results on a per record basis so you will only be processing a few records when you add/edit/delete as opposed to whacking them all at once with query / code and you also eliminate any *old* data issues.

Steve
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17156516
Thanks for the new suggestions, but I'm swamped right now with projects that actually are important.  I will hopefully be able to look at this again next week, and let you know how it goes.
0
 
LVL 18

Author Comment

by:WarCrimes
ID: 17499094
The help was appreciated, and did get me half way to an answer, but never fully created a query that runs efficiently enough to be useable.  I wouldn't mind giving points, but also wondered if maybe deleting the question would be better since no one else will probably be able to gain any help from this question.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17531706
I think this should be PAQ and Refund because while we did not get a SQL that does the trick (because you simply can not do this with plain SQL) we did explore the issues with concatenating the results of the same field across aggregated records and provided some methods by which it can be done and also the inherent performance impact of this type of processing. While it may seem a bit esoteric I think *my* code :-) that improves performance 2x shows some neat tricks with Static, GetString and Collect.

Steve
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17535662
I have been on the fence between delete and PAQ on this one.  I think the question of improved efficeincy is very general to start with, and there are good contributions addressing that here even if they didn't produce "usable" results overall.  I'm okay with changing the recommendation to PAQ.  However without an independent solution provided by the author, I think credit should be given to the participants for providing sound contributions adressing performance issues (Warcrimes: if that sounds fair to you, feel free to accept the responses that you found most valuable).

Before I jump into changing the recommendation, are there any further comments?

If there are no further comments/objections/Action by the author/etc by Sept. 18, I'll assume that everyone is okay with a split between the following posts that I feel have added value here:

 GRayL {17102972},  Arthur_Wood {17105485}, stevbe {17110874}, Rakafkaven {17110808}

0
 
LVL 61

Expert Comment

by:mbizup
ID: 17535676
Let me try those links again :-)
 
GRayL {http:#17102972},  Arthur_Wood {http:#17105485}, stevbe {http:#17110874}, Rakafkaven {http:#17110808}
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17549123
Change Recommendation:
Split: GRayL {http:#17102972} &  Arthur_Wood {http:#17105485} & stevbe {http:#17110874} & Rakafkaven {http:#17110808}

 
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now