Skip printing selected records

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Skip printing selected records

I have an access report that prints a count of how many times a company name
appears in the table. Example

Walmart 4
Target 5
Kmart 1

I am using the =count(*) to add the number of times the company appears and
this works fine. But, I only need to print the companies that are greater
than 1.

Can I use the >1 with the count function to selectively print only companies
that appear more times the one. And how do I write the Controlsource
=count(*)>1?????

Any help appreciated
 
Mike said:
Skip printing selected records

I have an access report that prints a count of how many times a company
name
appears in the table. Example

Walmart 4
Target 5
Kmart 1

I am using the =count(*) to add the number of times the company appears
and
this works fine. But, I only need to print the companies that are greater
than 1.

Can I use the >1 with the count function to selectively print only
companies
that appear more times the one. And how do I write the Controlsource
=count(*)>1?????

Any help appreciated
Click to expand...


I'm not sure if this is what you're after, but normally you would just set
the report's recordsource to a query that returns only companies with counts
greater than one. Something like this simple query:

SELECT CompanyName, Count(*) As Occurrences
FROM Companies
GROUP BY CompanyName
HAVING Count(*) > 1
 
Thanks Dirk,,,,

I am a beginner at this. I assume this goes in the controlsource of the
Count field on the report.

I tried it and it prompts me to enter a parameter. When I enter Customer,
the count appears with the word "Customer" on the report.

Do I need to put the code somewhere other than the controlsource?
 
Mike said:
Thanks Dirk,,,,

I am a beginner at this. I assume this goes in the controlsource of the
Count field on the report.
Click to expand...

No. What I gave you was an example of a possible RecordSource for the
report. Since I don't know what your table looks like and I don't know what
report is trying to show, it's very hard for me to be more specific. What
is your table name? What fields do you want to show on your report?
 
The table name is 0609PblmData sorted by Customer. The data fields on the
report are:

