combining views to populate combobox 1

[embryo]

Hello-

I'm trying to write a view whose recordset will be the RowSource for a big combo box that essentially displays a user's search results. I've done this before, but am now having trouble.
Ideally, the combobox will display an account name, ID number, client name, reference number, last call date, last call time, and last call result.

I have a table named "contacts" which contains the account name, ID number, reference number, and client ID number. I have created a view named "clientnames", which fetches the clientname based on the clientID value in the contact record, and another view named "lastcall" which is SUPPOSED to fetch the last record in the "calls" table for the corresponding ID number.

SELECT DISTINCT MAX(DISTINCT tstamp) AS theLastCall, ContactID, CallDate, CallTime, Result
FROM         dbo.Calls
GROUP BY ContactID, CallDate, CallTime, Result

I think this is where my problem lies, because this is a new database with empty tables except for a few test records I created, and I'm getting more than one record returned. I should be seeing this:
theLastCall Contact ID Call Date Call Time Result
6/2/2009 9:33:23 PM 1 6/2/2009 9:33 PM LMOR

but it returns this:
theLastCall Contact ID Call Date Call Time Result
6/2/2009 9:21:03 PM 1 6/2/2009 9:20 PM BUSY
6/2/2009 9:33:23 PM 1 6/2/2009 9:33 PM LMOR

Can someone tell me why neither MAX nor MAX Distinct will limit my result to the last record in the table?

Below is the code for the master view to populate the combobox...which currently returns NO RECORDS...

SELECT     dbo.Contacts.Accountname AS [Account Name], dbo.clientnames.ClientID AS [Client Name], dbo.Contacts.ClientRefNumber AS [Ref. #], 
                      dbo.Contacts.ClientID, dbo.Contacts.ContactID, dbo.lastcall.Result, dbo.lastcall.CallTime, dbo.lastcall.CallDate
FROM         dbo.Contacts INNER JOIN
                      dbo.clientnames ON dbo.Contacts.ClientID = dbo.clientnames.ClientID LEFT OUTER JOIN
                      dbo.lastcall ON dbo.Contacts.ContactID = dbo.lastcall.ContactID
WHERE     (dbo.Contacts.Active = 1)
GROUP BY dbo.Contacts.ClientRefNumber, LEFT(dbo.clientnames.ClientID, 3), dbo.clientnames.ClientID, dbo.Contacts.Accountname, dbo.Contacts.ContactID, 
                      dbo.Contacts.ClientID, dbo.lastcall.Result, dbo.lastcall.CallTime, dbo.lastcall.CallDate
HAVING      (LEFT(dbo.clientnames.ClientID, 3) = dbo.Contacts.ClientID)

Thanks in advance for any insight-
Steve

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[PWise]

for starters your first query should be

SELECT DISTINCT MAX(DISTINCT tstamp) AS theLastCall, ContactID, CallDate, CallTime, Result
from calls
inner join(
           SELECT ContactID,MAX(DISTINCT tstamp) AS theLastCall
           from dbo.calls
           group by ContactID
           )lastcall
on lastcall.contactid=calls.contactid
and lastcall.theLastCall=calls.tstamp

 

[embryo]

pwise:

Hmmm...I initially tried to use a subquery, but couldn't get past a multitude of ADO errors saying that each of the fields weren't part of the aggregegate expression or included in the group by clause...
Got the same error just now using your code, but then added those fields into an additional group by clause just now, and Voila!...it worked, only the one expected record is returned. Thanks!

However...my master view still returns no records, so I'm still open to suggestions-

Steve

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[PWise]

try this as the last call query

SELECT tstamp AS theLastCall, ContactID, CallDate, CallTime, Result
from calls
inner join(
           SELECT ContactID,MAX(tstamp) AS theLastCall
           from dbo.calls
           group by ContactID
           )lastcall
on lastcall.contactid=calls.contactid
and lastcall.theLastCall=calls.tstamp

and this as seconed query

SELECT     dbo.Contacts.Accountname AS [Account Name], dbo.clientnames.ClientID AS [Client Name], dbo.Contacts.ClientRefNumber AS [Ref. #], 
dbo.Contacts.ClientID, dbo.Contacts.ContactID, dbo.lastcall.Result, dbo.lastcall.CallTime, dbo.lastcall.CallDate
FROM         dbo.Contacts 
INNER JOIN dbo.clientnames 
ON LEFT(dbo.clientnames.ClientID, 3) = dbo.Contacts.ClientID 
And   (dbo.Contacts.Active = 1)
LEFT OUTER JOIN dbo.lastcall 
ON dbo.Contacts.ContactID = dbo.lastcall.ContactID

 

[embryo]

Wow...thanks man, that did the trick!
Have a great day!

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If