SQL Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am trying to run a join query, which yields 10 fields and which joins 2
tables, 1 table is the one, the other contains the "many".

I want to only grab the first record containing duplicate key field value
from the many table. However, the DISTINCT clause evaluates the entire
record, not just the key field.

Any ideas?
 
DISTINCT does not evaluate the entire record. It only evaluates the fields
in the SELECT statement. I think what you want is DISTINCTROW.
 
David said:
I am trying to run a join query, which yields 10 fields and which joins 2
tables, 1 table is the one, the other contains the "many".

I want to only grab the first record containing duplicate key field value
from the many table. However, the DISTINCT clause evaluates the entire
record, not just the key field.
Click to expand...
PMFBI

One other idea might be to switch to
a Totals query (click on sigma icon).

In the Totals row under fields of "many table,"
leave as "Group By"

for fields from "one table,"
change "Group By" to "First"

remember, the "First" aggregates
will only guarantee the results returned
will come from the same record
(the "first" record that Access sees
may not mean what you mean by
"first" unless some specific order
is applied )

Apologies again for butting in...
 
Thanks for your response.

What I meant by the entire record ARE the fields in the SELECT statement.

Of the fields in the SELECT statement, the only one I care about being
DISTINCT is the key field. However, DISTINCT evaluates all fields in the
SELECT, collectively.
 
Back
Top