Help with simple querry please!

[Clownfish]

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

[onnodb]

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

[Clownfish]

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.co m 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

[Tom van Stiphout]

On 8 Aug 2006 02:26:11 -0700, "Clownfish" <cl*********@ya hoo.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

[Clownfish]

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.StaffNam e, Table1.Arrive,
Count(Table1.Of fice) AS CountOfOffice
FROM Table1
GROUP BY Table1.Office, Table1.StaffNam e, 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*********@ya hoo.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

[Bob Quintal]

"Clownfish" <cl*********@ya hoo.comwrote in
news:11******** **************@ b28g2000cwb.goo glegroups.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,ta ble.name,table. phone,Query1.Of ficeCount
from table inner join query1 on table.office = query1.office;

The whole works in 1 query:
Select table.office,ta ble.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