|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 22nd, 2009, 10:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
subquery
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 
|
|
#2
December 22nd, 2009, 11:14 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
subquery
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; -- Build a little, test a little. "Kou Vang" wrote: 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
|
|
#3
December 23rd, 2009, 02:22 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
subquery
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 Kou Vang wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
