Count records utilizing Where clause

  • Thread starter Thread starter REGREGL
  • Start date Start date
R

REGREGL

Hey all,

I have a table containing, lets say one hundred sales leads. Each of these
leads has a listing category. I want to count the number of sales leads for
each listing category in the table. There are more than a hundred listing
categories available. So if a listing category currently has no sales leads,
I want that listing categry to show 0 leads. There is also a master listing
category table to cross reference this to if necessary.

So, I want to be able to generate a report which shows:

Accounting/Bookkeeping: 3
Firearms/Gun Sales: 2
Book Stores: 4
Copier/Printing Services: 0

etc....

any ideas?
 
REGREGL said:
I have a table containing, lets say one hundred sales leads. Each of these
leads has a listing category. I want to count the number of sales leads for
each listing category in the table. There are more than a hundred listing
categories available. So if a listing category currently has no sales leads,
I want that listing categry to show 0 leads. There is also a master listing
category table to cross reference this to if necessary.

So, I want to be able to generate a report which shows:

Accounting/Bookkeeping: 3
Firearms/Gun Sales: 2
Book Stores: 4
Copier/Printing Services: 0
Click to expand...


SELECT [master listing category].[category name],
Count(*) As CountOfLeads
FROM [master listing category] LEFT JOIN [sales leads]
ON [master listing category].[category id] =
[sales leads].[category id]
GROUP BY [master listing category].[category name]
 
Thanks for the info.

I get an syntax error in JOIN operation.

Here is what I wrote:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [master category text]
ON [top categories].[master category text]=[Sales Leads].[master category
text]
GROUP BY [top categories].[master category text]

top categoreis=table with master list of sales lead categories
sales lead=table containing sales leads
master category text=field describing sales lead category within both top
categories and sales leads tables.




Marshall Barton said:
REGREGL said:
I have a table containing, lets say one hundred sales leads. Each of these
leads has a listing category. I want to count the number of sales leads for
each listing category in the table. There are more than a hundred listing
categories available. So if a listing category currently has no sales leads,
I want that listing categry to show 0 leads. There is also a master listing
category table to cross reference this to if necessary.

So, I want to be able to generate a report which shows:

Accounting/Bookkeeping: 3
Firearms/Gun Sales: 2
Book Stores: 4
Copier/Printing Services: 0
Click to expand...


SELECT [master listing category].[category name],
Count(*) As CountOfLeads
FROM [master listing category] LEFT JOIN [sales leads]
ON [master listing category].[category id] =
[sales leads].[category id]
GROUP BY [master listing category].[category name]
Click to expand...
 
REGREGL said:
I get an syntax error in JOIN operation.

Here is what I wrote:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [master category text]
ON [top categories].[master category text]=[Sales Leads].[master category
text]
GROUP BY [top categories].[master category text]

top categoreis=table with master list of sales lead categories
sales lead=table containing sales leads
master category text=field describing sales lead category within both top
categories and sales leads tables.
Click to expand...


The syntax error is because you replaced the names of a
table with a field name. I think this should be closer:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [Sales Leads]
ON [top categories].[master category text]=
[Sales Leads].[master category text]
GROUP BY [top categories].[master category text]
 
Thanks, that fixed the syntax error. Only problem I have now is that it is
assigning a value of 1 to all of the categories that do not have an entry on
the sales lead table. I need it to read 0 on those.

Marshall Barton said:
REGREGL said:
I get an syntax error in JOIN operation.

Here is what I wrote:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [master category text]
ON [top categories].[master category text]=[Sales Leads].[master category
text]
GROUP BY [top categories].[master category text]

top categoreis=table with master list of sales lead categories
sales lead=table containing sales leads
master category text=field describing sales lead category within both top
categories and sales leads tables.
Click to expand...


The syntax error is because you replaced the names of a
table with a field name. I think this should be closer:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [Sales Leads]
ON [top categories].[master category text]=
[Sales Leads].[master category text]
GROUP BY [top categories].[master category text]
Click to expand...
 
Sorry, the Count should be:

Count([Sales Leads].[master category text])
--
Marsh
MVP [MS Access]

Thanks, that fixed the syntax error. Only problem I have now is that it is
assigning a value of 1 to all of the categories that do not have an entry on
the sales lead table. I need it to read 0 on those.

Marshall Barton said:
REGREGL said:
I get an syntax error in JOIN operation.

Here is what I wrote:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [master category text]
ON [top categories].[master category text]=[Sales Leads].[master category
text]
GROUP BY [top categories].[master category text]

top categoreis=table with master list of sales lead categories
sales lead=table containing sales leads
master category text=field describing sales lead category within both top
categories and sales leads tables.
Click to expand...


The syntax error is because you replaced the names of a
table with a field name. I think this should be closer:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [Sales Leads]
ON [top categories].[master category text]=
[Sales Leads].[master category text]
GROUP BY [top categories].[master category text]
Click to expand...
Click to expand...
 
That works! Thanks alot!

Marshall Barton said:
Sorry, the Count should be:

Count([Sales Leads].[master category text])
--
Marsh
MVP [MS Access]

Thanks, that fixed the syntax error. Only problem I have now is that it is
assigning a value of 1 to all of the categories that do not have an entry on
the sales lead table. I need it to read 0 on those.

Marshall Barton said:
REGREGL wrote:
I get an syntax error in JOIN operation.

Here is what I wrote:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [master category text]
ON [top categories].[master category text]=[Sales Leads].[master category
text]
GROUP BY [top categories].[master category text]

top categoreis=table with master list of sales lead categories
sales lead=table containing sales leads
master category text=field describing sales lead category within both top
categories and sales leads tables.


The syntax error is because you replaced the names of a
table with a field name. I think this should be closer:

SELECT [top categories].[master category text],
Count(*) As CountOfLeads
FROM [top categories] LEFT JOIN [Sales Leads]
ON [top categories].[master category text]=
[Sales Leads].[master category text]
GROUP BY [top categories].[master category text]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top