Count Unique Values

The following query "SixDifinPackagedUniqueQuery", using
fields "JobNumber" and "UniqueLabelPack" from
table "SixDifinPackaged" gives me a list of unique carton
labels. I want to count these and use the results to tell
me the number of cartons packaged in a report. However,
when I try to use Count in the query it counts all the
Unique Labels in the table and not just the labels in the
query list. Since some of the labels are in the table more
than once I get a incorrect answer(836 Unique label values
when counted becomes 859). I have set the query for unique
values and 836 is the correct answer.

SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));


I tried placing the following in the On Print event of the
detail section of a report.
Dim CartonsPackaged as Single
CartonsPackaged = Nz(DCount
("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))
I get the following error
Run-time error '2001':
You canceled the previous operation.

Thank you in advance for your help.

Allan

Hi,



SELECT COUNT(*)
FROM (SELECT DISTINCT JobNumber FROM myTable) As a


would return the number of distinct JobNumber.


If you wish the number of distinct value by group, an easy solution is to
use a crosstab query (initial solution proposed by Steve Dassin):

TRANSFORM COUNT(*) As countTotal
SELECT myGroup, COUNT(countTotal) As CountDistinct
FROM myTable
GROUP BY myGroup
PIVOT JobNumber


would display the number of distinct JobNumber, for each Group. If you do
not want the various fields created by the crosstab, use, as example, in
Northwind:

TRANSFORM COUNT(*) As countCity
SELECT Customers.Country, COUNT(countCity) As DistinctCount, COUNT(*) As
TotalCount
FROM Customers
GROUP BY Country
PIVOT city IN( NULL)

Query1
Country DistinctCount TotalCount
Argentina 1 3

Austria 2 2

Belgium 2 2

Brazil 4 9

Canada 3 3

Denmark 2 2

Finland 2 2

France 9 11

Germany 11 11

Ireland 1 1

Italy 3 3

Mexico 1 5

Norway 1 1

Poland 1 1

Portugal 1 2

Spain 3 5

Sweden 2 2

Switzerland 2 2

UK 2 7

USA 12 13

Venezuela 4 4





Hoping it may help,
Vanderghast, Access MVP



"AHopper" wrote in message
...
The following query "SixDifinPackagedUniqueQuery", using
fields "JobNumber" and "UniqueLabelPack" from
table "SixDifinPackaged" gives me a list of unique carton
labels. I want to count these and use the results to tell
me the number of cartons packaged in a report. However,
when I try to use Count in the query it counts all the
Unique Labels in the table and not just the labels in the
query list. Since some of the labels are in the table more
than once I get a incorrect answer(836 Unique label values
when counted becomes 859). I have set the query for unique
values and 836 is the correct answer.

SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));


