Stumped on query, group count max type

[bringmewater]

Can anyone tell me how to do this query please?

3 fields. provider, site, visitdate. Given a date range for
visitdate,

Return all providers that have at least one visit in that date range
and also display which site has the most visitdates for EACH provider
for all records (not date constrained).

Since a provider can have visits at multiple sites I only want to
return the site where they have the most visits.

THANKS A LOT

Also, is there a limit on nesting of queries like 3 levels deep?

 

[tw]

set up
this query (query1) will get a list of providers that had a visit within a
date range

SELECT tblVisits.provider
FROM tblVisits
GROUP BY tblVisits.provider
HAVING (((Max(tblVisits.visitdate)) Between [from] And [ to]));

this query will give you a list of the sites and count of visits for each
provider regardless of date range for providers who had a visit within the
date range.

SELECT Query1.provider, tblVisits.site, Count(tblVisits.site) AS CountOfsite
FROM Query1 INNER JOIN tblVisits ON Query1.provider = tblVisits.provider
GROUP BY Query1.provider, tblVisits.site;

is this something like your asking for?

 

[MGFoster]

Can anyone tell me how to do this query please?

3 fields. provider, site, visitdate. Given a date range for
visitdate,

Return all providers that have at least one visit in that date range
and also display which site has the most visitdates for EACH provider
for all records (not date constrained).

Since a provider can have visits at multiple sites I only want to
return the site where they have the most visits.

Also, is there a limit on nesting of queries like 3 levels deep?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your specification is ambiguous: in one paragraph you say "at least one
visit"; in another paragraph you say "only want to return the site where
they have the most visits." Can't be both - that would be 2 queries.

Perhaps this,

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT provider, site,
(SELECT Count(visitdate) FROM tableA As B
WHERE B.provider = A.provider
AND B.site = A.site) As TotalVisits
FROM tableA As A
WHERE visitdate BETWEEN [Start Date?] And [End Date?]
GROUP BY provider, site
ORDER BY 3 DESC

The provider, site w/ the most visits will be at the top of the results.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnAUEYechKqOuFEgEQIIPACfdEDL+ldHeg/a3nPHDpGCBtkxIPQAniNN
ih9AgI1kf/ladmSvt0ayC/Q/
=+G6b
-----END PGP SIGNATURE-----