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

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

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