How do I join tables with different data formats in the field? For example in ACCESS 97: One field has 1AA10 in 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.