|
 |
access thread: (SubQuery Help!) SQL Syntax
Message #1 by "Gary Clark" <glc.home@v...> on Wed, 18 Jul 2001 16:04:38
|
|
Using Microsoft Access:
I have a "Contacts" recordset, and a "Calls" recordset. The calls
recordset contains date/time, duration, and call result information that
relates back to "Contacts" by ContactId.
There are several "Calls" for several "Contacts" in a day.
I want to select the Contacts.Name, Contacts.Phone, Calls.DateTime,
Calls.Duration, and Calls.Result for the very last call on every Contact
that has a call for a given day.
Everything I have tried only returns the information for 1 contact that
had the very last call.
Message #2 by "John Ruff" <John_Ruff@m...> on Wed, 18 Jul 2001 08:10:21 -0700
|
|
Group your Contacts Recordset on Contact.Name, Contact.Phone, and
max(Calls.DateTime)
Create another recordset from the Grouped recordset and join the Calls
recordset with the Contacts recordset with the ContactID and DateTime
This will give you the data you need.
John Ruff - The Eternal Optimist :)
-----Original Message-----
From: Gary Clark [mailto:glc.home@v...]
Sent: Wednesday, July 18, 2001 4:05 PM
To: Access
Subject: [access] (SubQuery Help!) SQL Syntax
Using Microsoft Access:
I have a "Contacts" recordset, and a "Calls" recordset. The calls
recordset contains date/time, duration, and call result information that
relates back to "Contacts" by ContactId.
There are several "Calls" for several "Contacts" in a day.
I want to select the Contacts.Name, Contacts.Phone, Calls.DateTime,
Calls.Duration, and Calls.Result for the very last call on every Contact
that has a call for a given day.
Everything I have tried only returns the information for 1 contact that
had the very last call.
Message #3 by "Darron Michael" <darron.michael@h...> on Wed, 18 Jul 2001 16:25:28
|
|
There is probably a "Finesse" way but for now here is the "Brute Force"
way.
Use 2 Queries.
Query1 selects ContactID and DateTime from from Calls grouping by
contactID and choosing Max value of datetime.
Query2 uses Contacts, Calls, and Query1. It finds the Name, and Phone
from contacts where contactID is the same, and it finds the date/time,
duration, and result from Calls where the contactID is the same AND the
datetime is the same.
HTH
8^)
Darron
> Using Microsoft Access:
>
> I have a "Contacts" recordset, and a "Calls" recordset. The calls
> recordset contains date/time, duration, and call result information that
> relates back to "Contacts" by ContactId.
>
> There are several "Calls" for several "Contacts" in a day.
>
> I want to select the Contacts.Name, Contacts.Phone, Calls.DateTime,
> Calls.Duration, and Calls.Result for the very last call on every Contact
> that has a call for a given day.
>
> Everything I have tried only returns the information for 1 contact that
> had the very last call.
|
|
|
View Cart
