• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

Serialize using Subquery

Hi experts,

Up till now I've been using Lebans Serialize function to add sequential numbers to my query.
For a particular query it was very slow, so I decided to take a different path. I use a subquery, & in the form it works fine...much quicker than the serialize function.
The trouble is when I try and view the query Access blows up/Closes down...no message.
Any ideas why it should work in a form, but not able to view it.
I can also view it in design mode.

Attached please find the query..It's based on many others..I can't post them all.

I'm using Access 2003 SP3 + latest hotfixes

Gary
SELECT o1.ContactID, (SELECT COUNT(contactid) FROM QryPreSubTotalFacility AS o2 WHERE o2.DueDate <=O1.DueDate) AS InstNo, o1.DueDate
FROM QryPreSubTotalFacility AS o1;

Open in new window

0
tbsgadi
Asked:
tbsgadi
  • 4
  • 2
1 Solution
 
tbsgadiAuthor Commented:
BTW the same happens in Access 2007
0
 
Rey Obrero (Capricorn1)Commented:
1. convert the query QryPreSubTotalFacility to a make table query (this is just a test)
   * run a query with the count(*) subquery using the created table, see if you still get the problem

2. can you post the SQL of query QryPreSubTotalFacility
0
 
Gustav BrockCIOCommented:
Here is a very fast method using a Collection.
The in-line comments explain typical usage.

/gustav
Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Or strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

Open in new window

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
tbsgadiAuthor Commented:
Cap I tried the temp Table & of course it worked fine.
The question still remains why does a query work within a form but doesn't display?
0
 
tbsgadiAuthor Commented:
I've come across this code before, but never tried it,assuming (wrongly) that it wouldn't be any quicker than Serialize.
Thanks!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
tbsgadiAuthor Commented:
FYI I've managed to make the original query work..it needed to have ORDER BY o1.DueDate ;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now