Query field needs to return only first record in 1 to Many

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

Nick Mirro

I have a query which produced 200 records, the number based on VisitID in
one table. There is another table in the query "tblCallInfo" that has Many
records for each One VisitID.


tblCallInfo key Fields
CallID DateOfCall
Communication


The table contains this above memo field "Communication" and a date field
"DateOfCall."

The query generates 200 records based on the key VisitID. For 1 VisitID, I
only want 1 "Communication" record field returned corresponding to the
earliest "DateOfCall," even though there are many CallIDs for each VisitID.

Sometimes the records in tblCallInfo are Null.

What criteria can I add to prevent the multiple instances of the field
"communication" from being generated.

Nick
 
Make a query with the DateOfCall sorted descending, and set the TOP 1
clause:

SELECT TOP 1 <fieldlist> ORDER BY DateOFCall Descending

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Thanks for helping with this. Its not working. No arrangement of sorting
and or "top 1" seems to produce more than 4 records. I tried "DistinctRow"
but get a data type mismatch error.

Nick
 
Sorry, I dont understand. I thought you wanted only one record, now you say
you want more than 4 ?
What I usually do in debugging queries is separate them out into small bits,
a query to return the first subset of the data you want, then use that query
as the source for the next query up, returning the next bit of the criteria.
Once you have the result correct for each part of the chain, doing the next
bit is usually easy. Experts in SQL can build complex queries in one go,
but I am not an expert.

I see you also posted a query in formscoding about how to use the TOP 1
clause, using the same example. While there is no restriction on doing
this, it makes it confusing for both me and the other posters trying to
help. If you must do this, cross-post, rather than multi-post.
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Well that's probably a better approach for me. I think I can create a query
that can eliminate duplicates in the table, and then join it to the next
table via query. Thanks for the tip.
 
Back
Top