Is it possible to ORDER BY a SubSelect? I don't see why it wouldn't
be, but I'm having some trouble. Hopefully it's just a simple error in
syntax and somebody can tell me. Here's what I'm trying to do...
I've got two tables:
Table1:
TestData Filename
Bird FileB
Blue FileA
Circle FileC
Fish FileB
Green FileA
Nofile1 (NULL)
Nofile2 (NULL)
Nofile3 (NULL)
Square FileC
Trapezoid FileC
Triangle FileC
Yellow FileA
Table2:
DateImported Filename
9/20/2005 FileA
9/21/2005 FileB
9/22/2005 FileC
I'd like to get the full listing of elements from Table1, sorted by the
DateImported field that corresponds to them by the two tables
"Filename" field.
I know that one way of doing it would be like so:
SELECT DateImported, *
FROM TABLE1, TABLE2
WHERE Table2.Filename=Table1.Filename
ORDER BY DateImported;
However, that query does not return the records from Table1 that have a
(NULL) value for their Filename.
My next guess was to do this:
SELECT (SELECT DateImported FROM Table2 WHERE
Table2.Filename=Table1.Filename) AS ImportDate, *
FROM TABLE1
ORDER BY ImportDate;
If you take out the "ORDER BY ImportDate" from this query, it returns
ALL the elements from Table1, just like I want and once I have them I
can sort them by their ImportDate by right-clicking on the column name.
However, I need the query to do the sorting and when I leave the
"ORDER BY ImportDate" on the end of that query, Access for some reason
doesn't realize that it's the name of my subselect column.
Any ideas?