Access: Counting checked boxes in a multi value lookup field

Question

Hi I have a table with on field of client names 'Clients' and a multi-value look up field called 'Addresses' containing the addresses the clients have applied for. In this field I can check multiple boxes of addresses a particular client has applied for. I would like access to count the addresses each client has applied for and display the number of total checked boxes per client. Please advice how to do this.

Answer 1

If you want to display this information in a form or report, you can use the VBA code to populate the form/report with the calculated data. Create a form/report, and then add a textbox bound to the "CheckedCount" field from the query.

In the form/report's VBA module, use the DLookup function to retrieve the count of checked boxes for each client and update the textbox value accordingly. For example:

vba Copy code Private Sub Form_Current() Dim clientID As Long Dim checkedCount As Integer

' Get the Client ID for the current record clientID = Me.ClientID

' Use DLookup to retrieve the count of checked boxes for the current client checkedCount = DLookup("CheckedCount", "YourQueryName", "ClientID = " & clientID)

' Update the textbox with the count of checked boxes Me.txtCheckedCount.Value = checkedCount End Sub Replace "YourQueryName" with the actual name of your query and adjust the field and table names to match your database.

By following these steps, you can count the checked boxes in the multi-value lookup field and display the count per client in Access.

Answer 2

Hi,

I'm Ajibola, an Independent Consultant here and a Microsoft user like you. I don't work for Microsoft and cannot access any of your data on their system.

To count the number of checked boxes in a multi-value lookup field in Access, you can use the following steps:

1. Create a new query in design view.
2. Add the table that contains the multi-value lookup field to the query.
3. Add the multi-value lookup field to the query.
4. In the "Total" row of the query design grid, select "Count" from the drop-down list for the multi-value lookup field.
5. Run the query.

This will display a count of the number of checked boxes for each client in the "Clients" field.

I hope this helps! Let me know if you have any other questions.

References: (1) Access: Counting checked boxes in a multi value lookup field. https://answers.microsoft.com/en-us/msoffice/forum/all/access-counting-checked-boxes-in-a-multi-value/79d5036f-54f2-445f-bbb0-e08e8d490eb6. (2) Create or delete a multivalued field - Microsoft Support. https://support.microsoft.com/en-us/office/create-or-delete-a-multivalued-field-7c2fd644-3771-48e4-b6dc-6de9bebbec31. (3) Using Lookup and multivalued fields in queries. https://support.microsoft.com/en-us/office/using-lookup-and-multivalued-fields-in-queries-6f64f92d-659f-411c-9503-b6624e1e323a.

Kind regards Ajibola

Answer 3

I would agree with Scott that multi-valued fields are best avoided.  For an illustration of how such fields can be converted into a conventional design, where the many-to-many relationship type is modelled by a table which resolves it into two one-to-many relationship types You might like to take a look at MVFCorrector.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo file illustrates how to recast the data where it is currently represented by a multi valued field which draws its list for a table, or which draws its list for a value list embedded in the design of the field.

For an illustration of the use of an unbound multi-valued list box as the interface take a look at StudentCourses.zip in the same OneDrive folder.  Note, however, that using a list box in this way does not allow for any non-key attributes of the relationship type to be included, such as the Status column in my demo.  I would therefore recommend the conventional interface of a form/subform, which is very easy to implement and is code free.

If you decide to continue to use a multi-valued field then you need to reference its Value property in a query to aggregate the number of True values per row. The query should be grouped by the table's primary key.

Answer 4

While multi-value fields (MVF) present a nice interface that allows you to check values, they are often a pain to use the data. That's why most pro developers don't use them. They are, basically, a kludge on a many to many relationship.

I would recommend scrapping the MVF and and adding a client/address table:

tjxClientAddress

ClientAddressID (PK Autonumber)

ClientID (FK)

AddressID (FK)

For data entry, add a subform, bound to this table to your main Clients form linked on ClientID. This subform would have one visible control, a combobox that lists all the addresses the Client applies for. From there it is very easy to get a count by Client:

SELECT ClientID, Count(AddressID) as NumAddresses

FROM tjxClientAddress

GROUP BY ClientID;

The subform would also have a count.

Alternatively, you could have a multi-select listbox, that allows the user to click on multiple addresses and then populate tjxClientAddresses via code.

Answer 5

Hi, I'm Ajibola, an Independent Consultant here and a Microsoft user like you. I don't work for Microsoft and cannot access any of your data on their system. To count the number of checked boxes in a multi-value lookup field in Access, you can use the following steps: 1. Create a new query in design view. 2. Add the table that contains the multi-value lookup field to the query. 3. Add the multi-value lookup field to the query. 4. In the "Total" row of the query design grid, select "Count" from the drop-down list for the multi-value lookup field. 5. Run the query. This will display a count of the number of checked boxes for each client in the "Clients" field. I hope this helps! Let me know if you have any other questions. References: (1) Access: Counting checked boxes in a multi value lookup field. https://answers.microsoft.com/en-us/msoffice/forum/all/access-counting-checked-boxes-in-a-multi-value/79d5036f-54f2-445f-bbb0-e08e8d490eb6. (2) Create or delete a multivalued field - Microsoft Support. https://support.microsoft.com/en-us/office/create-or-delete-a-multivalued-field-7c2fd644-3771-48e4-b6dc-6de9bebbec31. (3) Using Lookup and multivalued fields in queries. https://support.microsoft.com/en-us/office/using-lookup-and-multivalued-fields-in-queries-6f64f92d-659f-411c-9503-b6624e1e323a. Kind regards Ajibola

Hi Ajibola

Thanks very much for your response. I tried your solution and running the query resulted in 1 number (12) Im not sure what that number relates to as there are only 9 addresses in the data base. I would access to add up the number of addresses selected for each individual client. So if for client 1, 3 boxes with addresses are checked then I would like Access to should show the number 3 in a form or report if you get whatt I mean :)

Martin