tbsgadi
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
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;
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
* 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
The question still remains why does a query work within a form but doesn't display?
ASKER
I've come across this code before, but never tried it,assuming (wrongly) that it wouldn't be any quicker than Serialize.
Thanks!
Thanks!
You are welcome!
/gustav
/gustav
ASKER
FYI I've managed to make the original query work..it needed to have ORDER BY o1.DueDate ;
ASKER