So I have a table called [2012 raw], and it has many fields, two of which are [Capture ID] and [Internet Site].
I want to count how many internet sites there are per capture ID.
So I created this query:
But the counting numbers are way too high. Clearly, there is some kind of double counting going on.
Now, there are other fields besides the ones listed above. Lots of them. And there are some pairs of records that have the same data for Internet Site and Capture ID, but different data for the other fields.
But those other fields are not included in the query. I'm confused about how to prevent the double counting.
So what I end up doing in cases like this, is that first I'll do a query where I just group both fields, like this:
...and then I'll run that as a make-table, and then I'll run the count query against that new table, and then I'll get the results I want.
Is that how it's typically done? Obviously I'm not very good at SQL and need help understanding the order of operations.
thanks...
I want to count how many internet sites there are per capture ID.
So I created this query:
Code:
SELECT [2012 raw].[Capture ID], Count([2012 raw].[Internet Site]) AS [CountOfInternet Site]
FROM [2012 raw]
GROUP BY [2012 raw].[Capture ID];
But the counting numbers are way too high. Clearly, there is some kind of double counting going on.
Now, there are other fields besides the ones listed above. Lots of them. And there are some pairs of records that have the same data for Internet Site and Capture ID, but different data for the other fields.
But those other fields are not included in the query. I'm confused about how to prevent the double counting.
So what I end up doing in cases like this, is that first I'll do a query where I just group both fields, like this:
Code:
SELECT [2012 raw].[Capture ID], [2012 raw].[Internet Site]
FROM [2012 raw]
GROUP BY [2012 raw].[Capture ID], [2012 raw].[Internet Site];
...and then I'll run that as a make-table, and then I'll run the count query against that new table, and then I'll get the results I want.
Is that how it's typically done? Obviously I'm not very good at SQL and need help understanding the order of operations.
thanks...