If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL view of messed up action queries
I really appreciate your kindness in helping me out. Here
is the SQL for the action queries (if it clarifies things): SQL of append query (AcceptoAppend): INSERT INTO tbl_New_Report_DB ( Record_ID, [Date], Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees Paid], Credits, ConCredits, [Obl Fees] ) SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data Entry].Date, [tbl_Inital Data Entry].Jurisdiction, [tbl_Inital Data Entry].Zone, [tbl_Inital Data Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits, [tbl_Inital Data Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees] FROM [tbl_Inital Data Entry] WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept")); SQL of delete query (DeleteonDecline): DELETE [tbl_Inital Data Entry].*, [tbl_Inital Data Entry].AdminStatus, [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data Entry].Date, [tbl_Inital Data Entry].Jurisdiction, [tbl_Inital Data Entry].Zone, [tbl_Inital Data Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits, [tbl_Inital Data Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees], [tbl_Inital Data Entry]![AdminStatus] FROM [tbl_Inital Data Entry] WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline")); I have put these both into a macro called "AcceptDeclineMac" which only has two actions: 1. Open Query (argument is open AccepttoAppend) 2. Open Query (argumeent in open DeleteonDecline) AcceptDeclineMac is referenced to in the properties/event/"on change", as the AdminStatus field is a pull down menu. I also tried linking the macro to the "after update field" -- no changes in performance. I really appreciate your time, I will keep an eye out for your response. Thanks, Kendra |
#2
|
|||
|
|||
Kendra,
I'm not a SQL expert, but............. 1.) Don't use reserved words (like Date) as field names. Also, don't use spaces in names. It is easier (less headaches) to use tbl_Inital_Data_Entry or tblInitalDataEntry. Only the developer will (should) ever see the field/control names. (Not really a problem, but good programming practice) 2.) Try not to use macros. The forum has lots of threads debating Macos vs VB code, but Macros have too many disadvantages compared to VB code (in my opinion). If you are still learning VB, write a macro, but then use the wizard to convert it to VB code. (Again, not really a problem, but makes it easier to develop DBs) 3.) WHY are there 10 conditions in the Where expressions? Since they are the same, you only need one: For the delete query (DeleteonDecline) try (should be on one line): DELETE * FROM [tbl_Inital Data Entry] WHERE [tbl_Inital Data Entry]![AdminStatus])="Decline"; NOTE: The statement above will delete the RECORD, not set the fields to Null. For the append query (AcceptoAppend) try changing the Where condition to: WHERE [tbl_Inital Data Entry]![AdminStatus])="Accept"); The 'real' Experts will be along later to straighten us out :-D HTH Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Kendra" wrote: I really appreciate your kindness in helping me out. Here is the SQL for the action queries (if it clarifies things): SQL of append query (AcceptoAppend): INSERT INTO tbl_New_Report_DB ( Record_ID, [Date], Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees Paid], Credits, ConCredits, [Obl Fees] ) SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data Entry].Date, [tbl_Inital Data Entry].Jurisdiction, [tbl_Inital Data Entry].Zone, [tbl_Inital Data Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits, [tbl_Inital Data Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees] FROM [tbl_Inital Data Entry] WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept")); SQL of delete query (DeleteonDecline): DELETE [tbl_Inital Data Entry].*, [tbl_Inital Data Entry].AdminStatus, [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data Entry].Date, [tbl_Inital Data Entry].Jurisdiction, [tbl_Inital Data Entry].Zone, [tbl_Inital Data Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits, [tbl_Inital Data Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees], [tbl_Inital Data Entry]![AdminStatus] FROM [tbl_Inital Data Entry] WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And ([tbl_Inital Data Entry]![AdminStatus])="Decline")); I have put these both into a macro called "AcceptDeclineMac" which only has two actions: 1. Open Query (argument is open AccepttoAppend) 2. Open Query (argumeent in open DeleteonDecline) AcceptDeclineMac is referenced to in the properties/event/"on change", as the AdminStatus field is a pull down menu. I also tried linking the macro to the "after update field" -- no changes in performance. I really appreciate your time, I will keep an eye out for your response. Thanks, Kendra |
#3
|
|||
|
|||
Not sure WHY things were reformatted for you. I would try pasting the following
into the SQL window and then switch to the design window and see what it gives you. I did surround Date and Zone with [] and changed the ! to a . in the where clause. INSERT INTO tbl_New_Report_DB ( Record_ID, [Date], Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees Paid], Credits, ConCredits, [Obl Fees] ) SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data Entry].[Date], [tbl_Inital Data Entry].Jurisdiction, [tbl_Inital Data Entry].[Zone], [tbl_Inital Data Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits, [tbl_Inital Data Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees] FROM [tbl_Inital Data Entry] WHERE [tbl_Inital Data Entry].[AdminStatus])="Accept" IF the query works, then save it, close it, open it in design view and look at it. Did it still change? Please post back with your results. And if it changed, post back to show us the new results. Kendra wrote: I really appreciate your kindness in helping me out. Here is the SQL for the action queries (if it clarifies things): SQL of append query (AcceptoAppend): INSERT INTO tbl_New_Report_DB ( Record_ID, [Date], Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees Paid], Credits, ConCredits, [Obl Fees] ) SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data Entry].Date, [tbl_Inital Data Entry].Jurisdiction, [tbl_Inital Data Entry].Zone, [tbl_Inital Data Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits, [tbl_Inital Data Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees] FROM [tbl_Inital Data Entry] WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And ([tbl_Inital Data Entry]![AdminStatus])="Accept")); S N I P I have put these both into a macro called "AcceptDeclineMac" which only has two actions: 1. Open Query (argument is open AccepttoAppend) 2. Open Query (argumeent in open DeleteonDecline) AcceptDeclineMac is referenced to in the properties/event/"on change", as the AdminStatus field is a pull down menu. I also tried linking the macro to the "after update field" -- no changes in performance. I really appreciate your time, I will keep an eye out for your response. Thanks, Kendra |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access and SQL | AHopper | General Discussion | 26 | August 3rd, 2004 07:01 PM |
Efficient queries with SQL backend | wheeel_o | Running & Setting Up Queries | 1 | July 20th, 2004 02:38 AM |
Product Key for Office XP | P.G.Indiana | Setup, Installing & Configuration | 1 | June 7th, 2004 03:22 AM |
sql view | charles osborne | Database Design | 1 | May 29th, 2004 05:21 AM |
Hold it, we found SQL view.... | 1.156 | Running & Setting Up Queries | 2 | May 28th, 2004 03:20 PM |