Identify most common text value or last text value

[ffionnah]

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

 

[JonXL]

Do you add a new field (column) for each new month?

 

[Joe4]

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]

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.

 

[ffionnah]

Thank you for all of your input, I really appreciate it! I will take a look at those resources.