count report column

[Guest]

I have a report that has 6 columns I have sorted and counted records just
fine. I am having difficulty just geeting the count of unique entries in just
1 column.
=Count([Company Name]) I figured this would return how many recors in this
column.

Now when I counted the records I hid the duplicates so my reurned data

Company Name
record 1
record 2
you get the idea
now I understand that if i counted the total(of company name column) the
above would be 3. What I want to do is have it retur a count of 1 for each
unique company name then add it up so I can display the total number of
unique company names.

TIA
David

 

[Guest]

Hi David,

You can have a running count displayed next to the company name. To do this,
create a Company Name group with a group header (View > Sorting and Grouping
in report design view). Move your company name field to this header. Add an
unbound textbox in this header. Set it's Control Source to: =1 on the data
tab of the properties listing. Set Running Sum = "Over All". When you now
run the report, you should see an incrementing number next to each company
name.

If this is not to your liking, you can use an unbound textbox in the report
footer section (View > Report Header/Footer). Use a DCount function in the
control source of this textbox, as follows:

=DCount("CompanyName","tblCustomers")

This will give you a count of all CompanyNames in a table named
tblCustomers. You may need to add an optional criteria statement, which
matches any criteria applied to the report's recordsource. An example of
using criteria with the DLookup function can be found here (the criteria for
DCount would be similar):

http://www.mvps.org/access/general/gen0018.htm

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a report that has 6 columns I have sorted and counted records just
fine. I am having difficulty just geeting the count of unique entries in just
1 column.
=Count([Company Name]) I figured this would return how many recors in this
column.

Now when I counted the records I hid the duplicates so my reurned data

Company Name
record 1
record 2
you get the idea
now I understand that if i counted the total(of company name column) the
above would be 3. What I want to do is have it retur a count of 1 for each
unique company name then add it up so I can display the total number of
unique company names.

TIA
David

 

[Arvin Meyer [MVP]]

Access doesn't support the ANSI-standard Count(DISTINCT ..). You
have to do a SELECT DISTINCT first, then count that in a second query.
----------------- Air Code -------------------
qryDistinctValues
SELECT DISTINCT ID, SomeOtherColumn
FROM MyTable;

SELECT Something, Count(SomeOtherColumn)
FROM Staff LEFT JOIN qryDistinctValues ON Staff.SomeOtherColumn =
qryDistinctValues.SomeOtherColumn
GROUP BY Something;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

One way to do this is in code in the report's module. First add an unbound
text box txtCompanyCount to the report footer. Then put code in the report's
module to count the individual company names at runtime. So the report's
module would look like this:

'''''module begins'''''
Option Compare Database
Option Explicit

' declare module level variables to hold list of companies and
' count of distinct compny names
Dim strCompanyList As String, lngCompanyCount As Long

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

' initialise variables to a zero length string and zero
strCompanylist = ""
lngCompanyCount = 0

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

' if company name is not already in list then
' add it and increment count of companies.
If InStr(strCompanyList, Me.[Company Name]) = 0 Then
strCompanyList = Me.[Company Name] & "~" & strCompanyList
lngCompanyCount = lngCompanyCount + 1
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

' assign value of variable to text box
Me.txtCompanyCount = lngCompanyCount

End Sub
'''''module ends'''''

If you want the count to be within groups in the report rather than over the
report as a whole put the txtCompanyCount control in a group footer,
initialise the variables in the same group's header's Format event procedure
rather than the report header's, and assign the value to the control in the
group footer's event rather than the report footer's.

 

[Guest]

Ok maybe this is simpler than i thought
In the report footer I put an unbound text box with a control of
=[DealerCount]
That number totals correctly.
Now how do I get that to display in the Report header? The user wants this
number as soon as the report opens.

 

[Guest]

Hello Tom,

Your advice helps a bit, but I am not able to get the "Criteria" part of the
code (used in the link you posted) to work. I would like to only count
records that have "Pending" listed under my Reservation Status [ResvStatus]
field. How can I narrow down the count so it includes a select number of
records rather than all of the records? Thank you.

Here is what I am using so far:
DCount("ResvStatus" , "[MVC Schedule]")

This counts all of my records. I went to the link you posted but the
examples did not help me narrow down the count.

Thanks,
Diana

 

[Guest]

Hi Diana,

Try this, if you want to use a hard-coded value of Pending:

DCount("ResvStatus" , "[MVC Schedule]", "ResvStatus = 'Pending'")

Note: For clarity, that's a single apostrophe before and after the word
Pending with a closing double quote: ' Pending ' " )

If you want to use a string value that is available in a form (ie. not
hard-coded to one value), then use this form for the criteria portion:

"ResvStatus = '" & [Forms]![NameOfForm]![NameOfField] & "'")

For clarity, that's = ' " & [Forms]![NameOfForm]![NameOfField] & " ' " )


Tom
__________________________________________

:

Hello Tom,

Your advice helps a bit, but I am not able to get the "Criteria" part of the
code (used in the link you posted) to work. I would like to only count
records that have "Pending" listed under my Reservation Status [ResvStatus]
field. How can I narrow down the count so it includes a select number of
records rather than all of the records? Thank you.

Here is what I am using so far:
DCount("ResvStatus" , "[MVC Schedule]")

This counts all of my records. I went to the link you posted but the
examples did not help me narrow down the count.

Thanks,
Diana
__________________________________________

:

Hi David,

You can have a running count displayed next to the company name. To do this,
create a Company Name group with a group header (View > Sorting and Grouping
in report design view). Move your company name field to this header. Add an
unbound textbox in this header. Set it's Control Source to: =1 on the data
tab of the properties listing. Set Running Sum = "Over All". When you now
run the report, you should see an incrementing number next to each company
name.

If this is not to your liking, you can use an unbound textbox in the report
footer section (View > Report Header/Footer). Use a DCount function in the
control source of this textbox, as follows:

=DCount("CompanyName","tblCustomers")

This will give you a count of all CompanyNames in a table named
tblCustomers. You may need to add an optional criteria statement, which
matches any criteria applied to the report's recordsource. An example of
using criteria with the DLookup function can be found here (the criteria for
DCount would be similar):

http://www.mvps.org/access/general/gen0018.htm

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a report that has 6 columns I have sorted and counted records just
fine. I am having difficulty just geeting the count of unique entries in just
1 column.
=Count([Company Name]) I figured this would return how many recors in this
column.

Now when I counted the records I hid the duplicates so my reurned data

Company Name
record 1
record 2
you get the idea
now I understand that if i counted the total(of company name column) the
above would be 3. What I want to do is have it retur a count of 1 for each
unique company name then add it up so I can display the total number of
unique company names.

TIA
David

 

[Guest]

Hi David,

Place a text box in the report header section. Set the control source for
this textbox as follows:

=[NameOfTextboxInReportFooter]


Tom
__________________________________________

:

Ok maybe this is simpler than i thought
In the report footer I put an unbound text box with a control of
=[DealerCount]
That number totals correctly.
Now how do I get that to display in the Report header? The user wants this
number as soon as the report opens.

__________________________________________

:

Hi David,

You can have a running count displayed next to the company name. To do this,
create a Company Name group with a group header (View > Sorting and Grouping
in report design view). Move your company name field to this header. Add an
unbound textbox in this header. Set it's Control Source to: =1 on the data
tab of the properties listing. Set Running Sum = "Over All". When you now
run the report, you should see an incrementing number next to each company
name.

If this is not to your liking, you can use an unbound textbox in the report
footer section (View > Report Header/Footer). Use a DCount function in the
control source of this textbox, as follows:

=DCount("CompanyName","tblCustomers")

This will give you a count of all CompanyNames in a table named
tblCustomers. You may need to add an optional criteria statement, which
matches any criteria applied to the report's recordsource. An example of
using criteria with the DLookup function can be found here (the criteria for
DCount would be similar):

http://www.mvps.org/access/general/gen0018.htm

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a report that has 6 columns I have sorted and counted records just
fine. I am having difficulty just geeting the count of unique entries in just
1 column.
=Count([Company Name]) I figured this would return how many recors in this
column.

Now when I counted the records I hid the duplicates so my reurned data

Company Name
record 1
record 2
you get the idea
now I understand that if i counted the total(of company name column) the
above would be 3. What I want to do is have it retur a count of 1 for each
unique company name then add it up so I can display the total number of
unique company names.

TIA
David

 

[Guest]

It just returns 1 and the textbox in the footer returns he correct number of 50

 

[Guest]

Thanks, that works perfectly!

Hi Diana,

Try this, if you want to use a hard-coded value of Pending:

DCount("ResvStatus" , "[MVC Schedule]", "ResvStatus = 'Pending'")

Note: For clarity, that's a single apostrophe before and after the word
Pending with a closing double quote: ' Pending ' " )

If you want to use a string value that is available in a form (ie. not
hard-coded to one value), then use this form for the criteria portion:

"ResvStatus = '" & [Forms]![NameOfForm]![NameOfField] & "'")

For clarity, that's = ' " & [Forms]![NameOfForm]![NameOfField] & " ' " )


Tom
__________________________________________

:

Hello Tom,

Your advice helps a bit, but I am not able to get the "Criteria" part of the
code (used in the link you posted) to work. I would like to only count
records that have "Pending" listed under my Reservation Status [ResvStatus]
field. How can I narrow down the count so it includes a select number of
records rather than all of the records? Thank you.

Here is what I am using so far:
DCount("ResvStatus" , "[MVC Schedule]")

This counts all of my records. I went to the link you posted but the
examples did not help me narrow down the count.

Thanks,
Diana
__________________________________________

:

Hi David,

You can have a running count displayed next to the company name. To do this,
create a Company Name group with a group header (View > Sorting and Grouping
in report design view). Move your company name field to this header. Add an
unbound textbox in this header. Set it's Control Source to: =1 on the data
tab of the properties listing. Set Running Sum = "Over All". When you now
run the report, you should see an incrementing number next to each company
name.

If this is not to your liking, you can use an unbound textbox in the report
footer section (View > Report Header/Footer). Use a DCount function in the
control source of this textbox, as follows:

=DCount("CompanyName","tblCustomers")

This will give you a count of all CompanyNames in a table named
tblCustomers. You may need to add an optional criteria statement, which
matches any criteria applied to the report's recordsource. An example of
using criteria with the DLookup function can be found here (the criteria for
DCount would be similar):

http://www.mvps.org/access/general/gen0018.htm

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a report that has 6 columns I have sorted and counted records just
fine. I am having difficulty just geeting the count of unique entries in just
1 column.
=Count([Company Name]) I figured this would return how many recors in this
column.

Now when I counted the records I hid the duplicates so my reurned data

Company Name
record 1
record 2
you get the idea
now I understand that if i counted the total(of company name column) the
above would be 3. What I want to do is have it retur a count of 1 for each
unique company name then add it up so I can display the total number of
unique company names.

TIA
David

 

[Guest]

Hi Tom,

Do you also know how to write the code for more than one criterion? I tried
the following:

DCount("ResvStatus" , "[MVC Schedule]", "ResvStatus = 'Bkd'", "[CC Charged]
= '0'")

but the Access program tells me I have too many arguments. I am trying to
count records that meet two criteria. Both fields (ResvStatus and [CC
Charged]) are from the same table.

Thanks,
Diana

Hi Diana,

Try this, if you want to use a hard-coded value of Pending:

DCount("ResvStatus" , "[MVC Schedule]", "ResvStatus = 'Pending'")

Note: For clarity, that's a single apostrophe before and after the word
Pending with a closing double quote: ' Pending ' " )

If you want to use a string value that is available in a form (ie. not
hard-coded to one value), then use this form for the criteria portion:

"ResvStatus = '" & [Forms]![NameOfForm]![NameOfField] & "'")

For clarity, that's = ' " & [Forms]![NameOfForm]![NameOfField] & " ' " )


Tom
__________________________________________

:

Hello Tom,

Your advice helps a bit, but I am not able to get the "Criteria" part of the
code (used in the link you posted) to work. I would like to only count
records that have "Pending" listed under my Reservation Status [ResvStatus]
field. How can I narrow down the count so it includes a select number of
records rather than all of the records? Thank you.

Here is what I am using so far:
DCount("ResvStatus" , "[MVC Schedule]")

This counts all of my records. I went to the link you posted but the
examples did not help me narrow down the count.

Thanks,
Diana
__________________________________________

:

Hi David,

You can have a running count displayed next to the company name. To do this,
create a Company Name group with a group header (View > Sorting and Grouping
in report design view). Move your company name field to this header. Add an
unbound textbox in this header. Set it's Control Source to: =1 on the data
tab of the properties listing. Set Running Sum = "Over All". When you now
run the report, you should see an incrementing number next to each company
name.

If this is not to your liking, you can use an unbound textbox in the report
footer section (View > Report Header/Footer). Use a DCount function in the
control source of this textbox, as follows:

=DCount("CompanyName","tblCustomers")

This will give you a count of all CompanyNames in a table named
tblCustomers. You may need to add an optional criteria statement, which
matches any criteria applied to the report's recordsource. An example of
using criteria with the DLookup function can be found here (the criteria for
DCount would be similar):

http://www.mvps.org/access/general/gen0018.htm

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a report that has 6 columns I have sorted and counted records just
fine. I am having difficulty just geeting the count of unique entries in just
1 column.
=Count([Company Name]) I figured this would return how many recors in this
column.

Now when I counted the records I hid the duplicates so my reurned data

Company Name
record 1
record 2
you get the idea
now I understand that if i counted the total(of company name column) the
above would be 3. What I want to do is have it retur a count of 1 for each
unique company name then add it up so I can display the total number of
unique company names.

TIA
David

 

[Guest]

David,

I'm not sure what's going on with your database. I had first tested the
advise I gave, using the Northwind sample database, and I was able to display
the number of companies in the report header without a problem.

I'm willing to take a look at your report, if you can zip your database and
send it to me.

Tom
QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
__________________________________________

:

It just returns 1 and the textbox in the footer returns he correct number of
50
__________________________________________

:

Hi David,

Place a text box in the report header section. Set the control source for
this textbox as follows:

=[NameOfTextboxInReportFooter]


Tom
__________________________________________

:

Ok maybe this is simpler than i thought
In the report footer I put an unbound text box with a control of
=[DealerCount]
That number totals correctly.
Now how do I get that to display in the Report header? The user wants this
number as soon as the report opens.

__________________________________________

:

Hi David,

You can have a running count displayed next to the company name. To do this,
create a Company Name group with a group header (View > Sorting and Grouping
in report design view). Move your company name field to this header. Add an
unbound textbox in this header. Set it's Control Source to: =1 on the data
tab of the properties listing. Set Running Sum = "Over All". When you now
run the report, you should see an incrementing number next to each company
name.

If this is not to your liking, you can use an unbound textbox in the report
footer section (View > Report Header/Footer). Use a DCount function in the
control source of this textbox, as follows:

=DCount("CompanyName","tblCustomers")

This will give you a count of all CompanyNames in a table named
tblCustomers. You may need to add an optional criteria statement, which
matches any criteria applied to the report's recordsource. An example of
using criteria with the DLookup function can be found here (the criteria for
DCount would be similar):

http://www.mvps.org/access/general/gen0018.htm

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a report that has 6 columns I have sorted and counted records just
fine. I am having difficulty just geeting the count of unique entries in just
1 column.
=Count([Company Name]) I figured this would return how many recors in this
column.

Now when I counted the records I hid the duplicates so my reurned data

Company Name
record 1
record 2
you get the idea
now I understand that if i counted the total(of company name column) the
above would be 3. What I want to do is have it retur a count of 1 for each
unique company name then add it up so I can display the total number of
unique company names.

TIA
David

 

[Guest]

Is the datatype for the CC Charged field text or numeric? By wrapping the 0
with single apostrophes, you are telling Access that this is a text value. If
this field is numeric, do not use the single apostrophes. Also, you cannot
separate multiple criteria with a comma, since the comma is intrepreted by
Access as the separator for the various terms: expression, domain & criteria.

Try one of the following forms

If the CC Charged field is a text datatype:
DCount("ResvStatus" , "[MVC Schedule]", "ResvStatus = 'Bkd' AND [CC Charged]
= '0'")

If the CC Charged field is a numeric datatype:
DCount("ResvStatus" , "[MVC Schedule]", "ResvStatus = 'Bkd' AND [CC Charged]
= 0")


Tom
___________________________________________

:

Hi Tom,

Do you also know how to write the code for more than one criterion? I tried
the following:

DCount("ResvStatus" , "[MVC Schedule]", "ResvStatus = 'Bkd'", "[CC Charged]
= '0'")

but the Access program tells me I have too many arguments. I am trying to
count records that meet two criteria. Both fields (ResvStatus and [CC
Charged]) are from the same table.

Thanks,
Diana

 

[Guest]

Works very well. Thanks!

Diana

 

[Guest]

You're welcome. Thanks for reporting back that it is working for you now.

Tom
__________________________________

:

Works very well. Thanks!