Call Center, Customer (name), Total (text) Count (calls made by the
Customer. So it looks like this:


Call Center Customer Calls
CCS Walmart Total 5

Hope that helps...........


Mike
 
Mike said:
The table name is 0609PblmData sorted by Customer. The data fields on the
report are:

Call Center, Customer (name), Total (text) Count (calls made by the
Customer. So it looks like this:


Call Center Customer Calls
CCS Walmart Total 5
Click to expand...


Try setting your report's Record Source property (on the Data tab of the
report's property sheet in design view) to this SQL statement:

SELECT [Call Center], Customer, Count(*) As Calls
FROM 0609PblmData
GROUP BY [Call Center], Customer
HAVING Count(*) > 1

Note: I've broken that onto multiple lines for clarity, but when you enter
it into the Record Source property, it should all be on one line, rather
like this:

SELECT [Call Center], Customer, Count(*) As Calls FROM 0609PblmData
GROUP BY [Call Center], Customer HAVING Count(*) > 1

(except that the above will have been broken onto multiple lines by the
newsreader).

Then have three text boxes on the report with their Control Source
properties set to [Call Center], Customer, and Calls, respectively.
 
Great I will give it a try in few minutes, have different fire to put out
now...I will let you know...thanks again
--
Mike


Dirk Goldgar said:
Mike said:
The table name is 0609PblmData sorted by Customer. The data fields on the
report are:

Call Center, Customer (name), Total (text) Count (calls made by the
Customer. So it looks like this:


Call Center Customer Calls
CCS Walmart Total 5
Click to expand...


Try setting your report's Record Source property (on the Data tab of the
report's property sheet in design view) to this SQL statement:

SELECT [Call Center], Customer, Count(*) As Calls
FROM 0609PblmData
GROUP BY [Call Center], Customer
HAVING Count(*) > 1

Note: I've broken that onto multiple lines for clarity, but when you enter
it into the Record Source property, it should all be on one line, rather
like this:

SELECT [Call Center], Customer, Count(*) As Calls FROM 0609PblmData
GROUP BY [Call Center], Customer HAVING Count(*) > 1

(except that the above will have been broken onto multiple lines by the
newsreader).

Then have three text boxes on the report with their Control Source
properties set to [Call Center], Customer, and Calls, respectively.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Dirk,

What you suggested made great progress. I now have a report that only prints
customers that called in 2 or more times and I also figured out how to select
a range of the number of calls example >2<26.

But one problem when the report prints, the count is always 1. I need the
count to be the number of times the customer called in. Do I need another
count field on the report?
--
Mike


Mike said:
Great I will give it a try in few minutes, have different fire to put out
now...I will let you know...thanks again
--
Mike


Dirk Goldgar said:
Mike said:
The table name is 0609PblmData sorted by Customer. The data fields on the
report are:

Call Center, Customer (name), Total (text) Count (calls made by the
Customer. So it looks like this:


Call Center Customer Calls
CCS Walmart Total 5
Click to expand...


Try setting your report's Record Source property (on the Data tab of the
report's property sheet in design view) to this SQL statement:

SELECT [Call Center], Customer, Count(*) As Calls
FROM 0609PblmData
GROUP BY [Call Center], Customer
HAVING Count(*) > 1

Note: I've broken that onto multiple lines for clarity, but when you enter
it into the Record Source property, it should all be on one line, rather
like this:

SELECT [Call Center], Customer, Count(*) As Calls FROM 0609PblmData
GROUP BY [Call Center], Customer HAVING Count(*) > 1

(except that the above will have been broken onto multiple lines by the
newsreader).

Then have three text boxes on the report with their Control Source
properties set to [Call Center], Customer, and Calls, respectively.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Mike said:
Dirk,

What you suggested made great progress. I now have a report that only
prints
customers that called in 2 or more times and I also figured out how to
select
a range of the number of calls example >2<26.

But one problem when the report prints, the count is always 1. I need the
count to be the number of times the customer called in. Do I need another
count field on the report?
Click to expand...

The recordsource query I gave you included the count of calls as the
calculated field "Calls". That is based on the understanding that each
record in your source table represents 1 call. Unless I've misunderstood,
there is no reason for yuo to be using any Count() expression on your
report. Just bind a text box to the Calls field.
 
Thanks Dirk, perfect got it to work. I have learned a lot from you in a short
time.

How do I include in the query to select a range, lets say customers that
have calls greater than 1 and less than 26. I have tried >1<26, and tried >1
AND <26 but I get an error.

Thanks
 
Mike said:
Thanks Dirk, perfect got it to work. I have learned a lot from you in a
short
time.

How do I include in the query to select a range, lets say customers that
have calls greater than 1 and less than 26. I have tried >1<26, and tried
AND <26 but I get an error.
Click to expand...


You want to specify this in the query itself? The hard-coded way would be
to use this for the query's SQL:

SELECT [Call Center], Customer, Count(*) As Calls
FROM 0609PblmData
GROUP BY [Call Center], Customer
HAVING Count(*) > 1 AND Count(*) < 26

Or, since the count can only be a whole numbers, you could do this:

SELECT [Call Center], Customer, Count(*) As Calls
FROM 0609PblmData
GROUP BY [Call Center], Customer
HAVING Count(*) Between 2 AND 25
 
Dirk, thanks again, it is working. I thought of the AND but did not include
the COUNT(*) after the AND and before the 26...

You have been great...

As I progress forward I am sure I will have more questions. I am taking a
class this October, I am an old COBOL program so much of the way queries are
written I can understand after I am shown how to configure them..

Thanks
--
Mike


Dirk Goldgar said:
Mike said:
Thanks Dirk, perfect got it to work. I have learned a lot from you in a
short
time.

How do I include in the query to select a range, lets say customers that
have calls greater than 1 and less than 26. I have tried >1<26, and tried
AND <26 but I get an error.
Click to expand...


You want to specify this in the query itself? The hard-coded way would be
to use this for the query's SQL:

SELECT [Call Center], Customer, Count(*) As Calls
FROM 0609PblmData
GROUP BY [Call Center], Customer
HAVING Count(*) > 1 AND Count(*) < 26

Or, since the count can only be a whole numbers, you could do this:

SELECT [Call Center], Customer, Count(*) As Calls
FROM 0609PblmData
GROUP BY [Call Center], Customer
HAVING Count(*) Between 2 AND 25


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Mike said:
As I progress forward I am sure I will have more questions. I am taking a
class this October, I am an old COBOL program so much of the way queries
are
written I can understand after I am shown how to configure them..
Click to expand...

I put in about 20 years on COBOL myself (along with about a dozen other
languages), so I know where you're coming from.

You may find Crystal Long's Access tutorials helpful:

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access
 
Back
Top