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]

Re: Another SQL/query question

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:vnpsmio9vv he1b@corp.super news.com...[color=blue]
> 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.
>
>
>[/color]

[HSalim]

Re: Another SQL/query question

ONE query is all you need


Select PointNumber, Count(*) from
YourTable
group by PointNumber


"dogwalker" <d@g.com> wrote in message
news:308fb.6842 $r.1040924@news 20.bellglobal.c om...[color=blue]
> first make a query that returns unique pointnumber, surveydate[/color]
combinations[color=blue]
> (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:vnpsmio9vv he1b@corp.super news.com...[color=green]
> > 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[/color][/color]
(ie.[color=blue][color=green]
> > how many different dates are in the table for each point). For the[/color][/color]
example[color=blue][color=green]
> > 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.
> >
> >
> >[/color]
>
>[/color]

[Mike MacSween]

Re: Another SQL/query question

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" <OnlyIfYouMust_ HSalim@msn.com> wrote in message
news:6Xjfb.2278 0$yU5.17854@nwr dny01.gnilink.n et...[color=blue]
> ONE query is all you need
>
>
> Select PointNumber, Count(*) from
> YourTable
> group by PointNumber
>
>
> "dogwalker" <d@g.com> wrote in message
> news:308fb.6842 $r.1040924@news 20.bellglobal.c om...[color=green]
> > first make a query that returns unique pointnumber, surveydate[/color]
> combinations[color=green]
> > (the 'unique values' query property will turn on 'select distinct')
> > then using that query, make a query that groups by pointnumber and[/color][/color]
counts[color=blue][color=green]
> > surveydate.
> >
> >
> > "DH" <dana_hartley@( remove)hotmail. com> wrote in message
> > news:vnpsmio9vv he1b@corp.super news.com...[color=darkred]
> > > Say I have a table with two columns that matter to this example,
> > > [PointNumber] and [SurveyDate]. And here is an example of what the[/color][/color][/color]
rows[color=blue][color=green][color=darkred]
> > > 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[/color][/color]
> (ie.[color=green][color=darkred]
> > > how many different dates are in the table for each point). For the[/color][/color]
> example[color=green][color=darkred]
> > > above, the query should output:
> > >
> > > [PointNumber], [NumberOfSurveys]
> > > 1,2
> > > 2,3
> > >
> > > I'd appreaciate any assistance. This seems like it would be simple but[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> > > don't know how to do it.
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[HSalim]

Re: Another SQL/query question

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" <mike.macsween. nospam@btintern et.com> wrote in message
news:3f7dce69$0 $15123$bed64819 @pubnews.gradwe ll.net...[color=blue]
> 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" <OnlyIfYouMust_ HSalim@msn.com> wrote in message
> news:6Xjfb.2278 0$yU5.17854@nwr dny01.gnilink.n et...[color=green]
> > ONE query is all you need
> >
> >
> > Select PointNumber, Count(*) from
> > YourTable
> > group by PointNumber
> >
> >
> > "dogwalker" <d@g.com> wrote in message
> > news:308fb.6842 $r.1040924@news 20.bellglobal.c om...[color=darkred]
> > > first make a query that returns unique pointnumber, surveydate[/color]
> > combinations[color=darkred]
> > > (the 'unique values' query property will turn on 'select distinct')
> > > then using that query, make a query that groups by pointnumber and[/color][/color]
> counts[color=green][color=darkred]
> > > surveydate.
> > >
> > >
> > > "DH" <dana_hartley@( remove)hotmail. com> wrote in message
> > > news:vnpsmio9vv he1b@corp.super news.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[/color][/color]
> rows[color=green][color=darkred]
> > > > 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[/color][/color][/color]
surveys[color=blue][color=green]
> > (ie.[color=darkred]
> > > > how many different dates are in the table for each point). For the[/color]
> > example[color=darkred]
> > > > above, the query should output:
> > > >
> > > > [PointNumber], [NumberOfSurveys]
> > > > 1,2
> > > > 2,3
> > > >
> > > > I'd appreaciate any assistance. This seems like it would be simple[/color][/color][/color]
but[color=blue]
> I[color=green][color=darkred]
> > > > don't know how to do it.
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Mike MacSween]

Re: Another SQL/query question

"HSalim" <OnlyIfYouMust_ HSalim@msn.com> wrote in message
news:Ilkfb.2290 5$yU5.15204@nwr dny01.gnilink.n et...[color=blue]
> Think again.
>
> The requirement was:
> the query should output:
> [PointNumber], [NumberOfSurveys]
> 1,2
> 2,3[/color]

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]

Re: Another SQL/query question

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.SurveyD ate) 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" <mike.macsween. nospam@btintern et.com> wrote in message
news:3f7ddfb0$0 $15124$bed64819 @pubnews.gradwe ll.net...[color=blue]
> "HSalim" <OnlyIfYouMust_ HSalim@msn.com> wrote in message
> news:Ilkfb.2290 5$yU5.15204@nwr dny01.gnilink.n et...[color=green]
> > Think again.
> >
> > The requirement was:
> > the query should output:
> > [PointNumber], [NumberOfSurveys]
> > 1,2
> > 2,3[/color]
>
> 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
>
>[/color]

[Mike MacSween]

Re: Another SQL/query question

"HSalim" <OnlyIfYouMust_ HSalim@msn.com> wrote in message
news:kvlfb.2199 1$541.6766@nwrd ny02.gnilink.ne t...[color=blue]
> 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[/color]
desired[color=blue]
> output.[/color]

No need to apologise. It's easy to misread things.
[color=blue]
> The correct answer is
> SELECT S.PointNumber, count(S.SurveyD ate) as SurveyCount
> FROM [Select Distinct PointNumber, SurveyDate from surveys]. as S
> GROUP BY S.PointNumber;[/color]

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]

Re: Another SQL/query question

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.PointNu mber,Count(DIST INCT surveys.SurveyD ate) AS
CountOfSurveyDa te
FROM surveys GROUP BY surveys.PointNu mber;

You may find it useful when microsoft gets around to fixing the problem.
see

http://support.microsoft.com/default.aspx?scid=kb;en-us;306250&Product=acc2002

HS


"Mike MacSween" <mike.macsween. nospam@btintern et.com> wrote in message
news:3f7de5e2$0 $15128$bed64819 @pubnews.gradwe ll.net...[color=blue]
> "HSalim" <OnlyIfYouMust_ HSalim@msn.com> wrote in message
> news:kvlfb.2199 1$541.6766@nwrd ny02.gnilink.ne t...[color=green]
> > 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[/color]
> desired[color=green]
> > output.[/color]
>
> No need to apologise. It's easy to misread things.
>[color=green]
> > The correct answer is
> > SELECT S.PointNumber, count(S.SurveyD ate) as SurveyCount
> > FROM [Select Distinct PointNumber, SurveyDate from surveys]. as S
> > GROUP BY S.PointNumber;[/color]
>
> 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[/color]
time[color=blue]
> one of these comes up.
>
> Cheers, Mike
>
>[/color]