Thanks Shawn.... it looks like taking an approach more like you suggest is going to work out. Cheers, Mark
On Tue, Aug 18, 2009 at 10:40 AM, Pierson, Shawn <shawn.pier...@sug.com>wrote: > ** > > I think your underlying flow is part of the problem. Why not just have a > field on the ALLrecs form such as the Status that can have only two values > of “New” and “Processed”? Even if you have a real need to push the records > to the ProcessedRecs table, you could have a filter on the ProcessedRecs > table push back the “Processed” value to the Allrecs table, at which point > your query in the Allrecs table can be where ‘Status’ = 0 (e.g. New)? > > > > I think rather than trying to make what you are doing work, you may want to > take a step back and look at your design and make sure you are proceeding > down the right path. > > > > Shawn Pierson > > > > *From:* Action Request System discussion list(ARSList) [mailto: > arsl...@arslist.org] *On Behalf Of *Mark Seiler > *Sent:* Tuesday, August 18, 2009 8:35 AM > *To:* arslist@ARSLIST.ORG > *Subject:* Query against two forms > > > > ** > > Hi Listers, > I'm looking for some guidance on doing the following: > I have 2 regular forms called ALLrecs and PROCESSEDrecs, each having fields > like F1, F2 and F3 > I'm looking to populate a third form to contain the results of a query such > that the user can select from a list of AVAILABLE records (everything in > ALLrecs less all the records in PROCESSEDrecs). A working SQL statement to > do this is: > > SELECT F1, F2, F3 > FROM ALLrecs > WHERE (F1 NOT IN (SELECT F1, F2, F3 FROM PROCESSEDrecs)) > > Making this new AVAILABLE form a join form (inner or outer) doesn't seem to > be giving me the results I want. Perhaps a Direct SQL or table walk on > ALLrecs? arrgh. > This AVAILABLE data has to be updated or recreated each time the user > performs this activity. > > Ultimately the users selections from AVAILABLE will be further processed, > then will update the PROCESSEDrecs form etc etc (I've got that part). > > Thanks for any thoughts. > -Mark > > > Win Server 2003/MS SQL 2000 > ARS 7.1 patch 6 > ITSM 7.0.03 patch 006 > > _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers > Are"_ > Private and confidential as detailed > here<http://www.sug.com/disclaimers/default.htm#Mail>. > If you cannot access hyperlink, please e-mail sender. > _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers > Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"