If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping
Hello,
A question. If I have a table like this: 777 555 1111, 7/1/2008, 1, 333 777 555 1111, 6/1/2008, 0, 444 777 555 1111, 7/1/2008, 1, 555 I can group on telephone number (first field) and Date (second field) if date = 7/1/2008 and sum on third and fourth field without a problem. But can I get the number of records with the same telephone number? For example a result that looks like: 777 555 1111(group by), 2(count date), 2 (sum field 3), 888( sum field 4), and a new field with the result 3 representing the number of records with the same telephone number(group by number) ?? If someone could give me a suggestion or point me in the right direction I would appreciate it. Thank you in advance. Greg |
#2
|
|||
|
|||
Grouping
In a separate query.
SELECT PhoneNumber, Count(PhoneNumber) as CountRecordswithThisPhone FROM YourTable GROUP BY PhoneNumber John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Greg wrote: Hello, A question. If I have a table like this: 777 555 1111, 7/1/2008, 1, 333 777 555 1111, 6/1/2008, 0, 444 777 555 1111, 7/1/2008, 1, 555 I can group on telephone number (first field) and Date (second field) if date = 7/1/2008 and sum on third and fourth field without a problem. But can I get the number of records with the same telephone number? For example a result that looks like: 777 555 1111(group by), 2(count date), 2 (sum field 3), 888( sum field 4), and a new field with the result 3 representing the number of records with the same telephone number(group by number) ?? If someone could give me a suggestion or point me in the right direction I would appreciate it. Thank you in advance. Greg |
#3
|
|||
|
|||
Grouping
John,
Thank you, I will give it a try! Greg "John Spencer" wrote in message ... In a separate query. SELECT PhoneNumber, Count(PhoneNumber) as CountRecordswithThisPhone FROM YourTable GROUP BY PhoneNumber John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Greg wrote: Hello, A question. If I have a table like this: 777 555 1111, 7/1/2008, 1, 333 777 555 1111, 6/1/2008, 0, 444 777 555 1111, 7/1/2008, 1, 555 I can group on telephone number (first field) and Date (second field) if date = 7/1/2008 and sum on third and fourth field without a problem. But can I get the number of records with the same telephone number? For example a result that looks like: 777 555 1111(group by), 2(count date), 2 (sum field 3), 888( sum field 4), and a new field with the result 3 representing the number of records with the same telephone number(group by number) ?? If someone could give me a suggestion or point me in the right direction I would appreciate it. Thank you in advance. Greg |
Thread Tools | |
Display Modes | |
|
|