Link to home
Start Free TrialLog in
Avatar of tbsgadi
tbsgadiFlag for Israel

asked on

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

Avatar of tbsgadi
tbsgadi
Flag of Israel image

ASKER

BTW the same happens in Access 2007
Avatar of Rey Obrero (Capricorn1)
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
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tbsgadi

ASKER

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?
Avatar of tbsgadi

ASKER

I've come across this code before, but never tried it,assuming (wrongly) that it wouldn't be any quicker than Serialize.
Thanks!
You are welcome!

/gustav
Avatar of tbsgadi

ASKER

FYI I've managed to make the original query work..it needed to have ORDER BY o1.DueDate ;