Help with an SQL Statement plzzzzz

G

Guest

I really need help please in writing an SQL Statement. The example below is a
sample of the table I would like the sum of the count of volumes for each
name and the sum of the count of the repeated volumes for each name. The
first two tables are the sample and I will like to create two new tables call
total volumes by names (showing the sum of the count of the volumes per name)
and Total Repeat Volumes by Name (showing the sum of the count of the volumes
of the volumes that are duplicates per reps). I have added the result the
SQL Statement will produce. Any help will be great.
Thanks in advance.
Name Volumes
Mary 111
Tom 121
Tina 123
Mary 111
Tom 121
Tom 131

Name Total Volumes by name Total repeat Volumes by name
Mary 2 2
Tom 3 2
Tina 1 0
 
G

Guest

Hi Mary,

I've called the source table tbname. This sql statement create tb1 where you
will find "total volumes by name"

SELECT tbname.name, Count(tbname.name) AS [Total Volumes by name] INTO tb1
FROM tbname
GROUP BY tbname.name;

to run it from VBA do that:

docmd.setwarnings false ' to switch off access warnings
docmd.runsql("SELECT tbname.name, Count(tbname.name) AS [Total Volumes by
name] INTO tb1
FROM tbname
GROUP BY tbname.name") ' execute your sql
docmd.setwarnings true ' to switch on again access warnings

Here's the statement that create tb2 where you'll find Total repeat Volumes
by name

SELECT tbname.name, tbname.volumes, Count(tbname.volumes) AS [Total repeat
Volumes by name] INTO tb2
FROM tbname
GROUP BY tbname.name, tbname.volumes;

to run it from VBA same as above.

HTH Paolo
 
G

Guest

Thank you so much. i think i was not clear.

I have a table that has a set of records with phone number. Some of these
numbers appear once and some appear more than ones. I would like an sql
statement that count the sum of the phone number that appears more than once
only.
Name phone
May 8122321212
John 2143231212
Tina 4243131211
May 8122321212
John 2143231212
John 3123112332
Answer should be
Name total phone
May 2
John 2

Note Tina was not counted and phone# 3123112332 by john was not count. Any
help will be great.


Paolo said:
Hi Mary,

I've called the source table tbname. This sql statement create tb1 where you
will find "total volumes by name"

SELECT tbname.name, Count(tbname.name) AS [Total Volumes by name] INTO tb1
FROM tbname
GROUP BY tbname.name;

to run it from VBA do that:

docmd.setwarnings false ' to switch off access warnings
docmd.runsql("SELECT tbname.name, Count(tbname.name) AS [Total Volumes by
name] INTO tb1
FROM tbname
GROUP BY tbname.name") ' execute your sql
docmd.setwarnings true ' to switch on again access warnings

Here's the statement that create tb2 where you'll find Total repeat Volumes
by name

SELECT tbname.name, tbname.volumes, Count(tbname.volumes) AS [Total repeat
Volumes by name] INTO tb2
FROM tbname
GROUP BY tbname.name, tbname.volumes;

to run it from VBA same as above.

HTH Paolo

Mary said:
I really need help please in writing an SQL Statement. The example below is a
sample of the table I would like the sum of the count of volumes for each
name and the sum of the count of the repeated volumes for each name. The
first two tables are the sample and I will like to create two new tables call
total volumes by names (showing the sum of the count of the volumes per name)
and Total Repeat Volumes by Name (showing the sum of the count of the volumes
of the volumes that are duplicates per reps). I have added the result the
SQL Statement will produce. Any help will be great.
Thanks in advance.
Name Volumes
Mary 111
Tom 121
Tina 123
Mary 111
Tom 121
Tom 131

Name Total Volumes by name Total repeat Volumes by name
Mary 2 2
Tom 3 2
Tina 1 0
Click to expand...
Click to expand...
 
J

John Spencer

SELECT YourTable.[name], YourTable.[Phone]
, Count([phone]) as DupeCount
FROM YourTable
GROUP BY YourTable.[name], YourTable.[Phone]
HAVING Count([Phone]) > 1



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you so much. i think i was not clear.

I have a table that has a set of records with phone number. Some of these
numbers appear once and some appear more than ones. I would like an sql
statement that count the sum of the phone number that appears more than once
only.
Name phone
May 8122321212
John 2143231212
Tina 4243131211
May 8122321212
John 2143231212
John 3123112332
Answer should be
Name total phone
May 2
John 2

