I am trying to design a query that works with a relationship between a
Table and a Query. I am comparing a value in the table with a computed
value inside the query.
The reason for this is that the Query calculates a field to become
identical to the corresponding table field, for instance:
Table 1 contains field "ID", which is WV008A00
Table 2 contains field "ID In", which is WV001000
In the query I created, I am using a function that converts Table 1's
ID to look identical to the records in Table 2. Namely, changing the
'8' to a 1' and the 'A' to a '0'. Let's call this Query calculation
field "FormattedID"
Now, I need to relate the Query calculation field "FormattedID" with
Table 2's "ID In" field and bring results from both tables.
If I make a relationship in the Relationships window between
"FormattedID" and "ID In", the properties window for that relationship
is very limited. Everything is greyed out and the only property I can
change is the 'Join Type' button.
Also, after relating the query and table and running my new query, it
will return every record from the calculation query for EVERY record in
Table 2. For instance, if Table 2 returns 2000 results and the
calculation query returns 20, my new query generates 40,000 results.
Any help in what direction I should be taking with this would be most
appreciated. Also, I am fairly new at Access, but not VB programming.
