Problems on joining result from 2 queries

[basura]

Hello,

I have two queries generating the following information:
Query 1: bank
relation bank

Query 2: relation
relation bank1 bank2

I want to see relation.relation, bank.relation, bank.bank where bank1 or bank2 matches bank.bank
I try to run the following query:

select relation.relation, bank.relation, bank.bank
FROM relation
INNER JOIN bank ON relation.bank1 = bank.bank;

But I keep getting expression errors..... I'm sure I don't have typo's

Anyone who can help me out ?

 

[neileg]

Your query won't match on bank2 but it should be OK on bank1. Exactly what errors are you getting?

 

[basura]

Sorry, posted to quickly....
It should be like this (I think):

select relation.relation, bank.relation, bank.bank
FROM relation
INNER JOIN bank ON relation.bank1 = bank.bank OR relation.bank2 = bank.bank;

Okay, I keep getting a type mismatch exception..... finally I understand, two different types are being compared

 

[neileg]

This suggests that at least one of bank, bank1 or bank2 is a different data type to the others.

 

[basura]

mmm.... now, how do I compare relation.bank2 with bank.bank when relation.bank2 is a text field and bank.bank is a numeric value ?

 

[neileg]

Use a function to change the datatype. Proably easier to change the text to numeric using CInt() or maybe Val(). Either change the inital query or your compound query.

 

[basura]

Thanks for helping me out here !