Another SQL/query question

[DH]

Say I have a table with two columns that matter to this example,
[PointNumber] and [SurveyDate]. And here is an example of what the rows
currently look like:

[PointNumber], [SurveyDate]
1,6/27/2001
1,6/27/2001
1,5/31/2001
2,6/27/2001
2,6/30/2001
2,5/31/2001

I want a query that will return PointNumber and the number of surveys (ie.
how many different dates are in the table for each point). For the example
above, the query should output:

[PointNumber], [NumberOfSurveys]
1,2
2,3

I'd appreaciate any assistance. This seems like it would be simple but I
don't know how to do it.

[dogwalker]

first make a query that returns unique pointnumber, surveydate combinations
(the 'unique values' query property will turn on 'select distinct')
then using that query, make a query that groups by pointnumber and counts
surveydate.
"DH" <dana_hartley@(remove)hotmail.com> wrote in message
news:vn************@corp.supernews.com...

Say I have a table with two columns that matter to this example,
[PointNumber] and [SurveyDate]. And here is an example of what the rows
currently look like:

[PointNumber], [SurveyDate]
1,6/27/2001
1,6/27/2001
1,5/31/2001
2,6/27/2001
2,6/30/2001
2,5/31/2001

I want a query that will return PointNumber and the number of surveys (ie.
how many different dates are in the table for each point). For the example
above, the query should output:

[PointNumber], [NumberOfSurveys]
1,2
2,3

I'd appreaciate any assistance. This seems like it would be simple but I
don't know how to do it.

[HSalim]

ONE query is all you need
Select PointNumber, Count(*) from
YourTable
group by PointNumber
"dogwalker" <[e-mail]> wrote in message
news:30******************@news20.bellglobal.com...

first make a query that returns unique pointnumber, surveydate combinations (the 'unique values' query property will turn on 'select distinct')
then using that query, make a query that groups by pointnumber and counts
surveydate.
"DH" <dana_hartley@(remove)hotmail.com> wrote in message
news:vn************@corp.supernews.com...

Say I have a table with two columns that matter to this example,
[PointNumber] and [SurveyDate]. And here is an example of what the rows
currently look like:

[PointNumber], [SurveyDate]
1,6/27/2001
1,6/27/2001
1,5/31/2001
2,6/27/2001
2,6/30/2001
2,5/31/2001

I want a query that will return PointNumber and the number of surveys (ie. how many different dates are in the table for each point). For the example above, the query should output:

[PointNumber], [NumberOfSurveys]
1,2
2,3

I'd appreaciate any assistance. This seems like it would be simple but I
don't know how to do it.

[Mike MacSween]

I don't think so. You'll see she has 2 identical dates for PointNumber 1.
But she wants unique dates.

I think it does need another query. I tried to do it in one query but
couldn't find a way.

Mike

"HSalim" <On******************@msn.com> wrote in message
news:6X*******************@nwrdny01.gnilink.net...

ONE query is all you need
Select PointNumber, Count(*) from
YourTable
group by PointNumber
"dogwalker" <[e-mail]> wrote in message
news:30******************@news20.bellglobal.com...

first make a query that returns unique pointnumber, surveydate

combinations

(the 'unique values' query property will turn on 'select distinct')
then using that query, make a query that groups by pointnumber and counts

surveydate.
"DH" <dana_hartley@(remove)hotmail.com> wrote in message
news:vn************@corp.supernews.com...

Say I have a table with two columns that matter to this example,
[PointNumber] and [SurveyDate]. And here is an example of what the rows currently look like:

[PointNumber], [SurveyDate]
1,6/27/2001
1,6/27/2001
1,5/31/2001
2,6/27/2001
2,6/30/2001
2,5/31/2001

I want a query that will return PointNumber and the number of surveys

(ie. how many different dates are in the table for each point). For the example above, the query should output:

[PointNumber], [NumberOfSurveys]
1,2
2,3

I'd appreaciate any assistance. This seems like it would be simple but I don't know how to do it.

[HSalim]

Think again.

The requirement was:
the query should output:
[PointNumber], [NumberOfSurveys]
1,2
2,3

This is fulfilled by the query I posted.

If you want to count the number of surveys by points and dates use
Select PointNumber, SurveyDate, Count(*) as NumberOfSurveys
from YourTable
group by PointNumber, SurveyDate

HS

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@pubnews.gradwell.ne t...

I don't think so. You'll see she has 2 identical dates for PointNumber 1.
But she wants unique dates.

I think it does need another query. I tried to do it in one query but
couldn't find a way.

Mike

"HSalim" <On******************@msn.com> wrote in message
news:6X*******************@nwrdny01.gnilink.net...

