subquery

  • Thread starter Thread starter Kou Vang
  • Start date Start date
K

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
 
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;
 
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
 
Back
Top