Distinct Record Counts

[Guest]

Okay, I give up.

Access is the only DB that I can't seem to giev me a Distinctive Count of
unique Sales Numbers.

I have a Sales DB that writes to an Inventory table. Now, I have a query &
report that gives me accurate inventory data for a given period, but I'll be
darn if I can get a count of distinct invoices for the same period. I've
tried to do this in the query (a couple of different ways), in my report,
etc. but the total either fails or gives me a zero.

Help please! Where am I going wrong?

MDM

 

[Guest]

It can be done in a query and in a report.
For a query, make it a Totals query (Click on the tool bar icon that looks
like the Greek letter Sigma), Include the period field, Invoice field, and
another occurance of the Invoice field.

In the Total row, select Group by for the period and the first occurance of
the Invoice. select Count for the second occurance of the Invoice field.

In a report, the Sorting and Grouping

 

[Guest]

Klatuu

Your posting broke off below, so I cannot see your other comment about
reports. However, yes, I have the query setp as you wrote but the total comes
out zero when I use Distinct.

Keep in mind that I have a many to one relationship. Many inventory records
to a single sales order, so if I do not use a distinctive count I wind up
getting what I'm getting now which is a total os all inventory records
instead of a total of all sales records.

I hope you post your second thought here as I would like to know how I can
achieve this inside the report itself. Now it's becomming personal.

Thanks,

MDM

 

[Gary Walter]

Okay, I give up.

Access is the only DB that I can't seem to giev me a Distinctive Count of
unique Sales Numbers.

I have a Sales DB that writes to an Inventory table. Now, I have a query &
report that gives me accurate inventory data for a given period, but I'll
be
darn if I can get a count of distinct invoices for the same period. I've
tried to do this in the query (a couple of different ways), in my report,
etc. but the total either fails or gives me a zero.

if you have a named query for sales
over the given period (say "qrySales"),
then you have several options...

SELECT
Count(*) As DistinctInvoiceCnt
FROM
[SELECT
InvoiceID
FROM
qrySales
GROUP BY
InvoiceID]. AS q;

or, save an additional query (say "qryDistinctInv")

SELECT
InvoiceID
FROM
qrySales
GROUP BY
InvoiceID;

in report, textbox source

=DCount("*","qryDistinctInv")

 

[Guest]

That was all of my post. I was not aware of the relationship, but I think if
you change the first occurance of invoice to sales order, and change what I
was calling invoice to inventory, you may get what you want.

As to the sorting and grouping, if you are reporting for all periods, set up
a group for the period, one for the sales order, and one for inventory (don't
know how I translated that to invoice). Then you can put a text box in the
group footer for the inventory with a count.

 

[Guest]

Thanks Gary. I tried doing this several various ways, but always get #Error
or 0 in the total field on the report (depending on what I change in the
query).

I finally took baby steps and built three queries each narrowing down data
to the final number I'm looking for: 64.

I know I called these Sales and Inventory, but this was for simplicity of
explanation. The table is Inventory Transactions. The data pulled is from the
Home Survey table and equipment sold. The equipment sold totals work fine,
but I just cannot get the total number of surveys performed that resulted in
the inventory sold.

I now get this number using the three quieries (each calling the prior).
However, when I place this final query in my Source
(=getSurveyNo![CountOfHome Survey No]) I still get #Error and can't figure
out why. When I run the query (getSurveyNo) directly I get 64.

How can I get this number to show in the report?

MDM

Okay, I give up.

Access is the only DB that I can't seem to giev me a Distinctive Count of
unique Sales Numbers.

I have a Sales DB that writes to an Inventory table. Now, I have a query &
report that gives me accurate inventory data for a given period, but I'll
be
darn if I can get a count of distinct invoices for the same period. I've
tried to do this in the query (a couple of different ways), in my report,
etc. but the total either fails or gives me a zero.

if you have a named query for sales
over the given period (say "qrySales"),
then you have several options...

SELECT
Count(*) As DistinctInvoiceCnt
FROM
[SELECT
InvoiceID
FROM
qrySales
GROUP BY
InvoiceID]. AS q;

or, save an additional query (say "qryDistinctInv")

SELECT
InvoiceID
FROM
qrySales
GROUP BY
InvoiceID;

in report, textbox source

=DCount("*","qryDistinctInv")

 

[Gary Walter]

you have a "final" query that gives you this
single distinct count (64)...and it is named "getSurveyNo"

and the field that contains that number in this
query is [CountOfHome Survey No]"

and in your report you have a textbox that you
wish to display that count, and its source is

=DLookUp("CountOfHome Survey No", "getSurveyNo")

or (if need brackets in domain function? I cannot remember)

=DLookUp("[CountOfHome Survey No]", "getSurveyNo")

Thanks Gary. I tried doing this several various ways, but always get
#Error
or 0 in the total field on the report (depending on what I change in the
query).

I finally took baby steps and built three queries each narrowing down data
to the final number I'm looking for: 64.

I know I called these Sales and Inventory, but this was for simplicity of
explanation. The table is Inventory Transactions. The data pulled is from
the
Home Survey table and equipment sold. The equipment sold totals work fine,
but I just cannot get the total number of surveys performed that resulted
in
the inventory sold.

I now get this number using the three quieries (each calling the prior).
However, when I place this final query in my Source
(=getSurveyNo![CountOfHome Survey No]) I still get #Error and can't figure
out why. When I run the query (getSurveyNo) directly I get 64.

How can I get this number to show in the report?

MDM

Okay, I give up.

Access is the only DB that I can't seem to giev me a Distinctive Count
of
unique Sales Numbers.

I have a Sales DB that writes to an Inventory table. Now, I have a
query &
report that gives me accurate inventory data for a given period, but
I'll
be
darn if I can get a count of distinct invoices for the same period.
I've
tried to do this in the query (a couple of different ways), in my
report,
etc. but the total either fails or gives me a zero.

if you have a named query for sales
over the given period (say "qrySales"),
then you have several options...

SELECT
Count(*) As DistinctInvoiceCnt
FROM
[SELECT
InvoiceID
FROM
qrySales
GROUP BY
InvoiceID]. AS q;

or, save an additional query (say "qryDistinctInv")

SELECT
InvoiceID
FROM
qrySales
GROUP BY
InvoiceID;

in report, textbox source

=DCount("*","qryDistinctInv")