ONE query is all you need
Select PointNumber, Count(*) from
YourTable
group by PointNumber
"dogwalker" <[e-mail]> wrote in message
news:30******************@news20.bellglobal.com...

first make a query that returns unique pointnumber, surveydate combinations

(the 'unique values' query property will turn on 'select distinct')
then using that query, make a query that groups by pointnumber and counts surveydate.
"DH" <dana_hartley@(remove)hotmail.com> wrote in message
news:vn************@corp.supernews.com...
> Say I have a table with two columns that matter to this example,
> [PointNumber] and [SurveyDate]. And here is an example of what the rows > currently look like:
>
> [PointNumber], [SurveyDate]
> 1,6/27/2001
> 1,6/27/2001
> 1,5/31/2001
> 2,6/27/2001
> 2,6/30/2001
> 2,5/31/2001
>
> I want a query that will return PointNumber and the number of

surveys

(ie.

> how many different dates are in the table for each point). For the

example

> above, the query should output:
>
> [PointNumber], [NumberOfSurveys]
> 1,2
> 2,3
>
> I'd appreaciate any assistance. This seems like it would be simple

but I > don't know how to do it.
>
>
>

[Mike MacSween]

"HSalim" <On******************@msn.com> wrote in message
news:Il*******************@nwrdny01.gnilink.net...

Think again.

The requirement was:
the query should output:
[PointNumber], [NumberOfSurveys]
1,2
2,3

Well, I just ran your query, with a table of the data that Dana supplied.
And I got:

1,3
2,3

So what am I doing wrong?

Did you get a different result when you ran your query against the sample
data?

Mike

[HSalim]

Mike,
I am so sorry. I feel like such a fool.
I was so cock sure of myself that I did not read what you wrote.
You were ofcourse right. The resuts in my query does not match the desired
output.

The correct answer is
SELECT S.PointNumber, count(S.SurveyDate) as SurveyCount
FROM [Select Distinct PointNumber, SurveyDate from surveys]. as S
GROUP BY S.PointNumber;

which is essentially using a subquery that first returns distinct values

HS
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@pubnews.gradwell.ne t...

"HSalim" <On******************@msn.com> wrote in message
news:Il*******************@nwrdny01.gnilink.net...

Think again.

The requirement was:
the query should output:
[PointNumber], [NumberOfSurveys]
1,2
2,3

Well, I just ran your query, with a table of the data that Dana supplied.
And I got:

1,3
2,3

So what am I doing wrong?

Did you get a different result when you ran your query against the sample
data?

Mike

[Mike MacSween]

"HSalim" <On******************@msn.com> wrote in message
news:kv******************@nwrdny02.gnilink.net...

Mike,
I am so sorry. I feel like such a fool.
I was so cock sure of myself that I did not read what you wrote.
You were ofcourse right. The resuts in my query does not match the desired output.
No need to apologise. It's easy to misread things.
The correct answer is
SELECT S.PointNumber, count(S.SurveyDate) as SurveyCount
FROM [Select Distinct PointNumber, SurveyDate from surveys]. as S
GROUP BY S.PointNumber;

Why couldn't I get that! I knew there was a simple single query answer to
this. And I knew it was something to do with DISTINCT. I'll get it next time
one of these comes up.

Cheers, Mike

[HSalim]

Mike,

Thanks for letting me off the hook so gracefully.

FYI: There is a way to do this in one query - i.e. without requiring a
subquery,
if you use ANSI SQL 92 Syntax. - unfortunately,
a. due to a known problem in Access, you will get an error.
b. it is a database wide option, so you may not want to choose this option.

Anyway, the single query syntax is simply
SELECT surveys.PointNumber,Count(DISTINCT surveys.SurveyDate) AS
CountOfSurveyDate
FROM surveys GROUP BY surveys.PointNumber;

You may find it useful when microsoft gets around to fixing the problem.
see
http://support.microsoft.com/default...roduct=acc2002

HS
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@pubnews.gradwell.ne t...

"HSalim" <On******************@msn.com> wrote in message
news:kv******************@nwrdny02.gnilink.net...

Mike,
I am so sorry. I feel like such a fool.
I was so cock sure of myself that I did not read what you wrote.
You were ofcourse right. The resuts in my query does not match the desired

output.

No need to apologise. It's easy to misread things.

The correct answer is
SELECT S.PointNumber, count(S.SurveyDate) as SurveyCount
FROM [Select Distinct PointNumber, SurveyDate from surveys]. as S
GROUP BY S.PointNumber;

Why couldn't I get that! I knew there was a simple single query answer to
this. And I knew it was something to do with DISTINCT. I'll get it next

time one of these comes up.

Cheers, Mike