Note Tina was not counted and phone# 3123112332 by john was not count. Any
help will be great.


Paolo said:
Hi Mary,

I've called the source table tbname. This sql statement create tb1 where you
will find "total volumes by name"

SELECT tbname.name, Count(tbname.name) AS [Total Volumes by name] INTO tb1
FROM tbname
GROUP BY tbname.name;

to run it from VBA do that:

docmd.setwarnings false ' to switch off access warnings
docmd.runsql("SELECT tbname.name, Count(tbname.name) AS [Total Volumes by
name] INTO tb1
FROM tbname
GROUP BY tbname.name") ' execute your sql
docmd.setwarnings true ' to switch on again access warnings

Here's the statement that create tb2 where you'll find Total repeat Volumes
by name

SELECT tbname.name, tbname.volumes, Count(tbname.volumes) AS [Total repeat
Volumes by name] INTO tb2
FROM tbname
GROUP BY tbname.name, tbname.volumes;

to run it from VBA same as above.

HTH Paolo

Mary said:
I really need help please in writing an SQL Statement. The example below is a
sample of the table I would like the sum of the count of volumes for each
name and the sum of the count of the repeated volumes for each name. The
first two tables are the sample and I will like to create two new tables call
total volumes by names (showing the sum of the count of the volumes per name)
and Total Repeat Volumes by Name (showing the sum of the count of the volumes
of the volumes that are duplicates per reps). I have added the result the
SQL Statement will produce. Any help will be great.
Thanks in advance.
Name Volumes
Mary 111
Tom 121
Tina 123
Mary 111
Tom 121
Tom 131

Name Total Volumes by name Total repeat Volumes by name
Mary 2 2
Tom 3 2
Tina 1 0
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi again,

if you add this statement at the end of the SQL I posted yesterday I think
you'll reach your goal

HAVING (((Count(tbname.name))>1));

HTH Paolo

Mary said:
Thank you so much. i think i was not clear.

I have a table that has a set of records with phone number. Some of these
numbers appear once and some appear more than ones. I would like an sql
statement that count the sum of the phone number that appears more than once
only.
Name phone
May 8122321212
John 2143231212
Tina 4243131211
May 8122321212
John 2143231212
John 3123112332
Answer should be
Name total phone
May 2
John 2

Note Tina was not counted and phone# 3123112332 by john was not count. Any
help will be great.


Paolo said:
Hi Mary,

I've called the source table tbname. This sql statement create tb1 where you
will find "total volumes by name"

SELECT tbname.name, Count(tbname.name) AS [Total Volumes by name] INTO tb1
FROM tbname
GROUP BY tbname.name;

to run it from VBA do that:

docmd.setwarnings false ' to switch off access warnings
docmd.runsql("SELECT tbname.name, Count(tbname.name) AS [Total Volumes by
name] INTO tb1
FROM tbname
GROUP BY tbname.name") ' execute your sql
docmd.setwarnings true ' to switch on again access warnings

Here's the statement that create tb2 where you'll find Total repeat Volumes
by name

SELECT tbname.name, tbname.volumes, Count(tbname.volumes) AS [Total repeat
Volumes by name] INTO tb2
FROM tbname
GROUP BY tbname.name, tbname.volumes;

to run it from VBA same as above.

HTH Paolo

Mary said:
I really need help please in writing an SQL Statement. The example below is a
sample of the table I would like the sum of the count of volumes for each
name and the sum of the count of the repeated volumes for each name. The
first two tables are the sample and I will like to create two new tables call
total volumes by names (showing the sum of the count of the volumes per name)
and Total Repeat Volumes by Name (showing the sum of the count of the volumes
of the volumes that are duplicates per reps). I have added the result the
SQL Statement will produce. Any help will be great.
Thanks in advance.
Name Volumes
Mary 111
Tom 121
Tina 123
Mary 111
Tom 121
Tom 131

Name Total Volumes by name Total repeat Volumes by name
Mary 2 2
Tom 3 2
Tina 1 0
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Syntax in macro with & " 4
brackets in a sql statement 3
Help with a couple sql of queries 0
SQL statement with LIKE need to show Numeric paceholders 1
Union query not displaying select statement 1
countif 6
Interesting query trick...expandable to a crosstab? 5
WorkSheet Change Event - Need Help With Coding Syntax 2

Top