List only 1 Rec No when more than 1 match

[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

 

[Heather]

Try:

Select fldID, first(fldDWGNo) from tblDWG, tblFilter
where
tblFilter.fldID = tblDWG.fldDWGNo

 

[John Vinson]

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.

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.

 

[Kahuna]

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?

 

[John Vinson]

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?

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>".

 

[Kahuna]

Thanks John - spot on as usual