In the query group by family ID you wish to see distinct rows for then if you do not care about the other field then specify first of file ID.
To do this in the QBE window put the family id in the first column and file ID in the second. Then select from the view menu "Totals" and specify Group by (default) in the first column and select first in the second. If more than one family id comes up then open the query to the SQL view and add "Distinct" after "Select".
There is a related table which I called Validity tbl which displays fields with errors for each OrgID. But not all OrgId's in the Validity tbl are present in the presence tbl. See table below:
OrgID FieldIssue Type
43432 Official Name Inconsistent
1234 Official Name Inconsistent
1234 Web URL Invalid Characters
1344 DOI Date not accepted
I want to create a query wherein, there will be a lookup of ORg IDs from Presence Tbl in the Validity Tbl. The org IDs with the same fields as in the Presence tbl. it will show a Fail if that org ID exist in the Validity table. The "Fail" comment will show in the specific column mentioned in the Field issue column. See below:
OrgID Official Name DOI Website
1234 Fail NA Fail
1235 NA NA NA
1344 NA Fail' Na
1235 NA NA NA
I tried doing this in MS Excel using Vlookup and it worked but it was really slow. I will be working on some 200thousand records and Access is pretty much quicker (as I tried presence and it worked with no wait time).