subquery

[Kou Vang]

How can I write a subquery within a query from:

Lake, Site, PersonID
01, 101, 0001
01, 101, 0001
01, 101, 0001
01, 101, 0002
01, 101, 0002
01, 101, 0003
01, 102, 0001
01, 102, 0001

To get: the total visits by site and total by person.

Thanks,

Kou

 

[KARL DEWEY]

Try this --
SELECT Site, (SELECT Count([YY].[Site] FROM YourTable
AS [XX]) AS Site_Visits, PersonID, (SELECT Count([YY].[PersonID] FROM
YourTable
AS [YY]) AS Person_Visits
FROM YourTable;

 

[John Spencer]

It is a bit unclear as to what you want. Perhaps the following will give you
an idea that you can use to solve your problem.

SELECT DISTINCT Lake, Site, PersonID
, (SELECT Count(*) FROM TheTable as Temp WHERE Temp.Lake = TheTable.Lake and
Temp.Site = TheTable.Site) as SiteCount
, (SELECT Count(*) FROM TheTable as Temp WHERE Temp.Lake = TheTable.Lake and
Temp.PersonID= TheTable.PersonID) as PersonCount
FROM TheTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County