Joining fields in two unrelated Tables in SQL (Access VBA)

Question

Dear All,

I'd like to set up a query to "join" two totally unrelated tables (T1, T2), taking a single field (F1) from a single row in T1 and putting that side by side with a single field (F2) from a single row in T2, with the resulting Query having just one row with two fields i.e. F1, F2. (Note: I don't want a UNION Query, as that will result in a query with two rows).

I saw on another site for another programming language that offers something like "JOIN ALWAYS" (and I even tried to JOIN on 1 = 1, so as always to evaluate to true), but SQL in Access VBA doesn't seem to support this (or maybe I've overlooked something?)

Many thanks in advance if anyone has a sample SQL code snippet that can achieve what I'm attempting!  . . . 

---

DragonForest

Answer 1

Perhaps I am missing something, but couldn't you create a new query, select both tables, grab T1.F1 and drag-n-drop it on T2.F2? Then drag both fields to the results grid.

---

-Tom. Microsoft Access MVP

Answer 2

Quick follow-up . . . I tried the following, which seems to work, but seems slightly inelegant, so if anyone has anything simpler, that would be great . . . many thanks!

SELECT F1, F2 FROM

(SELECT 1 as key, F1 FROM T1 WHERE F2  = #6/5/2012#) AS q1 INNER JOIN

(SELECT 1 as key, F2 FROM T2 WHERE F2 = #4/1/2010#) AS q2

ON q1.key = q2.key

---

DragonForest

Answer 3

Many thanks for your reply. A noble attempt (but unless I missed something) I believe that returns too many records (I forget what the technical term is . . .  "multiplicative join?" I think it returns R1 X R2 records unless I'm mistaken). 

---

DragonForest

Answer 4

What if you add 'DISTINCT' to your select query

---

Chris Ward

Answer 5

Try;

SELECT F1, F2
FROM T1, T2
WHERE F1 = #6/5/2012# AND F2 = #4/1/2010#

Assuming your original example should have been "(SELECT 1 as key, F1 FROM T1 WHERE F1 = #6/5/2012#)" this will return the single fields from each table on the proviso that the dates are unique within each table.  For example, if T1 has three records where the value of F1 is #6/5/2012# then you will get 3 records returned unless you use the "DISTINCT" option as mentioned by Chris Ward.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for changing her words.

Answer 6

Many thanks to all . . . Both approaches seem to work (though I may need to see if "DISTINCT" has any unforeseen pitfalls in the context of the solution which I'm solving). Thanks again for all the great help.

---

DragonForest