CODEWITHSUNDEEP
vbams-access
Fil
Fil

Reputation: 471

Returning the most common text from a Column

Is there a function that returns the most occurring text form a text filed in MS Access? More like the Mode string (the string that appears the most in a column). I have tried DlookUp but I don't know how to filter out to return the single string of interest.

The data on the query looks like:

CustomerNo PurchasedItem FinacialYearDate PruchaseDate
123        Books         01/01/2021       01/02/2022
123        Books         01/01/2021       03/03/2022
123        Books         01/01/2021       21/02/2022
123        Note pads     01/01/2021       01/02/2022
124        Pens          01/01/2021       01/01/2022
124        Note pads     01/01/2021       01/03/2022
124        Books         01/01/2021       01/03/2022

Expected Result for CustomerNo 123 should be: Books

This is my approach:

Private Sub Form_Current()
Me.lblMostPurchsedItemThisYear.Caption = "(" & DLookUp("PurchasedItem", "qryCustomerProfile", "[CustomerNo] = txtCustomerNo AND [PurchasedItem] = ..... AND DateDiff('d', [FinacialYearDate], [PruchaseDate]) < 365") & ")"
End Sub

Thank you SO.

Upvotes: 0

Views: 94

Answers (1)

tinazmu
tinazmu

Reputation: 5139

You can create your query based on this (adding other parameters as required):

SELECT TOP 1  PurchasedItem
FROM YourTable t
where CustomerNo=[pCustomerNo]
group by PurchasedItem
ORDER BY Count(*) desc;

And use this in the DLookup

Upvotes: 2

Related Questions