Forum Discussion
MS Access: Looking to find a way to check for matches across two fields based on character position.
There are two ways to approach the problem. One is hard, one a lot easier.
The hard way is to use this approach, in which three different data points are mushed into a single field, forcing you to figure out a trick to extract any one of them in order to use it. Hence the question here.
The easy way, which also turns out to be the way relational database design requires, is to create one field for each data point and put the data into their own fields. It's far, far easier to concatenate if you want to DISPLAY the mushed together value to a human user. It's also far easier for Access to manage the discrete data points if they remain discrete in the three fields in the table.
Follow the principles of proper table design, which is called Normalization, and your task is greatly simplified here and everywhere else you need to filter by location, or by project type, or by fiscal year, or any combination of the three!
