From: The Frog on
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
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
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