Query not Updateable

  • Thread starter Thread starter Gerard
  • Start date Start date
G

Gerard

Hey all,
I am running SQL 2k on Win 2k Server. We are
migrating from an Access app to a VB app. In the mean
time, we are using SQL Server as a backend for our Access
app. When certain Access queries run against SQL Server,
the Datasheet that comes back can not have records added
to it. Some almost identical queries come back fully
updateable. I've checked table permissions and that
doesn't seem to be the problem. Identical connection
strings make no difference, any ideas. Thanks in advance,
any help is appreciated. Below I have included the
queries in question with the table DDL and some data.

Gerard


*****************************************************
tblACH_Batch
-----------------------------------------------------
SQL Query(this is the query that isn't updateble)
-----------------------------------------------------
SELECT tblACH_Batch.AB_ABA_NUM, tblACH_Batch.AB_ACCTNUM,
tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
tblACH_Batch.AB_REMARKS, tblACH_Batch.AB_SCHED,
tblACH_Batch.AB_COMP_ID, tblACH_Batch.AB_ONCE_SW,
tblACH_Batch.AB_RETRY, tblACH_Batch.[INDICATORS],
tblACH_Batch.AB_PERIODS, tblACH_Company.COMP_NAME,
tblACH_Company.RETRY_AMOUNT
FROM tblACH_Batch LEFT JOIN tblACH_Company ON
tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
ORDER BY tblACH_Batch.AB_COMP_ID;

-----------------------------------------------------
tblACH_Batch DDL
-----------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblACH_Batch]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblACH_Batch]
GO

CREATE TABLE [dbo].[tblACH_Batch] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AB_ABA_NUM] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_ACCTNUM] [nvarchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_AMOUNT] [float] NULL ,
[AB_TRACK] [nvarchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_NAME] [nvarchar] (22) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_C_S_IND] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_BEG_DTE] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_END_DTE] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_REMARKS] [nvarchar] (4000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_SCHED] [nvarchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_COMP_ID] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_ONCE_SW] [bit] NULL ,
[INDICATORS] [nvarchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_PERIODS] [float] NULL ,
[AB_RETRY] [bit] NULL ,
[WhoWhen1] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen2] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen3] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen4] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen5] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

