Sort query with a field I can't make visible

  • Thread starter Thread starter Max Moor
  • Start date Start date
M

Max Moor

Hi All,
Here's the story...

I have a table of items on which I run an SQL query. This table is
linked to another table that gives me names of people associated with each
item. Some items have more than one person associated with them, so the
table returns more than one record for some items.

This is a problem. I want to be able to filter and sort the query
based on this linked "fldPerson" field, though.

I found that I can leave fldPerson out of the SELECT clause, and
still use it in the WHERE clause for filtering. The double records are
gone, and I can still filter on it. Very Cool!

It doesn't appear that I'm as lucky with using fldPerson in the ORDER
BY clause though. I suppose it makes sense that you can't sort on a field
that you don't select. That understanding doesn't help me get everything I
want though.

Can anyone give me any advice on how to write the SQL so that:

1. I don't get the double records returned by the query because of multiple
people associated with a single item.

2. I can still filter on a person's name (returning only records associated
with that name)

3. I can sort on the person's name.

Any thoughts? Please?

- Max
 
It doesn't appear that I'm as lucky with using fldPerson in the ORDER
BY clause though. I suppose it makes sense that you can't sort on a field
that you don't select. That understanding doesn't help me get everything I
want though.
Click to expand...

Um? That's not my experience.
Can anyone give me any advice on how to write the SQL so that:

1. I don't get the double records returned by the query because of multiple
people associated with a single item.

2. I can still filter on a person's name (returning only records associated
with that name)

3. I can sort on the person's name.
Click to expand...

SELECT thisfield, thatfield, otherfield
FROM tablename
WHERE fldPerson IN ("jones","smith","hussein")
ORDER BY fldPerson;

should work...

If it doesn't, please open your query in SQL view and post it here.
 
Um? That's not my experience.

....
Click to expand...


Hi John,
Thanks for the response. After you said it should work, I played
around some more. It turns out that the DISTINCT was making it not work.
If I sorted on a field that wasn't SELECTed with DISTINCT in the SQL, I got
zero records. Fortunately, I don't need the DISTINCT. I'd still like to
understand though. Do you know why the DISTICT causess a problem this way?
 
Back
Top