Query on Access Database

Dear All,

Let me explain my code problem:

I have a calls table consisting of:

callno, ID, callbackdate

A Contact table consisting of:

ID (link between calls and contact),clientname,status,contactname,renewaldate

Data in table is Contact:

18,Nick Hunt,Saffrons Bistro,E,'null'
234,Julie Ludlam,The Lawns Nursing Home, B, 01/01/2006

Data in Calls:

18, 16/11/2004, 252
18,'null', 284
18,'null', 285
18,'null', 353
234,'null', 451
234,10/01/2005, 512

We require only the last entry for each record out of the calls database
(which I presume could be achieved by sorting by callno and using top 1 or
something ie ID18 we would require callbackdate of blank to be returned as
per callno 353 while ID 234 would return 10/01/2005

We are looking to display on the asp form:

Saffrons Bistro - Nick Hunt - E - [blank renewaldate] - [blank callbackdate]
The Lawns Nursing Home - Julie Ludlam/Rosemary Harris - B - 01/01/2006 -
10/01/2005

So each client has a single line rather than multiple lines for each call,
which I can achieve by using:

select s.status, s.id,
s.clientname,s.contactname,s.renewaldate,r.id,r.callbackdate ,r.callno from
contact s, calls r where s.id = r.id and s.status = 'Q' order by s.clientname
asc

Just to overly complicate things - there maybe a contact without any calls -
we'd still need to display them on the form with a blank obviously
callbackdate

Anyopne with any ideas how we can do this ??

I've managed to get halfway towards solving it by doing:

select max(r.callno), s.status, s.id,
s.clientname,s.contactname,s.renewaldate,r.id,r.callbackdate

blah blah blah

from

contact s

left join calls r on s.id = r.id

group by

s.status, s.id,
s.clientname,s.contactname,s.renewaldate,r.id,r.callbackdate ,r.callno

Help >>>

Re: Query on Access Database

It sounds like you really know what you're doing. And you say you're
about halfway there although at first I couldn't tell what half you were
missing.

After thinking about it I believe it might be the subquery concept that
you still need to somehow wrap things up.

And here's a little something I wrote to myself awhile back about using
subqueries:

Example of one query (QueryB) based on the results of another query
(QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" &
QueryA & ")"

But the following is even faster and allows for more than one field to
be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS
tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not
just the CustID field) for the records returned in QueryA (which was the
customers starting with A).

I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build
from the tblSQLA on the left to other tables that have fields you want
to return.

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


<<
Query on Access Database
From: Matt Simms

Dear All,

Let me explain my code problem:

I have a calls table consisting of:

callno, ID, callbackdate

A Contact table consisting of:

ID (link between calls and
contact),clientname,status,contactname,renewaldate

Data in table is Contact:

18,Nick Hunt,Saffrons Bistro,E,'null'
234,Julie Ludlam,The Lawns Nursing Home, B, 01/01/2006

Data in Calls:

18, 16/11/2004, 252
18,'null', 284
18,'null', 285
18,'null', 353
234,'null', 451
234,10/01/2005, 512

We require only the last entry for each record out of the calls database
(which I presume could be achieved by sorting by callno and using top 1
or
something ie ID18 we would require callbackdate of blank to be returned
as
per callno 353 while ID 234 would return 10/01/2005

We are looking to display on the asp form:

Saffrons Bistro - Nick Hunt - E - [blank renewaldate] - [blank
callbackdate]
The Lawns Nursing Home - Julie Ludlam/Rosemary Harris - B - 01/01/2006 -
10/01/2005

So each client has a single line rather than multiple lines for each
call,
which I can achieve by using:

select s.status, s.id,
s.clientname,s.contactname,s.renewaldate,r.id,r.callbackdate ,r.callno
from
contact s, calls r where s.id = r.id and s.status = 'Q' order by
s.clientname
asc

Just to overly complicate things - there maybe a contact without any
calls -
we'd still need to display them on the form with a blank obviously
callbackdate

Anyopne with any ideas how we can do this ??

I've managed to get halfway towards solving it by doing:

select max(r.callno), s.status, s.id,
s.clientname,s.contactname,s.renewaldate,r.id,r.callbackdate

blah blah blah

from

contact s

left join calls r on s.id = r.id

group by

s.status, s.id,
s.clientname,s.contactname,s.renewaldate,r.id,r.callbackdate ,r.callno

Help >>>
>>

*** Sent via Developersdex http://www.developersdex.com ***