---------------------------------------------------
tblACH_Batch Data
---------------------------------------------------
1518,"000000000","0000000000",19,"5290911","STARR
COLE","C","1998/04/28","","06/04/03 C03 RETURN; CHANGED
ABA & ACCT #
02/03/03 R01 RETURN; JAN $ NOT COLLECTED
09/04/02 R01 RETURN
04/16/01 REACTIVATED W/NEW ACCT # PER FAX
04/03/01 R02 ACCOUNT CLOSED
08/28/00 REACTIVATED ACCT PER FAX; NEW ACCT INFO
08/02/00 R02 ACCOUNT CLOSED
07/17/00 CHANGED AMT FROM $38 TO $19; ALSO CHANGED ACCOUNT
TO BE UNDER STARR COLE ONLY PER FAX'ED REQUEST
01/26/99 NEW BANK INFO. PER FAX","M280","MT3",False,"
MT3GMS20031552004119",0,False,"","","","",""
1520,"000000000","0000000",25,"6324044","ANDREA
KINNEAR","S","1999/07/31","","7/19/99 REOPENED ACCT PER
FAX","M310","MAX",False,"IMAXGMS20022172002212",0,False,"",
"","","",""
1526,"000000000","0000000000",31,"8891510","BONITA
CANOVA","C","1998/05/01","2002/01/31","","M010","NBH",False
,"iNBHGMS19980972002032Expired
Record!",0,False,"","","","",""
1527,"000000000","0000000000",31,"8867110","ELIZABETH
BLEVIN","C","1998/04/23","2002/01/31","11/29/01 CANCELED
PER PHONE-BECKY
(DID NOT COLLECT OCT)
11/05/01 REPROCESSED NSF + $5
10/04/01 REPROCESSED NSF + $5
(DID NOT COLLECT AUG)
09/06/01 REPROCESSED NSF + $5
08/01/00 REPROCESSED 1ST NSF
10/04/99 REPROCESSED FOR
NSF","M230","NBH",False,"INBHGMS20013332001327",0,False,"",
"","","",""
1531,"000000000","0000000000",21.149999999999999,"2516328",
"NICOLE O'NEAL","C","1998/05/01","","03/07/01 CHANGED BANK
INFO PER FAX
3/22/99 CHG AMT PER
FAX","M010","CT0",False,"ICT0GMS20022112002182",0,False,"",
"","","",""
1535,"000000000","0000000000",32.270000000000003,"6940961",
"CHARLIE VANDERFORD","C","2000/12/09","","11/20/00
REACTIVATED ACCT PER FAX (ALREADY PAID $32.27 9/9/99
THROUGH 10/9/00)
09/09/99 ACCT RENEWED BY FAX","M090","MFC",False,"
MFCGMS20003252004128",0,False,"","","","",""
1536,"000000000","0000000000",130,"4268341","OCAJ,
INC.","C","1998/03/20","","01/19/99 CANCELLED PER FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1537,"000000000","00000000",75,"4399530","EDWARD
ARNOLD","C","1998/04/20","","01/19/99 CANCELLED PER FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1538,"000000000","0000000",45,"8857883","HULEC
ENTERPRISES","C","1998/05/20","","01/19/99 CANCELLED PER
FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1539,"000000000","0000000",250,"8857883","SURETY PROPERTY
MGMT.","C","1998/05/20","","01/19/99 CANCELLED PER FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1540,"000000000","00000000",145,"3736113","NATIONWIDE
PROP. MGMT.","C","1998/04/20","","01/19/99 CANCELLED PER
FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1543,"000000000","00000000000000000",19,"9874450","MICHELLE
MALONEY","C","1998/05/14","","07/16/99 CHANGED ABA & ACCT
#'S PER RETURN","M140","MT1",False,"
MT1GMS19991972004105",0,False,"","","","",""
1545,"000000000","0000000000",19,"8893869","LYDIA
DONALSON","C","1998/05/07","","07/09/03 CANCEL PER FAX
8/11/99 REACTIVATE FOR 9/7/99 PER
FAX","M070","MT4",False,"IMT4GMS20031902003188",0,False,"",
"","","",""
***************************************************


***************************************************
tblACH_Collection
---------------------------------------------------
SQL Query(this query is updateable)
---------------------------------------------------
SELECT tblACH_Collection.AC_RECORDID,
tblACH_Collection.AC_ABA_NUM,
tblACH_Collection.AC_ACCTNUM, tblACH_Collection.AC_AMOUNT,
tblACH_Collection.AC_CHECKNUM,
tblACH_Collection.AC_TRACKING, tblACH_Collection.AC_NAME,
tblACH_Collection.AC_C_S_IND,
tblACH_Collection.AC_REMARKS,
tblACH_Collection.AC_COMP_ID,
tblACH_Collection.AC_RETRY_SW,
tblACH_Collection.AC_FEE_ONLY,
tblACH_Collection.INDICATORS,
tblACH_Collection.AC_FACE_OK_SW,
tblACH_Collection.AC_FEE_OK_SW,
tblACH_Collection.AC_HOLD_SW,
tblACH_Collection.AC_DONE_SW, tblACH_Collection.AC_MIO_SW,
tblACH_Company.COMP_NAME
FROM tblACH_Collection LEFT JOIN tblACH_Company ON
tblACH_Collection.AC_COMP_ID = tblACH_Company.[KEY]
ORDER BY tblACH_Collection.AC_COMP_ID;

----------------------------------------------------
tblACH_Collection DDL
----------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblACH_Collection]') and OBJECTPROPERTY
(id, N'IsUserTable') = 1)
drop table [dbo].[tblACH_Collection]
GO

CREATE TABLE [dbo].[tblACH_Collection] (
[AC_RECORDID] [int] IDENTITY (1, 1) NOT NULL ,
[AC_ABA_NUM] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AC_ACCTNUM] [nvarchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AC_CHECKNUM] [nvarchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AC_AMOUNT] [float] NULL ,
[AC_TRACKING] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_NAME] [nvarchar] (22) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_C_S_IND] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_REMARKS] [ntext] COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_COMP_ID] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_RETRY_SW] [bit] NULL ,
[AC_FEE_ONLY] [bit] NULL ,
[INDICATORS] [nvarchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_FACE_OK_SW] [bit] NULL ,
[AC_FEE_OK_SW] [bit] NULL ,
[AC_HOLD_SW] [bit] NULL ,
[AC_DONE_SW] [bit] NULL ,
[AC_MIO_SW] [bit] NULL ,
[WhoWhen1] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen2] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen3] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen4] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen5] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--------------------------------------------------
tblACH_Collection Data
--------------------------------------------------
15960,"000000000","000000","383",14.98,"","NANCY
BOYLE","C","12/17/03 DO NOT PROCESS; RECORD
ONLY","ZLU",False,False,"IZLUGMS2003351",False,False,True,T
rue,False,"","","","",""
14528,"000000000","000000000","8991",11.800000000000001,"",
"LARRY KNUTSON","C","11/17/03 DO NOT PROCESS; RECORD
ONLY","Z5A",False,False,"IZ5AGMS2003321",False,False,True,T
rue,False,"","","","",""
13046,"000000000","0000000000","2012",23.5,"","JENNIFER
VANWAGNER","C","10/20/03 DO NOT PROCESS; RECORD
ONLY","Z3M",False,False,"IZ3MGMS2003293",False,False,True,T
rue,False,"","","","",""
16020,"000000000","0000000","1683",18.079999999999998,"","R
OBERT LUEBBEN","C","12/18/03 DO NOT PROCESS; RECORD
ONLY","ZTC",False,False,"IZTCGMS2003352",False,False,True,T
rue,False,"","","","",""
16004,"000000000","0000000000","5124",35,"","MICHAEL
AVEY","C","12/17/03 DO NOT PROCESS; RECORD
ONLY","Z3M",False,False,"IZ3MGMS2003351",False,False,True,T
rue,False,"","","","",""
16022,"000000000","0000000","2063",19.329999999999998,"","K
EVIN KLEMMENSEN","C","12/18/03 DO NOT PROCESS; RECORD
ONLY","ZTC",False,False,"IZTCGMS2003352",False,False,True,T
rue,False,"","","","",""
13048,"000000000","0000000","17735",10,"","SHIRLEY
SPICKLER","C","10/20/03 DO NOT PROCESS; RECORD
ONLY","ZBW",False,False,"IZBWGMS2003293",False,False,True,T
rue,False,"","","","",""
18325,"000000000","000000","5409",19.02,"","VIRGINIA
STUBBE","C","01/27/04 DO NOT PROCESS; RECORD
ONLY","ZX0",False,False,"IZX0GMS2004027",False,False,True,T
rue,False,"","","","",""
15643,"000000000","0000000000","2269",59.990000000000002,""
,"ADA WOEHRLE","C","12/09/03 DO NOT PROCESS; RECORD
ONLY","YTC",False,False,"IYTCGMS2003343",False,False,True,T
rue,False,"","","","",""
14078,"000000000","000000","122",187.69,"","CHRISTINE
EAGLES","C","11/12/03 DO NOT PROCESS; RECORD
ONLY","ZLU",False,False,"IZLUGMS2003316",False,False,True,T
rue,False,"","","","",""
14531,"000000000","00000000","7165",11.890000000000001,"","
R E POYTNER INC","C","11/17/03 DO NOT PROCESS; RECORD
ONLY","ZB8",False,False,"IZB8GMS2003321",False,False,True,T
rue,False,"","","","",""
15973,"000000000","0000000000","5185",25.050000000000001,""
,"MICHAEL AVEY","C","12/17/03 DO NOT PROCESS; RECORD
ONLY","ZEL",False,False,"IZELGMS2003351",False,False,True,T
rue,False,"","","","",""
18310,"000000000","000000","1151",17.809999999999999,"","HE
ATHER PETERSEN","C","01/27/04 DO NOT PROCESS; RECORD
ONLY","ZX0",False,False,"IZX0GMS2004027",False,False,True,T
rue,False,"","","","",""
14145,"000000000","000000000","9571",11.99,"","ROBERT
ZOELLNER","C","11/13/03 DO NOT PROCESS; RECORD
ONLY","ZQE",False,False,"IZQEGMS2003317",False,False,True,T
rue,False,"","","","",""
13378,"000000000","0000000000","3084",35,"","LEAANNE
SEMMENS","C","10/29/03 DO NOT PROCESS; RECORD
ONLY","ZO6",False,False,"IZO6GMS2003302",False,False,True,T
rue,False,"","","","",""
15515,"000000000","000000","1292",15,"","JOSHUA
GEIER","C","12/08/03 DO NOT PROCESS; RECORD
ONLY","ZNO",False,False,"IZNOGMS2003342",False,False,True,T
rue,False,"","","","",""
15693,"000000000","0000","9395",137.84999999999999,"","TERE
SA PETERSON","C","12/09/03 DO NOT PROCESS; RECORD
ONLY","ZQS",False,False,"IZQSGMS2003343",False,False,True,T
rue,False,"","","","",""
14485,"000000000","0000000","1214",34.450000000000003,"","A
NDRE PHILLIPS","C","11/17/03 DO NOT PROCESS; RECORD
ONLY","ZNO",False,False,"IZNOGMS2003321",False,False,True,T
rue,False,"","","","",""
16024,"000000000","0000000","1105",37.68,"","TONY
COWANS","C","12/18/03 DO NOT PROCESS; RECORD
ONLY","ZTC",False,False,"IZTCGMS2003352",False,False,True,T
rue,False,"","","","",""
12361,"000000000","000000","1138",43.090000000000003,"","RA
CHEL HUDDLESTON","C","10/03/03 DO NOT PROCESS; RECORD
ONLY","YGK",False,False,"IYGKGMS2003279",False,False,True,T
rue,False,"","","","",""
14164,"000000000","000000","6907",100,"","THOMAS
PREAM","C","11/13/03 DO NOT PROCESS; RECORD
ONLY","ZDG",False,False,"IZDGGMS2003317",False,False,True,T
rue,False,"","","","",""
12817,"000000000","000000","4064",41,"","SABRINA
MUDEK","C","10/16/03 DO NOT PROCESS; RECORD
ONLY","ZM0",False,False,"IZM0GMS2003289",False,False,True,T
rue,False,"","","","",""
**************************************************
 
Make sure all tables have a Key field in SQL.
Are opening this in a form? If so make sure you set the Unique Table
property for the form. You may want to review the info in Help regarding
the Unique table setting.

I always try and use subforms if the data is in different tables. It seems
to be cleaner and work much better this way.

I converted our app a couple of years ago and ended up changing the way our
app looks at data in several ways in order to get the speed benefit from
SQL. In the end it has definately been worth it.

HTH,
J. Clay

Gerard said:
Hey all,
I am running SQL 2k on Win 2k Server. We are
migrating from an Access app to a VB app. In the mean
time, we are using SQL Server as a backend for our Access
app. When certain Access queries run against SQL Server,
the Datasheet that comes back can not have records added
to it. Some almost identical queries come back fully
updateable. I've checked table permissions and that
doesn't seem to be the problem. Identical connection
strings make no difference, any ideas. Thanks in advance,
any help is appreciated. Below I have included the
queries in question with the table DDL and some data.

Gerard


*****************************************************
tblACH_Batch
-----------------------------------------------------
SQL Query(this is the query that isn't updateble)
-----------------------------------------------------
SELECT tblACH_Batch.AB_ABA_NUM, tblACH_Batch.AB_ACCTNUM,
tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
tblACH_Batch.AB_REMARKS, tblACH_Batch.AB_SCHED,
tblACH_Batch.AB_COMP_ID, tblACH_Batch.AB_ONCE_SW,
tblACH_Batch.AB_RETRY, tblACH_Batch.[INDICATORS],
tblACH_Batch.AB_PERIODS, tblACH_Company.COMP_NAME,
tblACH_Company.RETRY_AMOUNT
FROM tblACH_Batch LEFT JOIN tblACH_Company ON
tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
ORDER BY tblACH_Batch.AB_COMP_ID;

-----------------------------------------------------
tblACH_Batch DDL
-----------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblACH_Batch]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblACH_Batch]
GO

CREATE TABLE [dbo].[tblACH_Batch] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AB_ABA_NUM] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_ACCTNUM] [nvarchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_AMOUNT] [float] NULL ,
[AB_TRACK] [nvarchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_NAME] [nvarchar] (22) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_C_S_IND] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_BEG_DTE] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_END_DTE] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_REMARKS] [nvarchar] (4000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_SCHED] [nvarchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_COMP_ID] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_ONCE_SW] [bit] NULL ,
[INDICATORS] [nvarchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AB_PERIODS] [float] NULL ,
[AB_RETRY] [bit] NULL ,
[WhoWhen1] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen2] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen3] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen4] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen5] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

---------------------------------------------------
tblACH_Batch Data
---------------------------------------------------
1518,"000000000","0000000000",19,"5290911","STARR
COLE","C","1998/04/28","","06/04/03 C03 RETURN; CHANGED
ABA & ACCT #
02/03/03 R01 RETURN; JAN $ NOT COLLECTED
09/04/02 R01 RETURN
04/16/01 REACTIVATED W/NEW ACCT # PER FAX
04/03/01 R02 ACCOUNT CLOSED
08/28/00 REACTIVATED ACCT PER FAX; NEW ACCT INFO
08/02/00 R02 ACCOUNT CLOSED
07/17/00 CHANGED AMT FROM $38 TO $19; ALSO CHANGED ACCOUNT
TO BE UNDER STARR COLE ONLY PER FAX'ED REQUEST
01/26/99 NEW BANK INFO. PER FAX","M280","MT3",False,"
MT3GMS20031552004119",0,False,"","","","",""
1520,"000000000","0000000",25,"6324044","ANDREA
KINNEAR","S","1999/07/31","","7/19/99 REOPENED ACCT PER
FAX","M310","MAX",False,"IMAXGMS20022172002212",0,False,"",
"","","",""
1526,"000000000","0000000000",31,"8891510","BONITA
CANOVA","C","1998/05/01","2002/01/31","","M010","NBH",False
,"iNBHGMS19980972002032Expired
Record!",0,False,"","","","",""
1527,"000000000","0000000000",31,"8867110","ELIZABETH
BLEVIN","C","1998/04/23","2002/01/31","11/29/01 CANCELED
PER PHONE-BECKY
(DID NOT COLLECT OCT)
11/05/01 REPROCESSED NSF + $5
10/04/01 REPROCESSED NSF + $5
(DID NOT COLLECT AUG)
09/06/01 REPROCESSED NSF + $5
08/01/00 REPROCESSED 1ST NSF
10/04/99 REPROCESSED FOR
NSF","M230","NBH",False,"INBHGMS20013332001327",0,False,"",
"","","",""
1531,"000000000","0000000000",21.149999999999999,"2516328",
"NICOLE O'NEAL","C","1998/05/01","","03/07/01 CHANGED BANK
INFO PER FAX
3/22/99 CHG AMT PER
FAX","M010","CT0",False,"ICT0GMS20022112002182",0,False,"",
"","","",""
1535,"000000000","0000000000",32.270000000000003,"6940961",
"CHARLIE VANDERFORD","C","2000/12/09","","11/20/00
REACTIVATED ACCT PER FAX (ALREADY PAID $32.27 9/9/99
THROUGH 10/9/00)
09/09/99 ACCT RENEWED BY FAX","M090","MFC",False,"
MFCGMS20003252004128",0,False,"","","","",""
1536,"000000000","0000000000",130,"4268341","OCAJ,
INC.","C","1998/03/20","","01/19/99 CANCELLED PER FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1537,"000000000","00000000",75,"4399530","EDWARD
ARNOLD","C","1998/04/20","","01/19/99 CANCELLED PER FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1538,"000000000","0000000",45,"8857883","HULEC
ENTERPRISES","C","1998/05/20","","01/19/99 CANCELLED PER
FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1539,"000000000","0000000",250,"8857883","SURETY PROPERTY
MGMT.","C","1998/05/20","","01/19/99 CANCELLED PER FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1540,"000000000","00000000",145,"3736113","NATIONWIDE
PROP. MGMT.","C","1998/04/20","","01/19/99 CANCELLED PER
FAX'ED
REQUEST","M200","E02",False,"IE02GMS19990191998352",0,False
,"","","","",""
1543,"000000000","00000000000000000",19,"9874450","MICHELLE
MALONEY","C","1998/05/14","","07/16/99 CHANGED ABA & ACCT
#'S PER RETURN","M140","MT1",False,"
MT1GMS19991972004105",0,False,"","","","",""
1545,"000000000","0000000000",19,"8893869","LYDIA
DONALSON","C","1998/05/07","","07/09/03 CANCEL PER FAX
8/11/99 REACTIVATE FOR 9/7/99 PER
FAX","M070","MT4",False,"IMT4GMS20031902003188",0,False,"",
"","","",""
***************************************************


***************************************************
tblACH_Collection
---------------------------------------------------
SQL Query(this query is updateable)
---------------------------------------------------
SELECT tblACH_Collection.AC_RECORDID,
tblACH_Collection.AC_ABA_NUM,
tblACH_Collection.AC_ACCTNUM, tblACH_Collection.AC_AMOUNT,
tblACH_Collection.AC_CHECKNUM,
tblACH_Collection.AC_TRACKING, tblACH_Collection.AC_NAME,
tblACH_Collection.AC_C_S_IND,
tblACH_Collection.AC_REMARKS,
tblACH_Collection.AC_COMP_ID,
tblACH_Collection.AC_RETRY_SW,
tblACH_Collection.AC_FEE_ONLY,
tblACH_Collection.INDICATORS,
tblACH_Collection.AC_FACE_OK_SW,
tblACH_Collection.AC_FEE_OK_SW,
tblACH_Collection.AC_HOLD_SW,
tblACH_Collection.AC_DONE_SW, tblACH_Collection.AC_MIO_SW,
tblACH_Company.COMP_NAME
FROM tblACH_Collection LEFT JOIN tblACH_Company ON
tblACH_Collection.AC_COMP_ID = tblACH_Company.[KEY]
ORDER BY tblACH_Collection.AC_COMP_ID;

----------------------------------------------------
tblACH_Collection DDL
----------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblACH_Collection]') and OBJECTPROPERTY
(id, N'IsUserTable') = 1)
drop table [dbo].[tblACH_Collection]
GO

CREATE TABLE [dbo].[tblACH_Collection] (
[AC_RECORDID] [int] IDENTITY (1, 1) NOT NULL ,
[AC_ABA_NUM] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AC_ACCTNUM] [nvarchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AC_CHECKNUM] [nvarchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AC_AMOUNT] [float] NULL ,
[AC_TRACKING] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_NAME] [nvarchar] (22) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_C_S_IND] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_REMARKS] [ntext] COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_COMP_ID] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_RETRY_SW] [bit] NULL ,
[AC_FEE_ONLY] [bit] NULL ,
[INDICATORS] [nvarchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AC_FACE_OK_SW] [bit] NULL ,
[AC_FEE_OK_SW] [bit] NULL ,
[AC_HOLD_SW] [bit] NULL ,
[AC_DONE_SW] [bit] NULL ,
[AC_MIO_SW] [bit] NULL ,
[WhoWhen1] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen2] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen3] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen4] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[WhoWhen5] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--------------------------------------------------
tblACH_Collection Data
--------------------------------------------------
15960,"000000000","000000","383",14.98,"","NANCY
BOYLE","C","12/17/03 DO NOT PROCESS; RECORD
ONLY","ZLU",False,False,"IZLUGMS2003351",False,False,True,T
rue,False,"","","","",""
14528,"000000000","000000000","8991",11.800000000000001,"",
"LARRY KNUTSON","C","11/17/03 DO NOT PROCESS; RECORD
ONLY","Z5A",False,False,"IZ5AGMS2003321",False,False,True,T
rue,False,"","","","",""
13046,"000000000","0000000000","2012",23.5,"","JENNIFER
VANWAGNER","C","10/20/03 DO NOT PROCESS; RECORD
ONLY","Z3M",False,False,"IZ3MGMS2003293",False,False,True,T
rue,False,"","","","",""
16020,"000000000","0000000","1683",18.079999999999998,"","R
OBERT LUEBBEN","C","12/18/03 DO NOT PROCESS; RECORD
ONLY","ZTC",False,False,"IZTCGMS2003352",False,False,True,T
rue,False,"","","","",""
16004,"000000000","0000000000","5124",35,"","MICHAEL
AVEY","C","12/17/03 DO NOT PROCESS; RECORD
ONLY","Z3M",False,False,"IZ3MGMS2003351",False,False,True,T
rue,False,"","","","",""
16022,"000000000","0000000","2063",19.329999999999998,"","K
EVIN KLEMMENSEN","C","12/18/03 DO NOT PROCESS; RECORD
ONLY","ZTC",False,False,"IZTCGMS2003352",False,False,True,T
rue,False,"","","","",""
13048,"000000000","0000000","17735",10,"","SHIRLEY
SPICKLER","C","10/20/03 DO NOT PROCESS; RECORD
ONLY","ZBW",False,False,"IZBWGMS2003293",False,False,True,T
rue,False,"","","","",""
18325,"000000000","000000","5409",19.02,"","VIRGINIA
STUBBE","C","01/27/04 DO NOT PROCESS; RECORD
ONLY","ZX0",False,False,"IZX0GMS2004027",False,False,True,T
rue,False,"","","","",""
15643,"000000000","0000000000","2269",59.990000000000002,""
,"ADA WOEHRLE","C","12/09/03 DO NOT PROCESS; RECORD
ONLY","YTC",False,False,"IYTCGMS2003343",False,False,True,T
rue,False,"","","","",""
14078,"000000000","000000","122",187.69,"","CHRISTINE
EAGLES","C","11/12/03 DO NOT PROCESS; RECORD
ONLY","ZLU",False,False,"IZLUGMS2003316",False,False,True,T
rue,False,"","","","",""
14531,"000000000","00000000","7165",11.890000000000001,"","
R E POYTNER INC","C","11/17/03 DO NOT PROCESS; RECORD
ONLY","ZB8",False,False,"IZB8GMS2003321",False,False,True,T
rue,False,"","","","",""
15973,"000000000","0000000000","5185",25.050000000000001,""
,"MICHAEL AVEY","C","12/17/03 DO NOT PROCESS; RECORD
ONLY","ZEL",False,False,"IZELGMS2003351",False,False,True,T
rue,False,"","","","",""
18310,"000000000","000000","1151",17.809999999999999,"","HE
ATHER PETERSEN","C","01/27/04 DO NOT PROCESS; RECORD
ONLY","ZX0",False,False,"IZX0GMS2004027",False,False,True,T
rue,False,"","","","",""
14145,"000000000","000000000","9571",11.99,"","ROBERT
ZOELLNER","C","11/13/03 DO NOT PROCESS; RECORD
ONLY","ZQE",False,False,"IZQEGMS2003317",False,False,True,T
rue,False,"","","","",""
13378,"000000000","0000000000","3084",35,"","LEAANNE
SEMMENS","C","10/29/03 DO NOT PROCESS; RECORD
ONLY","ZO6",False,False,"IZO6GMS2003302",False,False,True,T
rue,False,"","","","",""
15515,"000000000","000000","1292",15,"","JOSHUA
GEIER","C","12/08/03 DO NOT PROCESS; RECORD
ONLY","ZNO",False,False,"IZNOGMS2003342",False,False,True,T
rue,False,"","","","",""
15693,"000000000","0000","9395",137.84999999999999,"","TERE
SA PETERSON","C","12/09/03 DO NOT PROCESS; RECORD
ONLY","ZQS",False,False,"IZQSGMS2003343",False,False,True,T
rue,False,"","","","",""
14485,"000000000","0000000","1214",34.450000000000003,"","A
NDRE PHILLIPS","C","11/17/03 DO NOT PROCESS; RECORD
ONLY","ZNO",False,False,"IZNOGMS2003321",False,False,True,T
rue,False,"","","","",""
16024,"000000000","0000000","1105",37.68,"","TONY
COWANS","C","12/18/03 DO NOT PROCESS; RECORD
ONLY","ZTC",False,False,"IZTCGMS2003352",False,False,True,T
rue,False,"","","","",""
12361,"000000000","000000","1138",43.090000000000003,"","RA
CHEL HUDDLESTON","C","10/03/03 DO NOT PROCESS; RECORD
ONLY","YGK",False,False,"IYGKGMS2003279",False,False,True,T
rue,False,"","","","",""
14164,"000000000","000000","6907",100,"","THOMAS
PREAM","C","11/13/03 DO NOT PROCESS; RECORD
ONLY","ZDG",False,False,"IZDGGMS2003317",False,False,True,T
rue,False,"","","","",""
12817,"000000000","000000","4064",41,"","SABRINA
MUDEK","C","10/16/03 DO NOT PROCESS; RECORD
ONLY","ZM0",False,False,"IZM0GMS2003289",False,False,True,T
rue,False,"","","","",""
**************************************************
Click to expand...
 
Back
Top