Identify most common text value or last text value

ffionnah

Board Regular
Joined
Jun 12, 2018
Messages
56
Hi all,
I am not as familiar with MS Access. Is there a way to identify the most common text value within 10+ columns and, if there are values that count to the same amount (for example; text value 1, text value 2, text value 1, text value 2), find the last most common value (from the example it would be value 2)? In Excel, I use iferror, index, mode, offset and match for this.

Any insight is much appreciated.


Example of fields this would include:
Unique ID Value 1 Value 2 Value 3 Value 4 Value 5 Value 6.... (updated monthly so this is continuous)
12345 Text Value 1 Text Value 2 Text Value 1 Text Value 2
12346 Text Value 3 Text Value 3 Text Value 3 Text Value 3
12347 Text Value 4
12348 Text Value 5 Text Value 5 Text Value 5 Text Value 6 Text Value 6 Text Value 6
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

JonXL

Active Member
Joined
Feb 5, 2018
Messages
302
Office Version
365, 2016
Platform
Windows
Do you add a new field (column) for each new month?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
Your data design is really not normalized, and therefore, really not conducive to being worked on in Access.
In Access Table Design, it is important to follow the Rules of Normalization (you can Google these), or else you may find it very hard to work with the data.
In this case, you would NOT have repeated columns of similar data. Rather, you would have a column describing what time frame the data is for, and then a column with the value.
So there would only be one value column.

In this sort of design, you could do what you want rather easily. With the design layout you posted above, what you want to do is rather difficult to do in Access.
If you are unable to change your data structure, you are probably better trying to use Excel to get what you want, as it is going to be very difficult to do in Access with an un-Normalized data structure, since Access functions are built to work on single fields (columns), and not across fields (columns).
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,250
Office Version
365
Your database is not normalized. That means your table structure is working against you and issues will increase until you address this basic problem.
Access and Excel are very different animals; built on different object models.
You need to research and apply basic database concepts to your evolving database.
There are several articles in this link on Database Planning and Design that may help you.
The tutorials from RogersAccessLibrary can be very effective and efficient to learn and experience the basics if you spend 45 -60 minutes and work through 1 or 2.
Good luck.
 

Forum statistics

Threads
1,089,218
Messages
5,406,920
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top