vendredi 8 juin 2012 03:34
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! . . .
Toutes les réponses
vendredi 8 juin 2012 03:36
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
vendredi 8 juin 2012 03:49
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
- Marqué comme réponse DragonForest samedi 9 juin 2012 07:33
vendredi 8 juin 2012 04:17Many 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).
vendredi 8 juin 2012 04:30What if you add 'DISTINCT' to your select query
vendredi 8 juin 2012 04:47
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.
samedi 9 juin 2012 07:35Many 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.