Re: Query against two forms
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.comwrote: ** 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 herehttp://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
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 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Query against two forms
Why didn't you use a Join form, you'll be using less space. Hugo Ruesga perotsystems® US 972.577.7000 MX +52 (33) 3332.3868 P Please consider the environment before printing this email The information contained in and transferred with this electronic message is intended only for the recipient(s) designated above, it is protected by law and it may contain information which is privileged and confidential. If you are not the intended recipient, please do not read, copy, or use it, and do not disclose it to others. Please notify the sender of the delivery error by replying to this message, and then delete it from your system. Thank you. Date: Tue, 18 Aug 2009 09:34:54 -0400 From: mark.sei...@gmail.com Subject: Query against two forms To: arslist@ARSLIST.ORG ** 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_ _ Messenger cumple 10 años y tiene regalos para ti www.aniversariomessenger.com.mx ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Query against two forms
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 the link, please e-mail sender. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
