List only 1 Rec No when more than 1 match

  • Thread starter Thread starter Kahuna
  • Start date Start date
K

Kahuna

Hi Folks, I have two tables:

tblFilter (one)
fldID

tblDWG (many)
fldDWGNo
fldID

They link on fldID and tblDWG may have many records matching one tblFilter.

I need to list the records in tblDWG which match those in tblFilter, but
list those records only once regardless of how many there are! The 1st
record takes precedence.

Any guidance would be appreciated.

Cheers
 
Try:

Select fldID, first(fldDWGNo) from tblDWG, tblFilter
where
tblFilter.fldID = tblDWG.fldDWGNo
 
Hi Folks, I have two tables:

tblFilter (one)
fldID

tblDWG (many)
fldDWGNo
fldID

They link on fldID and tblDWG may have many records matching one tblFilter.

I need to list the records in tblDWG which match those in tblFilter, but
list those records only once regardless of how many there are! The 1st
record takes precedence.
Click to expand...

Make it a Totals query by clicking the Greek Sigma icon; group by
fldID and select First of fldDWGNo. This will show the first record IN
DISK STORAGE ORDER - bear in mind that this order is *completely
arbitrary*, it won't necessarily be the first record entered. Use Min
instead of First if you want the lowest numeric value of fldDWGNo.
 
Thanks for the feedback John, but this result appears to exclude any records
where the fldDWG may be null (this can be the case) though that record
should still be listed bt fldID (in tblFilter)

Any suggestions to show those records?
 
Thanks for the feedback John, but this result appears to exclude any records
where the fldDWG may be null (this can be the case) though that record
should still be listed bt fldID (in tblFilter)

Any suggestions to show those records?
Click to expand...

Change the join type to Left Outer Join - select the line joining the
two tables in query design view and choose Option 2 (or 3), "show all
records in <first table> and matching records in <the second table>".
 
Back
Top