By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Help with simple querry please!

P: n/a
OK, I'm having a brain freeze.

I have a table like this:

Office Name Phone
----------------------------------
SG Larry 555-1212
SG Moe 553-4444
SG Curly 666-8888
PO Ren 222-9999
PO Stimpy 555-8888

and I want to a querry that produces this:

Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 3
SG Moe 553-4444 3
SG Curly 666-8888 3
PO Ren 222-9999 2
PO Stimpy 555-8888 2
I try this:

SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name, Phone

and I get
Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 1
SG Moe 553-4444 1
SG Curly 666-8888 1
PO Ren 222-9999 1
PO Stimpy 555-8888 1
What am I missing or doing wrong?

Thanks!

Pete

Aug 8 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Hi,
SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name, Phone
I guess you should't be grouping by "Phone", since that makes all
records unique again. Try:

SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name

I do hope this helps...

Regards,

Onno

Aug 8 '06 #2

P: n/a
Won't work. I get an error when the Group BY line does not match the
Select By line.
Both have to have the same fields.

Pete

on****@gmail.com wrote:
Hi,
SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name, Phone

I guess you should't be grouping by "Phone", since that makes all
records unique again. Try:

SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name

I do hope this helps...

Regards,

Onno
Aug 8 '06 #3

P: n/a
On 8 Aug 2006 02:26:11 -0700, "Clownfish" <cl*********@yahoo.com>
wrote:

Name is a reserved word. Don't use it for field names. Change it to
CustomerName, EmployeeName or whatever, and try again. Your SQL is
fine.

-Tom.
>OK, I'm having a brain freeze.

I have a table like this:

Office Name Phone
----------------------------------
SG Larry 555-1212
SG Moe 553-4444
SG Curly 666-8888
PO Ren 222-9999
PO Stimpy 555-8888

and I want to a querry that produces this:

Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 3
SG Moe 553-4444 3
SG Curly 666-8888 3
PO Ren 222-9999 2
PO Stimpy 555-8888 2
I try this:

SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name, Phone

and I get
Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 1
SG Moe 553-4444 1
SG Curly 666-8888 1
PO Ren 222-9999 1
PO Stimpy 555-8888 1
What am I missing or doing wrong?

Thanks!

Pete
Aug 8 '06 #4

P: n/a
Nope.. still no go.

I tried this TABLE1

Office StaffName Arrive
------------------------------------------------------
CO N/A 8/7/2006
PO Hamster 8/20/2006
PO Monkey 8/20/2006
SG Trout 7/20/2006
SG Bass 8/20/2006
SG N/A 8/20/2006
SG Bass 8/7/2006

SELECT Table1.Office, Table1.StaffName, Table1.Arrive,
Count(Table1.Office) AS CountOfOffice
FROM Table1
GROUP BY Table1.Office, Table1.StaffName, Table1.Arrive;

And I still get all ones in the counted field.

Office StaffName Arrive
CountOfOffice
----------------------------------------------------------------------------------------------
CO N/A 8/7/2006
1
PO Hamster 8/20/2006
1
PO Monkey 8/20/2006
1
SG Trout 7/20/2006
1
SG Bass 8/20/2006
1
SG N/A 8/20/2006
1
SG Bass 8/7/2006
1

I am probably doing this all wrong. Let me describe what I really need
and then perhaps someone can show me how dumb I have been.

My GOAL is to create a report that would print the following:
Office StaffName Arrive
------------------------------------------------------
CO NO CHANGES 8/7/2006
PO Hamster 8/20/2006
PO Monkey 8/20/2006
SG Trout 7/20/2006
SG Bass 8/20/2006
SG Bass 8/20/2006

The querry would print all records EXCEPT the 6th one, because the 6th
record has the text "N/A" in [StaffName] AND there are more than 1
record in it's "SG" group [Office]

I would like a statement that works like this:

Select all records EXCEPT those with ( "N/A" in [StaffName] AND
Count(Office) >1 )

Any ideas?

Thanks guys! (and gals!)

Peter

Tom van Stiphout wrote:
On 8 Aug 2006 02:26:11 -0700, "Clownfish" <cl*********@yahoo.com>
wrote:

Name is a reserved word. Don't use it for field names. Change it to
CustomerName, EmployeeName or whatever, and try again. Your SQL is
fine.

-Tom.
OK, I'm having a brain freeze.

I have a table like this:

Office Name Phone
----------------------------------
SG Larry 555-1212
SG Moe 553-4444
SG Curly 666-8888
PO Ren 222-9999
PO Stimpy 555-8888

and I want to a querry that produces this:

Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 3
SG Moe 553-4444 3
SG Curly 666-8888 3
PO Ren 222-9999 2
PO Stimpy 555-8888 2
I try this:

SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name, Phone

and I get
Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 1
SG Moe 553-4444 1
SG Curly 666-8888 1
PO Ren 222-9999 1
PO Stimpy 555-8888 1
What am I missing or doing wrong?

Thanks!

Pete
Aug 8 '06 #5

P: n/a
"Clownfish" <cl*********@yahoo.comwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:
OK, I'm having a brain freeze.

I have a table like this:

Office Name Phone
----------------------------------
SG Larry 555-1212
SG Moe 553-4444
SG Curly 666-8888
PO Ren 222-9999
PO Stimpy 555-8888

and I want to a querry that produces this:

Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 3
SG Moe 553-4444 3
SG Curly 666-8888 3
PO Ren 222-9999 2
PO Stimpy 555-8888 2
I try this:

SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name, Phone

and I get
Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 1
SG Moe 553-4444 1
SG Curly 666-8888 1
PO Ren 222-9999 1
PO Stimpy 555-8888 1
What am I missing or doing wrong?

Thanks!

Pete

What you want is incorrect.... 3 relates only to Office SG, not
to office and name and phone.2 relates to office PO not to
office + name + phone.

But, you can do it with 2 queries. And if you are ambitious,
you can merge the two queries into one querydef.

Query1
Select Office, count("*") as OfficeCount from table Group By
Office;

Query2
Select table.office,table.name,table.phone,Query1.OfficeC ount
from table inner join query1 on table.office = query1.office;

The whole works in 1 query:
Select table.office,table.name,table.phone,(SELECT count("*") as
X from table alias Y where Y.office = Z.office) as OfficeCount
from table alias Z;

It's a pain to write and debug a query in a query, so stick with
the two query system for a few years.

Not that if you try to run any of these queries, Access will
complain about using reserved words (table, name)

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 8 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.