How to use TOP 1 value

[Nick Mirro]

I have 2 tables in a query.


Key
tblVisits VisitID
tblCallInfo CallID

There are many Calls per Visit.

I want a query that will return just the first listed call per Visit.

I tried a query with these fields


Field Table Relationship side

VisitID tblVisits One
DateOfCall tblCallInfo Many
Communication tblCallInfo Many

The only way I can return the correct records is if I set DateOfCall to
ASCENDING, and Top Values to 1, which I don't understand. Problem with this
is that the fields from tblCallInfo are blank. Any other arrangement yields
duplicate VisitIDs from the many side, or very few records. (for more info,
see post at 1:25 PM)

Nick

 

[Marshall Barton]

I have 2 tables in a query.


Key
tblVisits VisitID
tblCallInfo CallID

There are many Calls per Visit.

I want a query that will return just the first listed call per Visit.

I tried a query with these fields


Field Table Relationship side

VisitID tblVisits One
DateOfCall tblCallInfo Many
Communication tblCallInfo Many

The only way I can return the correct records is if I set DateOfCall to
ASCENDING, and Top Values to 1, which I don't understand. Problem with this
is that the fields from tblCallInfo are blank. Any other arrangement yields
duplicate VisitIDs from the many side, or very few records. (for more info,
see post at 1:25 PM)

Add a Criteria for the DateOfCall field:

Is Not Null

 

[Nick Mirro]

Thanks for helping with this. Its doesn't work. 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

 

[John Spencer (MVP)]

One method which may be slow:


SELECT VisitID, DateOfCall, Communication
FROM tblVisits as V INNER JOIN tblCallInfo as C
ON t.VisitID = C.VisitID
WHERE DateOfCall IN
(SELECT Min(tmp.DateOfCall)
FROM tblCallInfo as TMP
WHERE tmp.CallID = C.CallID)


Of course, that does assume that there is some relationship between the two tables.

 

[Nick Mirro]

Thanks again John. Here's what I ended up with, but I get a syntax in JOIN
operation error.

SELECT tblVisits.VisitID, tblCallInfo.DateOfCall, tblCallInfo.Communication
FROM tblVisits as V INNER JOIN tblCallInfo as C
ON t.VisitID = C.VisitID
WHERE tblCallInfo.DateOfCall IN
(SELECT Min(tmp.tblCallInfo.DateOfCall)
FROM tblCallInfo.tblCallInfo as TMP
WHERE tmp.tblCallInfo.CallID = C.CallInfo.CallID;

The tables are many to one. Did I get this part right?

 

[Rod Scoullar]

There appears to be a minor typo (pedantic things computers).

Should be ON V.VisitID = C.VisitID

Rod

 

[Tom Ellison]

Dear Nick:

You have "aliased" tblVisits as V and tblCallInfo as C in the main
query. You must use only the alias to reference them everywhere in
the query. The original references to them by the full names of the
tables cannot be used any longer. Later you have another instance of
tblCallInfo which is aliased as tmp. This allows you to make
references between the two instances of that table unambiguously.

SELECT V.VisitID, C.DateOfCall, C.Communication
FROM tblVisits as V
INNER JOIN tblCallInfo as C ON V.VisitID = C.VisitID
WHERE C.DateOfCall IN
(SELECT Min(tmp.tblCallInfo.DateOfCall)
FROM tblCallInfo.tblCallInfo as TMP
WHERE tmp.tblCallInfo.CallID = C.CallInfo.CallID;

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Nick Mirro]

Using this corrected version, I get prompted for C.tblCallInfo.CallID. I'm
not much help here unfortunately.

Nick

SELECT V.VisitID, C.DateOfCall, C.Communication
FROM tblVisits as V
INNER JOIN tblCallInfo as C ON V.VisitID = C.VisitID
WHERE C.DateOfCall IN
(SELECT Min(tmp.DateOfCall)
FROM tblCallInfo as TMP
WHERE tmp.CallID = C.tblCallInfo.CallID);

 

[Tom Ellison]

Dear Nick:

I failed to see and correct this:

SELECT V.VisitID, C.DateOfCall, C.Communication
FROM tblVisits as V
INNER JOIN tblCallInfo as C ON V.VisitID = C.VisitID
WHERE C.DateOfCall IN
(SELECT Min(tmp.DateOfCall)
FROM tblCallInfo as TMP
WHERE tmp.CallID = C.CallID);

You had both the alias AND the acutal table name at that point. You
must use ONLY the alias.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Nick Mirro]

All works now. Thanks for your time and patience with this. I'm sure any
of these suggestions could be nicely implemented if I had any sort of skill
at this.

 

[Tom Ellison]

Dear Nick:

"I'm sure any of these suggestions could be nicely implemented if I
had any sort of skill at this."

Well, then, that's the point - to help point you toward getting the
skills you want.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts