Results 1 to 9 of 9
  1. #1
    templeowls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    54

    Can a form show the number of entries there are for a customer, based on an ID/

    Sorry for a likely confusing title. Basically I have a database that tracks customer complaints. It's just a table that holds the complaints and a form that has a free text box for follow-up. What would be nice would be if the form could auto generate the number of complaints a customer has made. All I'm looking for is just a number. Could use the customer name or ID to pull in the data via a query maybe. That way a manager could go and see what their other complaints were and if there's a greater issue. Probably not possible and is too specific but I thought I'd try.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,688
    Could try a DCount() expression in textbox. Do research on domain aggregate functions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,899
    dcount("*","table","[id]=" & txtID)

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,598
    I agree with June and ranman if all you need is the number of complaints for a Customer.
    However, if this is something new development/adjustment, then you might consider Category, Severity and DateOfComplaint if this is to be helpful to a manager.
    Just a few thoughts for consideration. You know your situation better than readers.
    Good luck with your project.

  5. #5
    templeowls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    54
    Quote Originally Posted by ranman256 View Post
    dcount("*","table","[id]=" & txtID)
    Thank you!! So if i have tblComplaints and frmComplaints, what would be the exact expression for it to pull in a count based on the field "CustomerID"?

    dcount("frmComplaints","tblComplaints","[CustomerID]=" & txtID)??

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,688
    Not quite. As ranman already showed, * character is adequate for DCount - for other domain aggregates would specify field name:

    DCount("*","tblComplaints","CustomerID=" & txtID)

    Again, research domain aggregates for better understanding of usage.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    templeowls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    54
    Again, research domain aggregates for better understanding of usage.
    Yes, I did following your first comment. But any article or video I find is a very macro approach, and doesn't really apply exactly to what I'm looking for. Hence why I'm using the forum for a more personalized approach. For instance, what do I put in for the "txtID"? I still have no idea how this is used to reference a field within the form despite 35 minutes of DCount article reading.

    Saying "go research elsewhere" is not really helpful at all, man. But I appreciate the help. Thanks guys.

  8. #8
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,166
    Quote Originally Posted by templeowls View Post
    Yes, I did following your first comment. But any article or video I find is a very macro approach, and doesn't really apply exactly to what I'm looking for. Hence why I'm using the forum for a more personalized approach. For instance, what do I put in for the "txtID"? I still have no idea how this is used to reference a field within the form despite 35 minutes of DCount article reading.

    Saying "go research elsewhere" is not really helpful at all, man. But I appreciate the help. Thanks guys.
    "txtID" would be the name of the control (textbox?) on your form that holds the same data as the Field called "CustomerID" in the table.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,688
    They all follow the same syntax:

    FunctionName("source field", "source table/query", "criteria here")

    Criteria argument follows the same structure of an SQL WHERE clause only without the WHERE keyword.

    The following seem to cover the basics:
    https://support.office.com/en-us/art...B-BED10DCA5937
    http://blueclaw-db.com/domain_aggregate_function/
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-07-2020, 10:46 PM
  2. Limit record entries based on number in field
    By lamore48 in forum Access
    Replies: 5
    Last Post: 02-12-2018, 02:05 PM
  3. Replies: 1
    Last Post: 09-05-2015, 01:55 PM
  4. Replies: 6
    Last Post: 08-27-2015, 11:35 PM
  5. Form with an unkonwn number of entries?
    By bakkouz in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 12:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums