IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 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]
New Re: Join the tables and only select from one of them
How do I do that using the Set statement?
New 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.
New 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
New 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.
New 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.
Expand Edited by acagle March 15, 2004, 06:08:27 PM EST
Expand Edited by acagle March 15, 2004, 06:08:48 PM EST
New 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)
     OpenRecordset in Access. - (acagle) - (13)
         Join the tables and only select from one of them -NT - (ben_tilly) - (6)
             Re: Join the tables and only select from one of them - (acagle)
             Re: Join the tables and only select from one of them - (acagle) - (4)
                 I can't figure that strategy out. - (FuManChu) - (3)
                     I think - (mmoffitt) - (2)
                         Ya, that's it. - (acagle) - (1)
                             Cut and paste is a known killer! :) -NT - (a6l6e6x)
         Bookmarks? - (altmann)
         'Nother question. - (acagle) - (4)
             Oooh, fun :-) - (ben_tilly)
             SELECT DISTINCT or a GROUP BY -NT - (ChrisR) - (1)
                 In access, I think its Select DistinctRow -NT - (mmoffitt)
             Make and examine a "Find Duplicates" Query - (FuManChu)

Did you recently read an article?
125 ms