OpenRecordset in Access.

Post #145,929 by acagle 3/12/04 5:14:04 PM Reply	OpenRecordset in Access. Hey dudes. I need to compare the records in two tables, call them rstV and rstA. rstV is a subset of rstA. Is there a way to put a filter on the rstA recordset to get only the ones that are also in rstV? This is what I use to get rstA: Set rstA = CurrentDb.OpenRecordset("tblALS_Add", dbOpenDynaset) SO I want like "Set rstA = CurrentDb.OpenRecordset("tblALS_Add", dbOpenDynaset) where tblALS_Add!BatchNo=xxx". ANd the Access 2000 "help" system is not at all.
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)
Post #145,934 by altmann 3/12/04 5:42:06 PM Reply	Bookmarks? Build an array of bookmarks on records in rstA that exist in rstV then pass that array to the Filter property of rstA? Not that I've ever done wuch a thing. I'd go for Ben's suggestion first. -- Chris Altmann
Post #146,997 by acagle 3/18/04 6:01:29 PM Reply	'Nother question. Is there an easy way to remove duplicates with SQL? E.g.: Johnson John 12/24/1963 blah blah blah Johnson John 12/24/1963 blah blah blah Schmugenmeyer Froelich 6/5/1948 blah blah blah Schmugenmeyer Froelich 6/5/1948 blah blah blah Schmugenmeyer Froelich 6/5/1948 blah blah blah
Post #147,002 by ben_tilly 3/18/04 6:24:13 PM Reply	Oooh, fun :-) The simplest and most portable way is to do an insert into an identical table that is the output of a group-by from your original table. Beyond that, well you get into vendor extensions, custom programming etc. The problem looks simple, but it is non-trivial exactly because SQL is set-oriented. By its nature, there is no set statement that you can make which actually distinguishes two identical things. So you can easily identify delete all duplicated things. But determining which duplicate to get rid of is another story. In Access this is a very good reason to add an auto-incrementing ID column. Then you are guaranteed to always have some way to distinguish two rows (the ID column). Cheers, Ben "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 #147,013 by ChrisR 3/18/04 7:46:02 PM Reply	SELECT DISTINCT or a GROUP BY
Post #147,060 by mmoffitt 3/19/04 11:06:48 AM Reply	In access, I think its Select DistinctRow bcnu, Mikem I don't do third world languages. So no, I don't do Java.
Post #147,067 by FuManChu 3/19/04 12:20:32 PM Reply	Make and examine a "Find Duplicates" Query For example: SELECT * FROM Telescopes WHERE (((Telescopes.ID) In (SELECT [ID] FROM [Telescopes] As Tmp GROUP BY [ID] HAVING Count(*)>1 ))) ORDER BY Telescopes.ID; Then write a VBA script to iterate through the results and delete all but one of each set.

Welcome to IWETHEY!