• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

MS Access Reports, Count values only once.

In a report column that contains multiple values. Some are the same, some is are not. Is there a formula that would only count values only once ?

For example
Pallet
P76
P76
S67

I need a count total that only shows 2.
0
jumpy262000
Asked:
jumpy262000
  • 12
  • 6
  • 5
  • +2
2 Solutions
 
mbizupCommented:
Try setting the control source of the txtTotal textbox to:

= DCount("*", "YourTableOrQuery","Pallet IN (SELECT DISTINCT Pallet From YourTableOrQUery)")
0
 
jumpy262000Author Commented:
Never seen a formula written like that. I placed:

=DCount("*","qryPalletTalley","Pallet IN (SELECT DISTINCT Pallet From qryPalletTalley)")

in the txtbox file of the report, received an error display.
0
 
mbizupCommented:
Hmm.  That wont work.  Use a vba Function

Function DistinctCount() as long
     Dim rs as DAO.recordset
     set rs = currentdb.openrecordset("SELECT DISTINCT Pallet From YourTableOrQUery")
     if rs.recordcount = 0 then
          distinctcount = 0
          goto exitfn
     end if
     rs.movelast
     distinctcount = rs.recordcount
exitfn:
     rs.close
     set rs = nothing
end function

Then use that function in a textbox's control source like this (include the = sign):

= DistinctCount
     
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
mbizupCommented:
That function should be placed in a standard module.
0
 
Patrick MatthewsCommented:
You could also avoid using a UDF by using straight SQL.  Put this as the controlsource
for an unbound textbox:

=(SELECT Count(*) FROM (SELECT DISTINCT FieldName FROM SomeTable))
0
 
jumpy262000Author Commented:
To call the function in the report. Should it look like =(DistinctCount([txtpallet]))

"txtPallet" is the control name that I'm trying to count.
0
 
mbizupCommented:
no parameters...

= DistinctCount()
0
 
jumpy262000Author Commented:
In the control line I entered =DistinctCount([txtpallet])

When I ran the report, the report whats me to enter a parameter value for DistinctCount.
0
 
mbizupCommented:
Leave the parameter out.... The Pallet field is included in the function's code.  All you need is the function name with empty parentheses, like this:

= DistinctCount()
0
 
Patrick MatthewsCommented:
jumpy262000,

Just curious if you had tried my suggestion.  The SQL is simplistic, as your data are probably
coming from a query, but it shows another way of doing it, perhaps...

Regards,

Patrick
0
 
mbizupCommented:
Patrick,

The SQL is  more concise that what I used in the function I posted, but I think it still needs to be enclosed in a function, unless there is some way (like a variation on DLookup) to extract the count from the SQL.
0
 
jumpy262000Author Commented:
Sorry had to set out of the cube.

Patrick,
When I tried the expression, I received a "Name" value placed in the text box

=(SELECT Count(*) FROM (SELECT DISTINCT txtPallet FROM qryPalletTalley))

txtPallet holds the value
qryPalletTalley is the query.

When I tried the Function DistinctCount()    
 
I received an error run-time error 3061
Too few parameters Expected 2.
for Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT txtPallet From qryPalletTalley")

I do appreciate all suggestions and also try them. I  learn more and helps me understand access better. The wonderful and most frustrating thing about Access is that there are so many solutions that can be found for a single problem, but then having to choose the most appropriate one.
0
 
mbizupCommented:
You need to use the name of the actual Field (not the name of the textbox) in the SQL statements.
(in other words, the field that txtPallet is bound to through it's control source)
0
 
jumpy262000Author Commented:
"txtPallet" is the filed name. On the report textbox I do have the control showing =DistinctCount().
In the module, I pressed the F5 to run the function. What's when the error showed.

When I placed the cursor on the highlighted line, it shows: rs value equals nothing .  
0
 
aesmikeCommented:
Patrick, out of curiosity, have you tried this yourself?
It doesn't work in any examples I tried--which is too bad because this would be great if you could do this in-line SQL type stuff.  Does anyone have any 1st hand experience in making this work?

BTW, Nobody asked but is this Total a report total or a group total?
If it's a group total, the other approach might be to put some code in the OnFormat event of the group.  The code is essentially mbizup's approach using a recordset variable but with criteria that restricts to totalling the the group
0
 
mbizupCommented:
What is the SQL for qryPalletTalley?
0
 
jumpy262000Author Commented:
SELECT tblMainTable.txtReferenceNumber,
tblMainTable.YesNoAccured, IIf(nz([datDateShipped],0)=0,"Missing",
[datDateShipped]) AS ShipDate,
tblMainTable.txtVendorNumber,
tblMainTable.txtVendor,
 tblMainTable.datDate,
tblMainTable.txtModelNumber,
tblMainTable.txtSKU,
tblMainTable.txtDescription,
tblMainTable.NumQty,
tblMainTable.CurCost,
tblMainTable.CurRetail,
tblMainTable.txtPallet,
([CurCost]*[NumQty]) AS TotalCost,
([CurRetail]*[NumQty]) AS TotalRetail,
tblMainTable.txtLocation,
tblMainTable.datDateFaxed,
tblMainTable.txtRA,
tblMainTable.datRAAssignedDate,
tblMainTable.datDateShipped,
tblMainTable.txtBOL,
tblMainTable.txtStatus,
tblMainTable.txtDeptNumber,
tblMainTable.YesNoAccured
FROM tblMainTable
GROUP BY tblMainTable.txtReferenceNumber,
 tblMainTable.YesNoAccured,
IIf(nz([datDateShipped],0)=0,"Missing",[datDateShipped]),
 tblMainTable.txtVendorNumber,
tblMainTable.txtVendor,
tblMainTable.datDate,
 tblMainTable.txtModelNumber,
 tblMainTable.txtSKU,
tblMainTable.txtDescription,
tblMainTable.NumQty,
tblMainTable.CurCost,
tblMainTable.CurRetail,
tblMainTable.txtPallet,
([CurCost]*[NumQty]), ([CurRetail]*[NumQty]),
tblMainTable.txtLocation,
tblMainTable.datDateFaxed,
 tblMainTable.txtRA,
tblMainTable.datRAAssignedDate,
tblMainTable.datDateShipped,
tblMainTable.txtBOL,
tblMainTable.txtStatus,
tblMainTable.txtDeptNumber,
tblMainTable.YesNoAccured

HAVING (((tblMainTable.YesNoAccured)=0) AND ((tblMainTable.txtVendor) Like IIf([Forms]![frmPalletTallySheet]![cboVendor] Is Null,"*",[Forms]![frmPalletTallySheet]![cboVendor] & "*")) AND ((tblMainTable.txtPallet) Like IIf([Forms]![frmPalletTallySheet]![strPallet] Is Null,"*",[Forms]![frmPalletTallySheet]![strPallet] & "*")) AND ((tblMainTable.txtDeptNumber)<>"SL"));
0
 
Patrick MatthewsCommented:
aesmike said:
>>Patrick, out of curiosity, have you tried this yourself?

This particular one, no.  I have used simple SELECT queries as the control source for
unbound text boxes, but not with subqueries.

:)
0
 
mbizupCommented:
Well... I *did* ask. :-)
The "too few parameters" message is caused by the references to form controls in the SQL you posted, which are not understood in the VBA recordset.

You may be able to work around this by creating a new query to count the distinct records.  I suggest using Patrick's SQL:

SELECT Count(*) as C FROM (SELECT DISTINCT txtPallet FROM qryPalletTalley

Save this as qryCount

Then revise my VBA code like this:

Function DistinctCount() as long
     Dim rs as DAO.recordset
     set rs = currentdb.openrecordset("SELECT * From qryCount")
     distinctcount = rs!C
exitfn:
     rs.close
     set rs = nothing
end function

Your textbox control source would be the same:

= DistinctCount()

Please note that I'm borrowing Patrick's suggestion here, and award points accordingly if this helps.
0
 
mbizupCommented:
Sorry... I dropped a parenthesis in the SQL for qryCount.  Corrected:

SELECT Count(*) as C FROM (SELECT DISTINCT txtPallet FROM qryPalletTalley)
0
 
aesmikeCommented:
My apologies, mbizup, if you asked it, I didn't see it in the above.

Patrick, so you're saying that you can set a text box's controlsource property equal to a select statement?  I assume that the select statement has to return only one row.  What's the syntax for this?  This goes under the "you learn something new every day" category for me.  I have a particular report that just "screams" for this technique and would love to know how.
0
 
mbizupCommented:
Mike, no problem... I posted "I *did* ask" because of the size of the query suprised me, not because of any of your comments.  Sorry for the confusion.  :-0

>"you learn something new every day"
SQL in a control source without a wrapper function is new to me too!
0
 
aesmikeCommented:
well, so far it ain't happening for me.  I'm hoping Patrick can shed some light on this but so far I'm going to consider this myth "Busted" until proven otherwise.
0
 
aesmikeCommented:
Mbizup, IMOHO, me thinks you should have gotten all the points as the only solution that works!
0
 
mbizupCommented:
Mike,
My code depended on Patrick's SQL used in a stored query.
Plus, I still haven't found a restaurant that will let me cash in points for a steak dinner.

To clarify that, I think the "Accepted comments" should have been:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22716903.html#19556173
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22716903.html#19557098
0
 
Patrick MatthewsCommented:
Mike and Miriam,

I could have sworn I've done this before.  I am looking for an example now.  You might see
a retraction coming :)

Patrick
0
 
Patrick MatthewsCommented:
OK, a retraction, and an embarassing one at that.  My memory was faulty; for some
reason my mind conflated setting the row source of a listbox or combobox using an
inline SQL statement, which is allowed, with setting the control source for an
unbound textbox using an inline SQL statement, which apparently is not.

Sorry for wasting everyone's time :)

Patrick
0
 
aesmikeCommented:
bummer.  I was hoping this could be done.
0
 
Leigh PurvisDatabase DeveloperCommented:
Hi all.
(I know - I arrive at parties late... or I would do, if I ever went to any these days :-S)

Rather than going back through everything I'll just make a couple of comments if I may.
First, for a variation on the theme of Miriam's first answer...

=DCount("*", "qryPalletTalley","PKField IN (SELECT Min(PKField) From qryPalletTalley GROUP BY Pallet)")
Assumes you have a Primary Key field.  But there's really no excuse for ever *not* having one. ;-)
(That won't be the fastest expression in the world I'd wager - but it can certainly be done).

And an approximation to Patrick's direct SQL statement (which - as you've found doesn't work alone, but he may have been thinking back to something like:)
=CurrentDb.OpenRecordset("SELECT Count(*) FROM (SELECT DISTINCT Pallet FROM qryPalletTalley)")(0)

Which is just using an object in the expression to return the recordset value directly and would be faster.
But that isn't particularly the sort of thing you'd have in a real world application.
Generally you would maintain your own generic recordset opening evaluation function and just call that.
Of course - if there were form references as parameters in the query then the direct evaluation would be scuppered - and you're then locked into either forming your query to evaluate the parameters for you - or have your generic evaluating function use a querydef object.
(We've all seen that kind of thing before...

Function fGenericRstEval(strSQL As String)

    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter
    Dim strTemp As String
   
    strTemp = CStr(Now)
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(strTemp, strSQL)
   
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next

    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
   
    If rst.RecordCount > 0 Then
        fGenericRstEval = rst.Fields(0)
    End If
   
    db.QueryDefs.Delete strTemp
   
    rst.Close
    Set prm = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
   
End Function

Giving us once again
=fGenericRstEval("SELECT Count(*) FROM (SELECT DISTINCT Pallet FROM qryPalletTalley)")
And so on.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 12
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now