DLOOKUP being a PIA
in [Help]
|
From: The Frog on 4 May 2010 07:44 Hi David, I will re-write the subselect as a join based query. I dont need the result to be edited, they will be in fact used as the values for a listbox. I would have expected that a join query might be even more 'expensive' than a subselect. I will rewrite it and give it a go -- the join will be syntactically much easier to maintain for people who come after me to be sure. Referential integrity is my friend, so I should make the most of it :-) I will come back with the results. Cheers The Frog
From: David W. Fenton on 4 May 2010 22:54 The Frog <mr.frog.to.you(a)googlemail.com> wrote in news:6c52f1bc-5e8d-4426-bbd1-e96ac918cf5d(a)q30g2000yqd.googlegroups.co m: > I will re-write the subselect as a join based query. I dont need > the result to be edited, they will be in fact used as the values > for a listbox. I would have expected that a join query might be > even more 'expensive' than a subselect. I will rewrite it and give > it a go -- the join will be syntactically much easier to maintain > for people who come after me to be sure. Referential integrity is > my friend, so I should make the most of it :-) > > I will come back with the results. Subselects, in my experience, often get optimized without taking account of all the available usable indexes, while I've never seen a join that did not. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: The Frog on 5 May 2010 07:44
And the final criteria query............ SELECT segment.segment, dictSegment.raw_value, dictSegment.dictionary_id FROM segment INNER JOIN dictSegment ON segment.segment_id = dictSegment.segment_id WHERE (((segment.subcategory_id)=[lstSubCategory])); I saved this as a query (called it Assigned) My desired query is 'Unassigned', so my criteria is: Not In (SELECT raw_value FROM assigned) Damned simple in the end. Runs smooth too. No delay in the forms responses at all. Looks like the indexing is doing its job. Thanks for the tip David :-) Cheers The Frog |