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 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.
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)
New 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
New '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
New 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]
New SELECT DISTINCT or a GROUP BY
New In access, I think its Select DistinctRow
bcnu,
Mikem

I don't do third world languages. So no, I don't do Java.
New 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.
     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)

So anyway...
149 ms