February 6, 2004 at 9:32 am
Here is the sql for the query:
SELECT tblVarianceAmt.[Pat Name], tblVarianceAmt.[Acct Nbr], tblVarianceAmt.[Guar SSN], tblVarianceAmt.DRG, tblVarianceAmt.[Pat Type], tblVarianceAmt.[Ins Cd], tblVarianceAmt.Insurance, tblVarianceAmt.[Tot Chgs], tblVarianceAmt.[Exp Reim], tblVarianceAmt.[Ins Paymt], tblVarianceAmt.[Pat Paymt], tblVarianceAmt.CoInsDeduct, tblVarianceAmt.Variance, tblVarianceAmt.Comments, tblVarianceAmt.Rep, tblVarianceAmt.[Acct Appealed], tblVarianceAmt.[Assigned Date], tblVarianceAmt.[Appeal Reason], tblVarianceAmt.[Not Appeal Reason], tblVarianceAmt.[Appealed Variance], tblVarianceAmt.[Close Date], tblVarianceAmt.[Close Reason], tblVarianceAmt.[Close ID], tblVarianceAmt.ManuallyAdded, tblVarianceAmt.Poss_BD, tblVarianceAmt.[Subsequent Variance], tblVarianceAmt.[Misc Reason], tblVarianceAmt.Subsequent_Charges, tblVarianceAmt.[Appealed Online]
FROM tblVarianceAmt
WHERE (((tblVarianceAmt.Rep)=[forms]![Main]![department]) AND ((tblVarianceAmt.[Acct Appealed])=0) AND ((tblVarianceAmt.[Close Date])=#1/1/1999#));
and here is the table layout:
2 [Pat Name] nvarchar 50 0
2 [Acct Nbr] nvarchar 50 0
0 [Guar SSN] nvarchar 50 1
0 DRG nvarchar 50 1
0 [Pat Type] nvarchar 50 1
0 [Ins Cd] nvarchar 50 1
0 Insurance nvarchar 50 1
0 [Tot Chgs] float 8 1
0 [Exp Reim] float 8 1
0 [Ins Paymt] float 8 1
0 [Paymt DT] smalldatetime 4 1
0 [Pat Paymt] float 8 1
0 CoInsDeduct float 8 1
2 Variance float 8 0
0 Comments ntext 16 1
0 [Comment DateTime] smalldatetime 4 1
0 [Comment ID] nvarchar 50 1
0 Rep nvarchar 50 1
0 [Acct Appealed] float 8 1
0 [Appeal Reason] nvarchar 50 1
0 [Appealed Packet Created] bit 1 1
0 DateCreated smalldatetime 4 1
0 [Action Date] smalldatetime 4 1
0 [Action Type] nvarchar 50 1
0 [Contact Name] nvarchar 50 1
0 [Contact Number] nvarchar 50 1
0 [Assigned Date] smalldatetime 4 1
0 [Expected Outcome] nvarchar 50 1
0 [Next Follow Up] smalldatetime 4 1
0 [Expected Recovery Amount] float 8 1
0 [Close Date] smalldatetime 4 1
0 [Close Reason] nvarchar 50 1
0 [Close ID] nvarchar 50 1
0 [Appealed Variance] float 8 1
0 [Misc Reason] nvarchar 50 1
0 [Subsequent Variance] float 8 1
0 ManPlanCo nvarchar 50 1
0 ManPlanDesc nvarchar 50 1
0 ManContNbr nvarchar 50 1
0 ManContName nvarchar 50 1
0 ManEmployer nvarchar 50 1
0 ManGroup nvarchar 50 1
0 ManuallyAdded bit 1 1
0 Admit_DT smalldatetime 4 1
0 Disch_DT smalldatetime 4 1
0 Poss_BD bit 1 1
0 Policy_Number nvarchar 50 1
0 [Router's_ID] nvarchar 50 1
0 Routed_DT smalldatetime 4 1
0 FirstFollowUp_DT smalldatetime 4 1
0 [Additional Payment] float 8 1
0 Insured_Name nvarchar 50 1
0 [Manual_Pt_ Payment] float 8 1
0 FinClass nvarchar 50 1
0 Account_Bal float 8 1
0 Group_Number nvarchar 50 1
0 Bulk_Appeal bit 1 1
0 Subsequent_Charges float 8 1
0 [Not Appeal Reason] nvarchar 50 1
0 [Follow Up Date] smalldatetime 4 1
0 Facility nvarchar 50 1
1 [Appealed Online] nvarchar 50 1
Thanks
Danny
February 6, 2004 at 9:54 am
Cool. Very nice. (What's your question?)
-SQLBill
February 6, 2004 at 10:00 am
Why can't my query update a linked table? I have a query in MS Access that has a linked (ODBC) SQL Server table as it's source. To get the results I need the query uses three separate fields for criteria. The query returns the correct data set, however when any data is changed / updated in the query, a write conflict message occurs. The exact message is: "This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made." There are no other users, and the SQL tables have been created with at least one primary key.
If two of the criteria are removed the query then becomes updateable. Is this a limitation (only one criteria field) of a query using a linked SQL Server table?
Any help would be greatly appreciated.
Dan the man.
February 6, 2004 at 1:31 pm
Disclaimer: I don't use Access. I don't work with Linked Servers.
First thing I would check is the datatypes. Make sure they are compatible, i.e. Pat Name should be NVARCHAR in both Access and SQL Server (also make sure the length is the same).
Second, is there a TRIGGER that might be changing data?
-SQLBill
February 6, 2004 at 2:10 pm
Are you providing a value for those bit columns?
I have often seen that message when it comes to Access Yes/No fields respectively bit columns in SQL Server when there is no default value on those columns.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply