DCount

[A Hopper]

I understand how to do a count of records in the field of
a table however, I would like to count the records and
count duplicated numbers only once. For example if the
table has numbers 1 to 45 and the numbers 15, 27, 35 occur
more than one time I want them to be counted only once.

Thanks in advance for your help

Allan

 

[John Vinson]

I understand how to do a count of records in the field of
a table however, I would like to count the records and
count duplicated numbers only once. For example if the
table has numbers 1 to 45 and the numbers 15, 27, 35 occur
more than one time I want them to be counted only once.

You'll need to create a Query based on the table, selecting only this
field; set this query's Unique Values property to True. Then base your
DCount on the Query.

 

[A Hopper]

John
After your response I realized I had left out some
important information. The table contains fields that I
need to include for criteria and then once the criteria
are met I want to count the unique records.

"JobNumber" and "SkidNumber" are the two fields needing to
have criteria from the form. This will give me the numbers
in the field "Label" that I want to count.

I have created a query and set the Unique Values to True.
I set the criteria for JobNumber and SkidNumber to match
the JobNumber and SkidNumber on the open form. The "Job
Number" and "Skid Number" are repeating in many records,
and there are only unique numbers in the "Label" field. I
don't understand why this is happening, however, if I can
rely on this to be true all the time, it will work.

Is there anything I am overlooking that might cause a
problem using the query with the extra fields?

Tnanks for your time and help.

Allan

 

[A Hopper]

John
SQL View of the Query
SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));


JobNumber UniqueLabelPack
0188 369500
0188 369501
0188 369502
0188 369503
0188 369504
0188 369505
0188 369506
0188 369507
0188 369508

 

[A Hopper]

John
SQL View of query:
SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));
Query is set to Unique Value

Sample information:
JobNumber UniqueLabelPack
0188 369500
0188 369501
0188 369502
0188 369503
0188 369504
0188 369505
0188 369506
0188 369507
0188 369508

In Form OnOpen event
Dim CartonsPackaged As Integer
CartonsPackaged = Nz(DCount
("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))

I don't understand why JobNumber is allowing repeat of the
same number in the query (which is OK for what I want to
do) and although there are repeat numbers in
UniqyeLabelPack they are being filtered out (again this is
what I want). I want to make certain that the
UniqueLabelPack will always include all unique numbers
with no duplicates, as it is now.

Thanks
Allan

 

[John Vinson]

I don't understand why JobNumber is allowing repeat of the
same number in the query (which is OK for what I want to
do)

well... because they are THERE. Setting the Unique Values property on
a query ensures that there will be no duplicates of *the entire set of
selected fields*. Even though the jobnumber repeats, the
UniqueLabelPack doesn't; none of the records you show duplicate any of
the other records you show. Maybe I don't understand what you would
find an acceptable result! Just one record for the job number, or
what?

and although there are repeat numbers in
UniqyeLabelPack they are being filtered out (again this is
what I want). I want to make certain that the
UniqueLabelPack will always include all unique numbers
with no duplicates, as it is now.

It SOUNDS like the query is returning what you want: a count of the
distinct labelpack values for a given jobnumber. Is the result you are
getting incorrect? If so, what are you getting for the DCount, and
what would be the correct value?

 

[A Hopper]

John
You have answered my question. I want unique records and
the JobNumber can repeat. I thought the query under
certain conditions might only allow the JobNumber once and
then I would get the wrong result. I was looking at Unique
Value as each field and not the whole record. I do get the
correct response in my DCount.

Thanks for your time and your responses.
Allan