asked on
How do you do a query using the first 5 characters or first 5 numbers joining two tables?
How do you join two tables and do a search using only the first 5 characters or first 5 numbers?
SQL
Last Comment
VinceBurgess
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
How would you do this in Access '97? It's almost like Access doesn't recognize this function.
ohhh... do u have substring finction there use that instead of left
LEFT function works in access.
I'm sure there are cleverer ways, but I usually resort to a simple but untidy method.
For each table, create a query containing all the fields you want + another field which is the first 5 characters. For example if you have a field called 'name' you could create a field called 'name5' in a query ie. "name5: Left([name],5)"
Then create a third query which joins the two querys using the new fields.
Its a bit untidy as you end up with three queries to do the job. But its easy and quick.
You also mentioned numbers. If your numbers are just numbers stored as text, then this will be fine. If you really mean numbers, then you have to decide how you want rationalise them. such as first five significant figures or rounded etc. and then create suitable functions to do that in your query.
For each table, create a query containing all the fields you want + another field which is the first 5 characters. For example if you have a field called 'name' you could create a field called 'name5' in a query ie. "name5: Left([name],5)"
Then create a third query which joins the two querys using the new fields.
Its a bit untidy as you end up with three queries to do the job. But its easy and quick.
You also mentioned numbers. If your numbers are just numbers stored as text, then this will be fine. If you really mean numbers, then you have to decide how you want rationalise them. such as first five significant figures or rounded etc. and then create suitable functions to do that in your query.