Join the tables and only select from one of them

Post #145,930 by ben_tilly 3/12/04 5:16:25 PM Reply	Join the tables and only select from one of them "good ideas and bad code build communities, the other three combinations do not" - [link\|http://archives.real-time.com/pipermail/cocoon-devel/2000-October/003023.html\|Stefano Mazzocchi]
Post #145,931 by acagle 3/12/04 5:21:43 PM Reply	Re: Join the tables and only select from one of them How do I do that using the Set statement?
Post #145,932 by acagle 3/12/04 5:32:18 PM Reply	Re: Join the tables and only select from one of them AHA! Never mind. I really didn't think it would accept an sql statement as the table def, and the "help" system wouldn't give me the syntax anyway.
Post #145,946 by FuManChu 3/12/04 7:01:31 PM Reply	I can't figure that strategy out. Access 2k and following seem to have gone out of their way to deny SQL ever existed, at least in the documentation. I will always write: Set rstA = CurrentDb.OpenRecordset("SELECT * FROM tblALS_Add WHERE BatchNo = " + xxx, dbOpenDynaset) ...before I use the brain-dead query builder tools. Can I assume your AHA! meant you tried something like: sql = "SELECT * FROM tblALS_Add WHERE EXISTS (SELECT * FROM tblALS_V WHERE tblALS_V.BatchNo = tblALS_Add.BatchNo)" Set rstA = CurrentDb.OpenRecordset(sql, dbOpenDynaset) ...or... sql = "SELECT tblALS_Add.* FROM tblALS_V LEFT JOIN tblALS_Add ON tblALS_V.BatchNo = tblALS_Add.BatchNo" Set rstA = CurrentDb.OpenRecordset(sql, dbOpenDynaset) ? I was one of the original authors of VB, and I wouldn't use VB for a text processing program. :-) Michael Geary, on comp.lang.python
Post #145,951 by mmoffitt 3/12/04 7:30:36 PM Reply	I think his Ah-hah was that he realized that in Access you can use a query as a primary record source. Like he uses the QBE grid to create a query on Table A that returns the rows he needs and saves it as qryA. Then does a "select foo from qryA where ..." That's only my interpretation of what he wrote, of course ;0) bcnu, Mikem I don't do third world languages. So no, I don't do Java.
Post #146,333 by acagle 3/15/04 5:52:38 PM 3/15/04 6:08:48 PM Reply	Ya, that's it. I used: strSQL = "SELECT tblBLS_Add.* FROM tblBLS_Add WHERE (((tblBLS_Add.BatchNo)=" & Me![BatchNo] & "));" Set rstA = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) Now I'm having trouble with a stupid form losing its filter after doing a bunch of code. It's completely baffling me. And I can't reset the filter with code either. [Edit] WEEEEEE!!!! Fixed it. But actually a head-slap because I myself was causing the filter to go off from a bit of code that I lifted for use from a different application. Bad me. Edited by acagle March 15, 2004, 06:08:27 PM EST Edited by acagle March 15, 2004, 06:08:48 PM EST Expand All History
Post #146,399 by a6l6e6x 3/15/04 9:31:13 PM Reply	Cut and paste is a known killer! :) Alex Necessity is the plea for every infringement of human freedom ... the argument of tyrants ... the creed of slaves. -- William Pitt, addressing the British House of Commons (1783)

Welcome to IWETHEY!