How do I join tables with different data formats in the field? For example in ACCESS 97: One field has 1AA10 in...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
one table but in the other table I have AA10. I need to join the tables together. The 1 in 1AA10 is for fiscal year 2001 and next fiscal year it will read 2AA10 for fiscal year 2002. (1 must be the fiscal year.) The portion of the field I need to join is AA10. Please help an SQL newbie!
The SQL depends on where the 1 and 2 will be coming from. If you will be hardcoding the values into the query as literals, you can use
select * from oneTable, otherTable where oneTable.ID = '1' + otherTable.ID
Note that the above uses the Microsoft Access syntax for concatenating character fields; the standard SQL syntax uses double pipes, i.e.
'1' || otherTable.ID
If you wanted to obtain the '1' or '2' from an actual fiscal year number, you would need to convert the year number to a string, then take the rightmost character of it. Here's an example using the year of the current date, which in Microsoft Access is given by the Now function --
select * from oneTable, otherTable where oneTable.ID = Right(Str(Year(Now)),1) + otherTable.ID
Dig Deeper on Oracle DBA jobs, training and certification
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.