I tried placing the following in the On Print event of the
detail section of a report.
Dim CartonsPackaged as Single
CartonsPackaged = Nz(DCount
("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))
I get the following error
Run-time error '2001':
You canceled the previous operation.

Thank you in advance for your help.

Allan

Michel, thank you for your response. I don't think I
communicated clearly what I am trying to do. I will try
again.
In a table named "SixDifinPackaged" I have
fields "JobNumber" and "UniqueLabelPack". I want to count
the distinct "UniqueLabelPack" for each "JobNumber" and
use the result in a report. Some "UniqueLabelPack" are
used more than once so I only want them counted once.

How would I put the result in a report? I am using the On
Print event of the report to show other information about
each "JobNumber"

Thanks for your help
Allan
-----Original Message-----
Hi,



SELECT COUNT(*)
FROM (SELECT DISTINCT JobNumber FROM myTable) As a


would return the number of distinct JobNumber.


If you wish the number of distinct value by group, an
easy solution is to
use a crosstab query (initial solution proposed by Steve
Dassin):

TRANSFORM COUNT(*) As countTotal
SELECT myGroup, COUNT(countTotal) As CountDistinct
FROM myTable
GROUP BY myGroup
PIVOT JobNumber


would display the number of distinct JobNumber, for each
Group. If you do
not want the various fields created by the crosstab, use,
as example, in
Northwind:

TRANSFORM COUNT(*) As countCity
SELECT Customers.Country, COUNT(countCity) As
DistinctCount, COUNT(*) As
TotalCount
FROM Customers
GROUP BY Country
PIVOT city IN( NULL)

Query1
Country DistinctCount TotalCount
Argentina 1 3

Austria 2 2

Belgium 2 2

Brazil 4 9

Canada 3 3

Denmark 2 2

Finland 2 2

France 9 11

Germany 11 11

Ireland 1 1

Italy 3 3

Mexico 1 5

Norway 1 1

Poland 1 1

Portugal 1 2

Spain 3 5

Sweden 2 2

Switzerland 2 2

UK 2 7

USA 12 13

Venezuela 4 4





Hoping it may help,
Vanderghast, Access MVP



"AHopper" wrote in
message
...
The following query "SixDifinPackagedUniqueQuery", using
fields "JobNumber" and "UniqueLabelPack" from
table "SixDifinPackaged" gives me a list of unique
carton
labels. I want to count these and use the results to
tell
me the number of cartons packaged in a report. However,
when I try to use Count in the query it counts all the
Unique Labels in the table and not just the labels in
the
query list. Since some of the labels are in the table
more
than once I get a incorrect answer(836 Unique label
values
when counted becomes 859). I have set the query for
unique
values and 836 is the correct answer.

SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));


I tried placing the following in the On Print event of
the
detail section of a report.
Dim CartonsPackaged as Single
CartonsPackaged = Nz(DCount

("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))
I get the following error
Run-time error '2001':
You canceled the previous operation.

Thank you in advance for your help.

Allan





.

Hi,

1- Make the following query (cut and paste in a SQL view of a blank query,
in the query designer)


TRANSFORM COUNT(*) as TotalCount
SELECT JobNumber, COUNT(TotalCount) As DistinctCount
FROM SixDifinPackaged
GROUP BY JobNumber
PIVOT UniqueLabelPack IN(NULL);



2- Save the query.

3- Build the report based on the saved query, using the fields DistinctCount
and JobNumber (just forget the "diamond" field, [] ).



Note: if you ever use a parameter in a crosstab query, you HAVE TO declare
its datatype.


Hoping it may help,
Vanderghast, Access MVP


"AHopper" wrote in message
...
Michel, thank you for your response. I don't think I
communicated clearly what I am trying to do. I will try
again.
In a table named "SixDifinPackaged" I have
fields "JobNumber" and "UniqueLabelPack". I want to count
the distinct "UniqueLabelPack" for each "JobNumber" and
use the result in a report. Some "UniqueLabelPack" are
used more than once so I only want them counted once.

How would I put the result in a report? I am using the On
Print event of the report to show other information about
each "JobNumber"

Thanks for your help
Allan
-----Original Message-----
Hi,



SELECT COUNT(*)
FROM (SELECT DISTINCT JobNumber FROM myTable) As a


would return the number of distinct JobNumber.


If you wish the number of distinct value by group, an
easy solution is to
use a crosstab query (initial solution proposed by Steve
Dassin):

TRANSFORM COUNT(*) As countTotal
SELECT myGroup, COUNT(countTotal) As CountDistinct
FROM myTable
GROUP BY myGroup
PIVOT JobNumber


would display the number of distinct JobNumber, for each
Group. If you do
not want the various fields created by the crosstab, use,
as example, in
Northwind:

TRANSFORM COUNT(*) As countCity
SELECT Customers.Country, COUNT(countCity) As
DistinctCount, COUNT(*) As
TotalCount
FROM Customers
GROUP BY Country
PIVOT city IN( NULL)

Query1
Country DistinctCount TotalCount
Argentina 1 3

Austria 2 2

Belgium 2 2

Brazil 4 9

Canada 3 3

Denmark 2 2

Finland 2 2

France 9 11

Germany 11 11

Ireland 1 1

Italy 3 3

Mexico 1 5

Norway 1 1

Poland 1 1

Portugal 1 2

Spain 3 5

Sweden 2 2

Switzerland 2 2

UK 2 7

USA 12 13

Venezuela 4 4





Hoping it may help,
Vanderghast, Access MVP



"AHopper" wrote in
message
...
The following query "SixDifinPackagedUniqueQuery", using
fields "JobNumber" and "UniqueLabelPack" from
table "SixDifinPackaged" gives me a list of unique
carton
labels. I want to count these and use the results to
tell
me the number of cartons packaged in a report. However,
when I try to use Count in the query it counts all the
Unique Labels in the table and not just the labels in
the
query list. Since some of the labels are in the table
more
than once I get a incorrect answer(836 Unique label
values
when counted becomes 859). I have set the query for
unique
values and 836 is the correct answer.

SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));


I tried placing the following in the On Print event of
the
detail section of a report.
Dim CartonsPackaged as Single
CartonsPackaged = Nz(DCount

("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))
I get the following error
Run-time error '2001':
You canceled the previous operation.

Thank you in advance for your help.

Allan





.

Michel,
I appreciate very much your help. Your query worked.
The report I would like to put the information into has a
different table as it's source. I have tried adding the
query to the source for the report and creating a
relationship with "JobNumber", however that gave me the
wrong information.
Is there a way to insert this query SQL staement into the
On Print event of the "rptJobToDate" make the
report "JobNumber" the criteria and put
the "DistinctCount" in a text box on the report?
Do I need to add a subreport based on the query?

I know that SQL statements and queries are powerful tools
and that there is a lot more I need to learn about them.
Many things I would like to do are probably very easy (or
at least can be done) through these two tools. I am not
certain were to find information so I can grow in this
area.

Thank you for your patience.

-----Original Message-----
Hi,

1- Make the following query (cut and paste in a SQL view
of a blank query,
in the query designer)


TRANSFORM COUNT(*) as TotalCount
SELECT JobNumber, COUNT(TotalCount) As DistinctCount
FROM SixDifinPackaged
GROUP BY JobNumber
PIVOT UniqueLabelPack IN(NULL);



2- Save the query.

3- Build the report based on the saved query, using the
fields DistinctCount
and JobNumber (just forget the "diamond" field, [] ).



Note: if you ever use a parameter in a crosstab query,
you HAVE TO declare
its datatype.


Hoping it may help,
Vanderghast, Access MVP


"AHopper" wrote in
message
...
Michel, thank you for your response. I don't think I
communicated clearly what I am trying to do. I will try
again.
In a table named "SixDifinPackaged" I have
fields "JobNumber" and "UniqueLabelPack". I want to
count
the distinct "UniqueLabelPack" for each "JobNumber" and
use the result in a report. Some "UniqueLabelPack" are
used more than once so I only want them counted once.

How would I put the result in a report? I am using the
On
Print event of the report to show other information
about
each "JobNumber"

Thanks for your help
Allan
-----Original Message-----
Hi,



SELECT COUNT(*)
FROM (SELECT DISTINCT JobNumber FROM myTable) As a


would return the number of distinct JobNumber.


If you wish the number of distinct value by group, an
easy solution is to
use a crosstab query (initial solution proposed by
Steve
Dassin):

TRANSFORM COUNT(*) As countTotal
SELECT myGroup, COUNT(countTotal) As CountDistinct
FROM myTable
GROUP BY myGroup
PIVOT JobNumber


would display the number of distinct JobNumber, for
each
Group. If you do
not want the various fields created by the crosstab,
use,
as example, in
Northwind:

TRANSFORM COUNT(*) As countCity
SELECT Customers.Country, COUNT(countCity) As
DistinctCount, COUNT(*) As
TotalCount
FROM Customers
GROUP BY Country
PIVOT city IN( NULL)

Query1
Country DistinctCount TotalCount
Argentina 1 3

Austria 2 2

Belgium 2 2

Brazil 4 9

Canada 3 3

Denmark 2 2

Finland 2 2

France 9 11

Germany 11 11

Ireland 1 1

Italy 3 3

Mexico 1 5

Norway 1 1

Poland 1 1

Portugal 1 2

Spain 3 5

Sweden 2 2

Switzerland 2 2

UK 2 7

USA 12 13

Venezuela 4 4





Hoping it may help,
Vanderghast, Access MVP



"AHopper" wrote
in
message
...
The following query "SixDifinPackagedUniqueQuery",
using
fields "JobNumber" and "UniqueLabelPack" from
table "SixDifinPackaged" gives me a list of unique
carton
labels. I want to count these and use the results to
tell
me the number of cartons packaged in a report.
However,
when I try to use Count in the query it counts all
the
Unique Labels in the table and not just the labels in
the
query list. Since some of the labels are in the table
more
than once I get a incorrect answer(836 Unique label
values
when counted becomes 859). I have set the query for
unique
values and 836 is the correct answer.

SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));


I tried placing the following in the On Print event
of
the
detail section of a report.
Dim CartonsPackaged as Single
CartonsPackaged = Nz(DCount


("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))
I get the following error
Run-time error '2001':
You canceled the previous operation.

Thank you in advance for your help.

Allan





.



.

Hi,


If there is no immediate "join" that can lead you to "naturally" combine
(join) the new crosstab with the actual query/table you use in the actual
report, I would definitively go for a sub-report, that is exactly what I
would do, as you guess, right.

You also guess right in that SQL allows to easily perform powerful tasks
that would otherwise require many lines of codes (and validation), without
counting the reduce amount of required maintenance (code you don't write
don't have to be maintained), and the robustness we so gain in the process.
SQL is NOT a general language for programming, but it excels in what it is
intended to be applied.


About learning SQL, well, the basic can be obtain from "generic" books, one
which is closely compatible with Jet and MS SQL Server is "SQL Queries form
Mere Mortals", by Hernandez and Viescas, at Addison-Wesley. I would stay
away from Joe Celko's books, for introduction: they are too advanced, and
their syntax is quite alien to Jet...(and not up to date with its
possibilities) but to progress past the intermediate stage, they are among
those books I cannot fail to recommend. Sure, this ng, and Google in
general, are not completely rotten either.



Hoping it may help,
Vanderghast, Access MVP


"AHopper" wrote in message
...
Michel,
I appreciate very much your help. Your query worked.
The report I would like to put the information into has a
different table as it's source. I have tried adding the
query to the source for the report and creating a
relationship with "JobNumber", however that gave me the
wrong information.
Is there a way to insert this query SQL staement into the
On Print event of the "rptJobToDate" make the
report "JobNumber" the criteria and put
the "DistinctCount" in a text box on the report?
Do I need to add a subreport based on the query?

I know that SQL statements and queries are powerful tools
and that there is a lot more I need to learn about them.
Many things I would like to do are probably very easy (or
at least can be done) through these two tools. I am not
certain were to find information so I can grow in this
area.

Thank you for your patience.

-----Original Message-----
Hi,

1- Make the following query (cut and paste in a SQL view
of a blank query,
in the query designer)


TRANSFORM COUNT(*) as TotalCount
SELECT JobNumber, COUNT(TotalCount) As DistinctCount
FROM SixDifinPackaged
GROUP BY JobNumber
PIVOT UniqueLabelPack IN(NULL);



2- Save the query.

3- Build the report based on the saved query, using the
fields DistinctCount
and JobNumber (just forget the "diamond" field, [] ).



Note: if you ever use a parameter in a crosstab query,
you HAVE TO declare
its datatype.


Hoping it may help,
Vanderghast, Access MVP


"AHopper" wrote in
message
...
Michel, thank you for your response. I don't think I
communicated clearly what I am trying to do. I will try
again.
In a table named "SixDifinPackaged" I have
fields "JobNumber" and "UniqueLabelPack". I want to
count
the distinct "UniqueLabelPack" for each "JobNumber" and
use the result in a report. Some "UniqueLabelPack" are
used more than once so I only want them counted once.

How would I put the result in a report? I am using the
On
Print event of the report to show other information
about
each "JobNumber"

Thanks for your help
Allan
-----Original Message-----
Hi,



SELECT COUNT(*)
FROM (SELECT DISTINCT JobNumber FROM myTable) As a


would return the number of distinct JobNumber.


If you wish the number of distinct value by group, an
easy solution is to
use a crosstab query (initial solution proposed by
Steve
Dassin):

TRANSFORM COUNT(*) As countTotal
SELECT myGroup, COUNT(countTotal) As CountDistinct
FROM myTable
GROUP BY myGroup
PIVOT JobNumber


would display the number of distinct JobNumber, for
each
Group. If you do
not want the various fields created by the crosstab,
use,
as example, in
Northwind:

TRANSFORM COUNT(*) As countCity
SELECT Customers.Country, COUNT(countCity) As
DistinctCount, COUNT(*) As
TotalCount
FROM Customers
GROUP BY Country
PIVOT city IN( NULL)

Query1
Country DistinctCount TotalCount
Argentina 1 3

Austria 2 2

Belgium 2 2

Brazil 4 9

Canada 3 3

Denmark 2 2

Finland 2 2

France 9 11

Germany 11 11

Ireland 1 1

Italy 3 3

Mexico 1 5

Norway 1 1

Poland 1 1

Portugal 1 2

Spain 3 5

Sweden 2 2

Switzerland 2 2

UK 2 7

USA 12 13

Venezuela 4 4





Hoping it may help,
Vanderghast, Access MVP



"AHopper" wrote
in
message
...
The following query "SixDifinPackagedUniqueQuery",
using
fields "JobNumber" and "UniqueLabelPack" from
table "SixDifinPackaged" gives me a list of unique
carton
labels. I want to count these and use the results to
tell
me the number of cartons packaged in a report.
However,
when I try to use Count in the query it counts all
the
Unique Labels in the table and not just the labels in
the
query list. Since some of the labels are in the table
more
than once I get a incorrect answer(836 Unique label
values
when counted becomes 859). I have set the query for
unique
values and 836 is the correct answer.

SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));


I tried placing the following in the On Print event
of
the
detail section of a report.
Dim CartonsPackaged as Single
CartonsPackaged = Nz(DCount


("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))
I get the following error
Run-time error '2001':
You canceled the previous operation.

Thank you in advance for your help.

Allan





.



.

Michel, you are absolutely right about this ng being a
great place to learn.
Thank you
Allan

-----Original Message-----
Hi,


If there is no immediate "join" that can lead you
to "naturally" combine
(join) the new crosstab with the actual query/table you
use in the actual
report, I would definitively go for a sub-report, that is
exactly what I
would do, as you guess, right.

You also guess right in that SQL allows to easily perform
powerful tasks
that would otherwise require many lines of codes (and
validation), without
counting the reduce amount of required maintenance (code
you don't write
don't have to be maintained), and the robustness we so
gain in the process.
SQL is NOT a general language for programming, but it
excels in what it is
intended to be applied.


About learning SQL, well, the basic can be obtain
from "generic" books, one
which is closely compatible with Jet and MS SQL Server
is "SQL Queries form
Mere Mortals", by Hernandez and Viescas, at Addison-
Wesley. I would stay
away from Joe Celko's books, for introduction: they are
too advanced, and
their syntax is quite alien to Jet...(and not up to date
with its
possibilities) but to progress past the intermediate
stage, they are among
those books I cannot fail to recommend. Sure, this ng,
and Google in
general, are not completely rotten either.



Hoping it may help,
Vanderghast, Access MVP


"AHopper" wrote in
message
...
Michel,
I appreciate very much your help. Your query worked.
The report I would like to put the information into has
a
different table as it's source. I have tried adding the
query to the source for the report and creating a
relationship with "JobNumber", however that gave me the
wrong information.
Is there a way to insert this query SQL staement into
the
On Print event of the "rptJobToDate" make the
report "JobNumber" the criteria and put
the "DistinctCount" in a text box on the report?
Do I need to add a subreport based on the query?

I know that SQL statements and queries are powerful
tools
and that there is a lot more I need to learn about them.
Many things I would like to do are probably very easy
(or
at least can be done) through these two tools. I am not
certain were to find information so I can grow in this
area.

Thank you for your patience.

-----Original Message-----
Hi,

1- Make the following query (cut and paste in a SQL
view
of a blank query,
in the query designer)


TRANSFORM COUNT(*) as TotalCount
SELECT JobNumber, COUNT(TotalCount) As DistinctCount
FROM SixDifinPackaged
GROUP BY JobNumber
PIVOT UniqueLabelPack IN(NULL);



2- Save the query.

3- Build the report based on the saved query, using the
fields DistinctCount
and JobNumber (just forget the "diamond" field,
[] ).



Note: if you ever use a parameter in a crosstab query,
you HAVE TO declare
its datatype.


Hoping it may help,
Vanderghast, Access MVP


"AHopper" wrote
in
message
...
Michel, thank you for your response. I don't think I
communicated clearly what I am trying to do. I will
try
again.
In a table named "SixDifinPackaged" I have
fields "JobNumber" and "UniqueLabelPack". I want to
count
the distinct "UniqueLabelPack" for each "JobNumber"
and
use the result in a report. Some "UniqueLabelPack"
are
used more than once so I only want them counted once.

How would I put the result in a report? I am using
the
On
Print event of the report to show other information
about
each "JobNumber"

Thanks for your help
Allan
-----Original Message-----
Hi,



SELECT COUNT(*)
FROM (SELECT DISTINCT JobNumber FROM myTable) As a


would return the number of distinct JobNumber.


If you wish the number of distinct value by group,
an
easy solution is to
use a crosstab query (initial solution proposed by
Steve
Dassin):

TRANSFORM COUNT(*) As countTotal
SELECT myGroup, COUNT(countTotal) As CountDistinct
FROM myTable
GROUP BY myGroup
PIVOT JobNumber


would display the number of distinct JobNumber, for
each
Group. If you do
not want the various fields created by the crosstab,
use,
as example, in
Northwind:

TRANSFORM COUNT(*) As countCity
SELECT Customers.Country, COUNT(countCity) As
DistinctCount, COUNT(*) As
TotalCount
FROM Customers
GROUP BY Country
PIVOT city IN( NULL)

Query1
Country DistinctCount TotalCount
Argentina 1 3

Austria 2 2

Belgium 2 2

Brazil 4 9

Canada 3 3

Denmark 2 2

Finland 2 2

France 9 11

Germany 11 11

Ireland 1 1

Italy 3 3

Mexico 1 5

Norway 1 1

Poland 1 1

Portugal 1 2

Spain 3 5

Sweden 2 2

Switzerland 2 2

UK 2 7

USA 12 13

Venezuela 4 4





Hoping it may help,
Vanderghast, Access MVP



"AHopper"
wrote
in
message
...
The following query "SixDifinPackagedUniqueQuery",
using
fields "JobNumber" and "UniqueLabelPack" from
table "SixDifinPackaged" gives me a list of unique
carton
labels. I want to count these and use the results
to
tell
me the number of cartons packaged in a report.
However,
when I try to use Count in the query it counts all
the
Unique Labels in the table and not just the
labels in
the
query list. Since some of the labels are in the
table
more
than once I get a incorrect answer(836 Unique
label
values
when counted becomes 859). I have set the query
for
unique
values and 836 is the correct answer.

SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));


I tried placing the following in the On Print
event
of
the
detail section of a report.
Dim CartonsPackaged as Single
CartonsPackaged = Nz(DCount



("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))
I get the following error
Run-time error '2001':
You canceled the previous operation.

Thank you in advance for your help.

Allan





.



.



.