Error running append query.

What happens when you run just the SELECT portion of the query?

Hi,

What happens...

I see those X amount of records...
Thanks,
Ben

you are trying to append rows with null values in some field that is defined as Not Null

@Aikimark,
There are no columns defined as not null besides the ID which is not in use here.
Besides, when this is the case I get a message that some records were not imported due to rule violation...had that before and fixed all those.
Here is the create table script.

USE [SkilledNursingVisitNotes]
GO

/****** Object:  Table [dbo].[Skilled_Nursing_Visit_Note]    Script Date: 11/26/2018 19:40:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Skilled_Nursing_Visit_Note](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SNV_ID] [nvarchar](255) NULL,
	[DateEntered] [datetime] NULL,
	[VisitDateInt] [int] NULL,
	[Client_Last_Name] [nvarchar](255) NULL,
	[Client_Last_Name_Init] [nvarchar](5) NULL,
	[Visit_Date] [datetime] NULL,
	[Visit_Date_Init] [nvarchar](5) NULL,
	[Date_Of_Birth] [datetime2](0) NULL,
	[Medical_Record_Number] [float] NULL,
	[Shift_From_Hour] [int] NULL,
	[Shift_To_Hour] [int] NULL,
	[Purpose_Of_Visit_Goal] [nvarchar](max) NULL,
	[Temp] [nvarchar](max) NULL,
	[Pulse] [nvarchar](max) NULL,
	[RR] [nvarchar](max) NULL,
	[BP] [nvarchar](max) NULL,
	[O2_Saturation] [nvarchar](max) NULL,
	[O2_In_Use] [nvarchar](max) NULL,
	[Respiratory_Lung_Sounds] [nvarchar](max) NULL,
	[Respiratory_Breathing_Pattern] [nvarchar](max) NULL,
	[Bipap] [nvarchar](max) NULL,
	[Cpap] [nvarchar](max) NULL,
	[Vent] [nvarchar](max) NULL,
	[Vent_BIPAP_Settings] [nvarchar](max) NULL,
	[Cardiovascular_Skin_Color] [nvarchar](max) NULL,
	[Skin_Temp] [nvarchar](max) NULL,
	[Cardiovascular_Edema] [nvarchar](max) NULL,
	[Neuro_Alert] [nvarchar](max) NULL,
	[Neuro_Oriented] [nvarchar](max) NULL,
	[Neuro_Self_Directing] [nvarchar](max) NULL,
	[Neuro_Dev_Delay] [nvarchar](max) NULL,
	[Neuro_Deficits] [nvarchar](max) NULL,
	[Neuro_Notes] [nvarchar](max) NULL,
	[GI_Oral_Tube_Feeding] [nvarchar](max) NULL,
	[Current_Feeds] [nvarchar](max) NULL,
	[Gastric_Residuals_Present] [nvarchar](max) NULL,
	[Residual_Volume] [nvarchar](max) NULL,
	[MD_Called] [nvarchar](max) NULL,
	[Abdominal_Assessment] [nvarchar](max) NULL,
	[Feeding_Tolerance] [nvarchar](max) NULL,
	[GU_Incontinent] [nvarchar](max) NULL,
	[Number_Of_Diaper_Changes] [nvarchar](max) NULL,
	[Urine_Stool_Volume_Description] [nvarchar](max) NULL,
	[Bedbound] [nvarchar](max) NULL,
	[WheelChair] [nvarchar](max) NULL,
	[Ambulatory] [nvarchar](max) NULL,
	[Mobility_Aids] [nvarchar](max) NULL,
	[Contractures] [nvarchar](max) NULL,
	[Musculoskeletal_Notes] [nvarchar](max) NULL,
	[Pain_Score] [nvarchar](max) NULL,
	[Pain_Notes] [nvarchar](max) NULL,
	[Medication_Changes] [nvarchar](max) NULL,
	[Medication_Changes_2] [nvarchar](max) NULL,
	[Integument_Skin_Assessment] [nvarchar](max) NULL,
	[Skin_Care_Measures_Taken] [nvarchar](max) NULL,
	[Home_Environment_Safety] [nvarchar](max) NULL,
	[Treatments_Administered] [nvarchar](max) NULL,
	[Patient_Response] [nvarchar](max) NULL,
	[Instructions] [nvarchar](max) NULL,
	[Understanding] [nvarchar](max) NULL,
	[Plan_Goal] [nvarchar](max) NULL,
	[Nurse_Signature_Last_Name] [nvarchar](255) NULL,
	[Signature] [nvarchar](max) NULL,
	[Date_Signed] [datetime] NULL,
	[Nurse_Name_Stamp_SNV] [nvarchar](255) NULL,
	[Nurse_User_ID_num_SNV] [nvarchar](255) NULL,
	[Medication_Changes_Yes] [nvarchar](max) NULL,
	[Acknowledge_Accuracy] [nvarchar](255) NULL,
	[Trach] [nvarchar](255) NULL,
	[CCM_Informed] [nvarchar](255) NULL,
	[Tube_Type] [nvarchar](max) NULL,
	[Reviewed_Status] [bit] NULL,
	[Reviewed_By] [nvarchar](255) NULL,
	[Reviewed_stamp] [datetime] NULL,
	[Client_First_Name] [nvarchar](255) NULL,
	[Client_First_Name_Init] [nvarchar](5) NULL,
	[Nurse_Signature_First_Name] [nvarchar](255) NULL,
	[Patient_Unable_To_Sign] [bit] NULL,
	[Reason] [nvarchar](255) NULL,
	[Shift_From_Minute] [int] NULL,
	[Shift_To_Minute] [int] NULL,
	[Shift_From_Init] [nvarchar](5) NULL,
	[Shift_To_Init] [nvarchar](5) NULL,
	[Status] [nvarchar](50) NULL,
	[ts] [timestamp] NULL,
 CONSTRAINT [PK_Skilled_Nursing_Visit_Note] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Select allOpen in new window

Thanks,
Ben

Ben,

Since you posted the SQL for creating the table, I assume you have access to SQL Server via SSMS.

How are you running this query, as a simple query within Access, or is it a pass-through query?

If you copy the query to SSMS, will it run?  If not, it might provide you with a better error message than what you are getting in Access.

Dale

@Dale,
Running it from Access, so may try that and will let you know...
was trying to avoid that as source data is not in SQL, guess will have to import it first, no choice...
Thanks,
Ben

Well, I assumed the data was in SQL Server.

Where is the source data (the SELECT part of the query) coming from?

I see you have both a Timestamp and a PK in the destination table, which is generally all you need to be able to write to the table.  Can you create an append query that looks something like:

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID)
Values("Test")

and does that run?  Since all of the fields in that table are Nullable, that query should work.  But I wonder whether SNV_ID, which is defined as nvarchar(255) in your table definition script above, is actually a string, or is a numeric value in the original code.  This might be the problem with many of the fields in your original data source and the destination table.

Dale

My bad.  The error stated that "33 fields" were converted to Null due to type conversion problems.  You'll need to look at the data types you are inserting.  Too bad the error message wasn't more specific.  You could look at the post-append table, looking for null field values.

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID)
Values("Test")

This works.

The error stated that "33 fields"

It doesn't look like the problem is related to that error, as messages ststed " converted those fields...", and in fact no records are being inserted whatsoever...

Can yiu share some data ?

Have to create some test, but that will not be the same...

is there a way to check that error on SQL Profiler?
what events should be selected?

Thanks,
Ben

Well, your columns all line up and match one for one in terms of names.....

So, can only be a mismatch in column datatypes and length. Also, old habits die hard and prefer to see a left join and check for NULL (or use the NOT EXISTS construct)

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID, Client_Last_Name, Visit_Date, Date_Of_Birth, Medical_Record_Number, Shift_From_Hour, Shift_To_Hour, Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, Vent_BIPAP_Settings, Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, Neuro_Notes, GI_Oral_Tube_Feeding, Current_Feeds, Gastric_Residuals_Present, Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, Number_Of_Diaper_Changes, Urine_Stool_Volume_Description, Bedbound, WheelChair, Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, Pain_Notes, Medication_Changes, Medication_Changes_2, Integument_Skin_Assessment, Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, Patient_Response, Instructions, Understanding, Plan_Goal, Nurse_Signature_Last_Name, Signature, Date_Signed, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, Reviewed_Status, Reviewed_By, Reviewed_stamp, Client_First_Name, Nurse_Signature_First_Name, Shift_From_Minute, Shift_To_Minute, Patient_Unable_To_Sign, Reason, Status )
SELECT V_Visit_Note_Export.SNV_ID, V_Visit_Note_Export.Client_Last_Name, V_Visit_Note_Export.Visit_Date, V_Visit_Note_Export.Date_Of_Birth, V_Visit_Note_Export.Medical_Record_Number, V_Visit_Note_Export.Shift_From_Hour, V_Visit_Note_Export.Shift_To_Hour, V_Visit_Note_Export.Purpose_Of_Visit_Goal, V_Visit_Note_Export.Temp, V_Visit_Note_Export.Pulse, V_Visit_Note_Export.RR, V_Visit_Note_Export.BP, V_Visit_Note_Export.O2_Saturation, V_Visit_Note_Export.O2_In_Use, V_Visit_Note_Export.Respiratory_Lung_Sounds, V_Visit_Note_Export.Respiratory_Breathing_Pattern, V_Visit_Note_Export.Bipap, V_Visit_Note_Export.Cpap, V_Visit_Note_Export.Vent, V_Visit_Note_Export.Vent_BIPAP_Settings, V_Visit_Note_Export.Cardiovascular_Skin_Color, V_Visit_Note_Export.Skin_Temp, V_Visit_Note_Export.Cardiovascular_Edema, V_Visit_Note_Export.Neuro_Alert, V_Visit_Note_Export.Neuro_Oriented, V_Visit_Note_Export.Neuro_Self_Directing, V_Visit_Note_Export.Neuro_Dev_Delay, V_Visit_Note_Export.Neuro_Deficits, V_Visit_Note_Export.Neuro_Notes, V_Visit_Note_Export.GI_Oral_Tube_Feeding, V_Visit_Note_Export.Current_Feeds, V_Visit_Note_Export.Gastric_Residuals_Present, V_Visit_Note_Export.Residual_Volume, V_Visit_Note_Export.MD_Called, V_Visit_Note_Export.Abdominal_Assessment, V_Visit_Note_Export.Feeding_Tolerance, V_Visit_Note_Export.GU_Incontinent, V_Visit_Note_Export.Number_Of_Diaper_Changes, V_Visit_Note_Export.Urine_Stool_Volume_Description, V_Visit_Note_Export.Bedbound, V_Visit_Note_Export.WheelChair, V_Visit_Note_Export.Ambulatory, V_Visit_Note_Export.Mobility_Aids, V_Visit_Note_Export.Contractures, V_Visit_Note_Export.Musculoskeletal_Notes, V_Visit_Note_Export.Pain_Score, V_Visit_Note_Export.Pain_Notes, V_Visit_Note_Export.Medication_Changes, V_Visit_Note_Export.Medication_Changes_2, V_Visit_Note_Export.Integument_Skin_Assessment, V_Visit_Note_Export.Skin_Care_Measures_Taken, V_Visit_Note_Export.Home_Environment_Safety, V_Visit_Note_Export.Treatments_Administered, V_Visit_Note_Export.Patient_Response, V_Visit_Note_Export.Instructions, V_Visit_Note_Export.Understanding, V_Visit_Note_Export.Plan_Goal, V_Visit_Note_Export.Nurse_Signature_Last_Name, V_Visit_Note_Export.Signature, V_Visit_Note_Export.Date_Signed, V_Visit_Note_Export.Nurse_Name_Stamp_SNV, V_Visit_Note_Export.Nurse_User_ID_num_SNV, V_Visit_Note_Export.Medication_Changes_Yes, V_Visit_Note_Export.Acknowledge_Accuracy, V_Visit_Note_Export.Trach, V_Visit_Note_Export.CCM_Informed, V_Visit_Note_Export.Tube_Type, V_Visit_Note_Export.Reviewed_Status, V_Visit_Note_Export.Reviewed_By, V_Visit_Note_Export.Reviewed_stamp, V_Visit_Note_Export.Client_First_Name, V_Visit_Note_Export.Nurse_Signature_First_Name, V_Visit_Note_Export.Shift_From_Minute, V_Visit_Note_Export.Shift_To_Minute, V_Visit_Note_Export.Patient_Unable_To_Sign, V_Visit_Note_Export.Reason, V_Visit_Note_Export.Status
FROM V_Visit_Note_Export left join ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
WHERE Skilled_Nursing_Visit_Note.SNV_ID Is Null 
AND (((V_Visit_Note_Export.Status) Is Null Or (V_Visit_Note_Export.Status)<>"draft")
 OR ((V_Visit_Note_Export.Status)="draft" AND (V_Visit_Note_Export.Deleted)=True));

Select allOpen in new window

Can we see a data definition for V_Visit_Note_Export ?

>> Have to create some test, but that will not be the same...

So long as it fails the same way :)

So, can only be a mismatch in column datatypes

Likely this is the cause

Can we see a data definition for V_Visit_Note_Export ?

See attached.
However still wondering why no error stating whats violating rule and preventing insert...
Thanks,
Ben
Untitled.png

OK, conformed Mark was right, the problem lays on data definition, as when tried omitting those date fields, it worked.
Now my question, what is the solution?!
Tried deleting the link and re-linking but it does not give me an option to specify date type definition.
File is in XLSX format (Excel).
Thanks,
Ben

Actually looks like that was only part of the problem...

Just tried converting those columns to date using CDate() as follows

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID, Client_Last_Name, Visit_Date, Date_Of_Birth, Medical_Record_Number, Shift_From_Hour, Shift_To_Hour, Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, Vent_BIPAP_Settings, Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, Neuro_Notes, GI_Oral_Tube_Feeding, Current_Feeds, Gastric_Residuals_Present, Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, Number_Of_Diaper_Changes, Urine_Stool_Volume_Description, Bedbound, WheelChair, Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, Pain_Notes, Medication_Changes, Medication_Changes_2, Integument_Skin_Assessment, Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, Patient_Response, Instructions, Understanding, Plan_Goal, Nurse_Signature_Last_Name, Signature, Date_Signed, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, Reviewed_Status, Reviewed_By, Reviewed_stamp, Client_First_Name, Nurse_Signature_First_Name, Shift_From_Minute, Shift_To_Minute, Patient_Unable_To_Sign, Reason, Status )
SELECT V_Visit_Note_Export.SNV_ID, V_Visit_Note_Export.Client_Last_Name, CDate([V_Visit_Note_Export].[Visit_Date]) AS Expr1, CDate([V_Visit_Note_Export].[Date_Of_Birth]) AS Expr2, V_Visit_Note_Export.Medical_Record_Number, V_Visit_Note_Export.Shift_From_Hour, V_Visit_Note_Export.Shift_To_Hour, V_Visit_Note_Export.Purpose_Of_Visit_Goal, V_Visit_Note_Export.Temp, V_Visit_Note_Export.Pulse, V_Visit_Note_Export.RR, V_Visit_Note_Export.BP, V_Visit_Note_Export.O2_Saturation, V_Visit_Note_Export.O2_In_Use, V_Visit_Note_Export.Respiratory_Lung_Sounds, V_Visit_Note_Export.Respiratory_Breathing_Pattern, V_Visit_Note_Export.Bipap, V_Visit_Note_Export.Cpap, V_Visit_Note_Export.Vent, V_Visit_Note_Export.Vent_BIPAP_Settings, V_Visit_Note_Export.Cardiovascular_Skin_Color, V_Visit_Note_Export.Skin_Temp, V_Visit_Note_Export.Cardiovascular_Edema, V_Visit_Note_Export.Neuro_Alert, V_Visit_Note_Export.Neuro_Oriented, V_Visit_Note_Export.Neuro_Self_Directing, V_Visit_Note_Export.Neuro_Dev_Delay, V_Visit_Note_Export.Neuro_Deficits, V_Visit_Note_Export.Neuro_Notes, V_Visit_Note_Export.GI_Oral_Tube_Feeding, V_Visit_Note_Export.Current_Feeds, V_Visit_Note_Export.Gastric_Residuals_Present, V_Visit_Note_Export.Residual_Volume, V_Visit_Note_Export.MD_Called, V_Visit_Note_Export.Abdominal_Assessment, V_Visit_Note_Export.Feeding_Tolerance, V_Visit_Note_Export.GU_Incontinent, V_Visit_Note_Export.Number_Of_Diaper_Changes, V_Visit_Note_Export.Urine_Stool_Volume_Description, V_Visit_Note_Export.Bedbound, V_Visit_Note_Export.WheelChair, V_Visit_Note_Export.Ambulatory, V_Visit_Note_Export.Mobility_Aids, V_Visit_Note_Export.Contractures, V_Visit_Note_Export.Musculoskeletal_Notes, V_Visit_Note_Export.Pain_Score, V_Visit_Note_Export.Pain_Notes, V_Visit_Note_Export.Medication_Changes, V_Visit_Note_Export.Medication_Changes_2, V_Visit_Note_Export.Integument_Skin_Assessment, V_Visit_Note_Export.Skin_Care_Measures_Taken, V_Visit_Note_Export.Home_Environment_Safety, V_Visit_Note_Export.Treatments_Administered, V_Visit_Note_Export.Patient_Response, V_Visit_Note_Export.Instructions, V_Visit_Note_Export.Understanding, V_Visit_Note_Export.Plan_Goal, V_Visit_Note_Export.Nurse_Signature_Last_Name, V_Visit_Note_Export.Signature, CDate([V_Visit_Note_Export].[Date_Signed]) AS Expr3, V_Visit_Note_Export.Nurse_Name_Stamp_SNV, V_Visit_Note_Export.Nurse_User_ID_num_SNV, V_Visit_Note_Export.Medication_Changes_Yes, V_Visit_Note_Export.Acknowledge_Accuracy, V_Visit_Note_Export.Trach, V_Visit_Note_Export.CCM_Informed, V_Visit_Note_Export.Tube_Type, V_Visit_Note_Export.Reviewed_Status, V_Visit_Note_Export.Reviewed_By, V_Visit_Note_Export.Reviewed_stamp, V_Visit_Note_Export.Client_First_Name, V_Visit_Note_Export.Nurse_Signature_First_Name, V_Visit_Note_Export.Shift_From_Minute, V_Visit_Note_Export.Shift_To_Minute, V_Visit_Note_Export.Patient_Unable_To_Sign, V_Visit_Note_Export.Reason, V_Visit_Note_Export.Status
FROM Skilled_Nursing_Visit_Note RIGHT JOIN V_Visit_Note_Export ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
WHERE (((V_Visit_Note_Export.Status) Is Null Or (V_Visit_Note_Export.Status)<>"draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null)) OR (((V_Visit_Note_Export.Status)="draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null) AND ((V_Visit_Note_Export.Deleted)=True));

Select allOpen in new window

And only some of those records were added, the rest are still waiting for a solution...

Thanks,
Ben

use format and put the dates into yyyy-mm-dd format

select Format([visit_date],"yyyy-mm-dd"),Format([date_of_birth],"yyyy-mm-dd")

Might also be an opportunity to clean up Hours and Minutes :)

If in XLSX file format, import directly via SQL Server, into a staging table (a temp table) then refine the columns / datatypes in SQL Server then do the insert from within SQL Server.

hi Mark,

select Format([visit_date],"yyyy-mm-dd"),Format([date_of_birth],"yyyy-mm-dd")

The format function as is will return a string which i cannot insert into a date/time sql field, are you intending I use CDate() on top of that?

Might also be an opportunity to clean up Hours and Minutes :)

Not sure how that will cleanup wrong entries, besides I cannot cleanup anything, data is being imported from another source and must be kept untouched.

If in XLSX file format, import directly via SQL Server, into a staging table (a temp table) then refine the columns / datatypes in SQL Server then do the insert from within SQL Server.

Are you suggesting this as a way to test it (similar to what Dale posted above)?

Thanks,
Ben

Format([visit_date],"yyyy-mm-dd"),Format([date_of_birth],"yyyy-mm-dd")

Tried that, and this also solved some record, however the majority are still not getting inserted...
(the cdate() solved 20% and this another 5%)

Thanks,
Ben

This should be a more readable code snippet:

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID, Client_Last_Name, Visit_Date, 
Date_Of_Birth, Medical_Record_Number, Shift_From_Hour, Shift_To_Hour, 
Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, 
Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, 
Vent_BIPAP_Settings, Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, 
Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, 
Neuro_Notes, GI_Oral_Tube_Feeding, Current_Feeds, Gastric_Residuals_Present, 
Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, 
Number_Of_Diaper_Changes, Urine_Stool_Volume_Description, Bedbound, WheelChair, 
Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, 
Pain_Notes, Medication_Changes, Medication_Changes_2, Integument_Skin_Assessment, 
Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, 
Patient_Response, Instructions, Understanding, Plan_Goal, Nurse_Signature_Last_Name, 
Signature, Date_Signed, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, 
Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, 
Reviewed_Status, Reviewed_By, Reviewed_stamp, Client_First_Name, 
Nurse_Signature_First_Name, Shift_From_Minute, Shift_To_Minute, 
Patient_Unable_To_Sign, Reason, Status )

SELECT VNE.SNV_ID, VNE.Client_Last_Name, CDate([VNE].[Visit_Date]) AS DtVisit, 
CDate([VNE].[Date_Of_Birth]) AS DOB, VNE.Medical_Record_Number, 
VNE.Shift_From_Hour, 
VNE.Shift_To_Hour, VNE.Purpose_Of_Visit_Goal, VNE.Temp, VNE.Pulse, VNE.RR, VNE.BP,  VNE.O2_Saturation, VNE.O2_In_Use, VNE.Respiratory_Lung_Sounds, 
VNE.Respiratory_Breathing_Pattern, VNE.Bipap, VNE.Cpap, VNE.Vent, 
VNE.Vent_BIPAP_Settings, VNE.Cardiovascular_Skin_Color, VNE.Skin_Temp, 
VNE.Cardiovascular_Edema, VNE.Neuro_Alert, VNE.Neuro_Oriented, 
VNE.Neuro_Self_Directing, VNE.Neuro_Dev_Delay, VNE.Neuro_Deficits, 
VNE.Neuro_Notes, 
VNE.GI_Oral_Tube_Feeding, VNE.Current_Feeds, VNE.Gastric_Residuals_Present, 
VNE.Residual_Volume, VNE.MD_Called, VNE.Abdominal_Assessment, 
VNE.Feeding_Tolerance, 
VNE.GU_Incontinent, VNE.Number_Of_Diaper_Changes, 
VNE.Urine_Stool_Volume_Description, 
VNE.Bedbound, VNE.WheelChair, VNE.Ambulatory, VNE.Mobility_Aids, 
VNE.Contractures, 
VNE.Musculoskeletal_Notes, VNE.Pain_Score, VNE.Pain_Notes, 
VNE.Medication_Changes, 
VNE.Medication_Changes_2, VNE.Integument_Skin_Assessment, 
VNE.Skin_Care_Measures_Taken, VNE.Home_Environment_Safety, 
VNE.Treatments_Administered, VNE.Patient_Response, VNE.Instructions, 
VNE.Understanding, VNE.Plan_Goal, VNE.Nurse_Signature_Last_Name, VNE.Signature, 
CDate([VNE].[Date_Signed]) AS DtSigned, VNE.Nurse_Name_Stamp_SNV, 
VNE.Nurse_User_ID_num_SNV, VNE.Medication_Changes_Yes, VNE.Acknowledge_Accuracy, 
VNE.Trach, VNE.CCM_Informed, VNE.Tube_Type, VNE.Reviewed_Status, VNE.Reviewed_By, 
VNE.Reviewed_stamp, VNE.Client_First_Name, VNE.Nurse_Signature_First_Name, 
VNE.Shift_From_Minute, VNE.Shift_To_Minute, VNE.Patient_Unable_To_Sign, 
VNE.Reason, VNE.Status

FROM Skilled_Nursing_Visit_Note RIGHT JOIN V_Visit_Note_Export As VNE ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID 

WHERE (((VNE.Status) Is Null Or (VNE.Status)<>"draft") 
AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null)) 
OR (((VNE.Status)="draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null) 
AND ((VNE.Deleted)=True));

Select allOpen in new window

If you have null fields, you probably don't want to list them in this append query.
What are your primary key fields?

a character string correctly formatted in yyyy-mm-dd will insert into a date / datetime  column.

you also have a few BIT datatypes - might want to make sure they are cast as 1 or 0

you also have INT and FLOAT in there -make sure they are in fact numeric or null.

>> Are you suggesting this as a way to test it (similar to what Dale posted above)?

No, an alternative to pushing through via Access.


Ben,

Without seeing data it is a guessing game.

How is that sample set going ? The sample set has to represent the same column lengths and data types as the Access data set, and, has to fail the same way.

@Aikimark,
I'm getting syntax error in join operation.

This should be a more readable code snippet

I'm using Access designer, so regardless how neat you write it, next time it will look again like I posted...

If you have null fields, you probably don't want to list them in this append query.

I do need all records included (this one has nothing to do with the other thread, besides being from the same table-:)

@Mark,

No, an alternative to pushing through via Access.

Would not consider that for now, as it will involve more coding.

you also have a few BIT datatypes - might want to make sure they are cast as 1 or 0

Any idea how to accomplish that in Access query?

How is that sample set going ?

Will work on it and post, hopefully soon...

Thanks,
Ben

How is that sample set going ?

Do you know of an easy way to convert every character to "A" (so like this I dont need to count every note how long it is, and replace it with something else...)
e.g "flushed Pedisource administered Head to toe" should become "AAAAAAA AAAAAAAAA AAAAAAAAAAA AAAA AA AAA"
Thanks,
Ben

Attached sample data.
Thanks,
Ben
Book1.xlsx

Why are date fields being shown as short text?  Are they defined as datetime in the server table or something else?  Are you using the correct driver?  The "SQL Server" default driver CANNOT support many common, newer SQL Server data types.  Either change the data types on the server to be a type that Access will recognize or get a newer driver.  Keep in mind that when you distribute this app, you will need to have the new driver installed on every user PC as well as your own or they won't be able to run the app.

To get a better handle on what is causing the problem, try to append only a few records.  Reduce the selected records until you get to one single record that you cannot append if necessary.

You are appending data using a view.  Make sure that EVERY part of the PK or unique index of the "lookup" table is properly populated AND that the referenced record exists.  You cannot insert data into multiple tables at one time.

You might also need to fix bad dates before you attempt the insert.  The SQL Server DateTime data type has a different scope than the Access DateTime data type.  For example, I found some typos in some old data that I was converting that were causing the entire append to fail.  I think there is a table setting in SQL Server that will allow bad records to be ignored and good records to be accepted so that appends work like they do with Jet/ACE but I'm not positive.  For example 12/12/204 was OK with Access but not with SQL Server.  Some dates, like this one were easy to fix the typo.  The year was supposed to be 2004 which was obvious by other dates in the record.  My Access app was actually editing dates for reasonableness but the old app did not so my version of the app would not have allowed that date but the old one did and I had to clean up the data to get it converted.

Ben,

That data doesnt seem to match what you had posted a screen shot for. Nor does it seem to be the  Skilled_Nursing_Visit_Note.

It seems to be an extract from your select statement.

I cant help you doing it this way....

I did import that spreadsheet into Access. It imported four different data types.
1) Short Text
2) Number
3) Date / Time
4) Yes/No

Short text should go to varchar
Number (for Hour and Minutes) needs some massaging
Date / Time - example previously given - for the time component you can add in the time components via format()
Yes/No see below

SELECT Sheet1.SNV_ID, IIf(Sheet1.Shift_From_Hour>=0 And Sheet1.Shift_From_Hour<=23,int(Sheet1.Shift_From_Hour),0) AS Shift_From_Hour, IIf(Sheet1.Shift_From_Minute>=0 And Sheet1.Shift_From_Minute<=59,int(Sheet1.Shift_From_Minute),0) AS Shift_From_Minute, IIf(Sheet1.Reviewed_Status=False,0,1) AS Reviewed_Status, format(Sheet1.date_signed,"yyyy-mm-dd HH:nn:ss") as Date_Signed
FROM Sheet1;

Select allOpen in new window

Not much else I can do other than code up a few examples....

Hi,

@Pat,

Reduce the selected records until you get to one single record that you cannot append if necessary.

I got to this point already...
none of those records are being inserted, all that were possible did make it thru as previously mentioned.

...You cannot insert data into multiple tables at one time

This is a query that worked for Access, just having trouble upon conversion.

You might also need to fix bad dates before you attempt the inser

We tried that already as posted above, only helped a little.



@Mark,

It seems to be an extract from your select statement.

well this is what I'm looking to accomplish, no?

Not much else I can do other than code up a few examples....

Working in that direction...-:)
https://www.experts-exchange.com/questions/29127578/script-to-convert-all-characters-in-excel-sheet-to-A.html#questionAdd

Thanks,
Ben

@Mark,
Here you got a test file of examples (besides for the date fields which we already covered, all looks good for testing purpose...)
THanks,
Ben
Book1.xlsx

Here's an expression for your missing fields query:

Mid(
IIF(IsNull([SNV_ID]),",SNV_ID","") & 
IIF(IsNull([Client_Last_Name]),",Client_Last_Name","") & 
IIF(IsNull([Visit_Date]),",Visit_Date","") & 
IIF(IsNull([Date_Of_Birth]),",Date_Of_Birth","") & 
IIF(IsNull([Medical_Record_Number]),",Medical_Record_Number","") & 
IIF(IsNull([Shift_From_Hour]),",Shift_From_Hour","") & 
IIF(IsNull([Shift_To_Hour]),",Shift_To_Hour","") & 
IIF(IsNull([Purpose_Of_Visit_Goal]),",Purpose_Of_Visit_Goal","") & 
IIF(IsNull([Temp]),",Temp","") & 
IIF(IsNull([Pulse]),",Pulse","") & 
IIF(IsNull([RR]),",RR","") & 
IIF(IsNull([BP]),",BP","") & 
IIF(IsNull([O2_Saturation]),",O2_Saturation","") & 
IIF(IsNull([O2_In_Use]),",O2_In_Use","") & 
IIF(IsNull([Respiratory_Lung_Sounds]),",Respiratory_Lung_Sounds","") & 
IIF(IsNull([Respiratory_Breathing_Pattern]),",Respiratory_Breathing_Pattern","") & 
IIF(IsNull([Bipap]),",Bipap","") & 
IIF(IsNull([Cpap]),",Cpap","") & 
IIF(IsNull([Vent]),",Vent","") & 
IIF(IsNull([Vent_BIPAP_Settings]),",Vent_BIPAP_Settings","") & 
IIF(IsNull([Cardiovascular_Skin_Color]),",Cardiovascular_Skin_Color","") & 
IIF(IsNull([Skin_Temp]),",Skin_Temp","") & 
IIF(IsNull([Cardiovascular_Edema]),",Cardiovascular_Edema","") & 
IIF(IsNull([Neuro_Alert]),",Neuro_Alert","") & 
IIF(IsNull([Neuro_Oriented]),",Neuro_Oriented","") & 
IIF(IsNull([Neuro_Self_Directing]),",Neuro_Self_Directing","") & 
IIF(IsNull([Neuro_Dev_Delay]),",Neuro_Dev_Delay","") & 
IIF(IsNull([Neuro_Deficits]),",Neuro_Deficits","") & 
IIF(IsNull([Neuro_Notes]),",Neuro_Notes","") & 
IIF(IsNull([GI_Oral_Tube_Feeding]),",GI_Oral_Tube_Feeding","") & 
IIF(IsNull([Current_Feeds]),",Current_Feeds","") & 
IIF(IsNull([Gastric_Residuals_Present]),",Gastric_Residuals_Present","") & 
IIF(IsNull([Residual_Volume]),",Residual_Volume","") & 
IIF(IsNull([MD_Called]),",MD_Called","") & 
IIF(IsNull([Abdominal_Assessment]),",Abdominal_Assessment","") & 
IIF(IsNull([Feeding_Tolerance]),",Feeding_Tolerance","") & 
IIF(IsNull([GU_Incontinent]),",GU_Incontinent","") & 
IIF(IsNull([Number_Of_Diaper_Changes]),",Number_Of_Diaper_Changes","") & 
IIF(IsNull([Urine_Stool_Volume_Description]),",Urine_Stool_Volume_Description","") & 
IIF(IsNull([Bedbound]),",Bedbound","") & 
IIF(IsNull([WheelChair]),",WheelChair","") & 
IIF(IsNull([Ambulatory]),",Ambulatory","") & 
IIF(IsNull([Mobility_Aids]),",Mobility_Aids","") & 
IIF(IsNull([Contractures]),",Contractures","") & 
IIF(IsNull([Musculoskeletal_Notes]),",Musculoskeletal_Notes","") & 
IIF(IsNull([Pain_Score]),",Pain_Score","") & 
IIF(IsNull([Pain_Notes]),",Pain_Notes","") & 
IIF(IsNull([Medication_Changes]),",Medication_Changes","") & 
IIF(IsNull([Medication_Changes_2]),",Medication_Changes_2","") & 
IIF(IsNull([Integument_Skin_Assessment]),",Integument_Skin_Assessment","") & 
IIF(IsNull([Skin_Care_Measures_Taken]),",Skin_Care_Measures_Taken","") & 
IIF(IsNull([Home_Environment_Safety]),",Home_Environment_Safety","") & 
IIF(IsNull([Treatments_Administered]),",Treatments_Administered","") & 
IIF(IsNull([Patient_Response]),",Patient_Response","") & 
IIF(IsNull([Instructions]),",Instructions","") & 
IIF(IsNull([Understanding]),",Understanding","") & 
IIF(IsNull([Plan_Goal]),",Plan_Goal","") & 
IIF(IsNull([Nurse_Signature_Last_Name]),",Nurse_Signature_Last_Name","") & 
IIF(IsNull([Signature]),",Signature","") & 
IIF(IsNull([Date_Signed]),",Date_Signed","") & 
IIF(IsNull([Nurse_Name_Stamp_SNV]),",Nurse_Name_Stamp_SNV","") & 
IIF(IsNull([Nurse_User_ID_num_SNV]),",Nurse_User_ID_num_SNV","") & 
IIF(IsNull([Medication_Changes_Yes]),",Medication_Changes_Yes","") & 
IIF(IsNull([Acknowledge_Accuracy]),",Acknowledge_Accuracy","") & 
IIF(IsNull([Trach]),",Trach","") & 
IIF(IsNull([CCM_Informed]),",CCM_Informed","") & 
IIF(IsNull([Tube_Type]),",Tube_Type","") & 
IIF(IsNull([Reviewed_Status]),",Reviewed_Status","") & 
IIF(IsNull([Reviewed_By]),",Reviewed_By","") & 
IIF(IsNull([Reviewed_stamp]),",Reviewed_stamp","") & 
IIF(IsNull([Client_First_Name]),",Client_First_Name","") & 
IIF(IsNull([Nurse_Signature_First_Name]),",Nurse_Signature_First_Name","") & 
IIF(IsNull([Shift_From_Minute]),",Shift_From_Minute","") & 
IIF(IsNull([Shift_To_Minute]),",Shift_To_Minute","") & 
IIF(IsNull([Patient_Unable_To_Sign]),",Patient_Unable_To_Sign","") & 
IIF(IsNull([Reason]),",Reason","") & 
IIF(IsNull([Status]),",Status","")
, 2) As MissingFields

Select allOpen in new window

Ben,

That spreadsheet sends us back a few steps. Instead of helping, it does the opposite.

Datatypes have been obfuscated (not just data), keys are repeated, ant it is yet another different definition where we are trying to find the mismatch between Access and SQL Seerver.

The reason I was able to test for reviewed_status (above) was one of the sample datasets had that column as a Yes/No.  Now it seems to be a character string "AAAAA"

And we have a lot of the same "AA" or 11  which doesnt help identify columns

And we already know the issue is a datatype mis-match  in fact, it set 33 fields to NULL and also detected duplicate keys (no doubt not helped by those NULL values)

So, let's do this a different way. Lets have a look at recieving columns which appears to be different from a previous question :

COLUMN_NAME                              DATA_TYPE            CHARACTER_MAXIMUM_LENGTH
---------------------------------------- -------------------- ------------------------
Patient_Unable_To_Sign                   bit                  0
Reviewed_Status                          bit                  0
Date_Signed                              datetime             0
DateEntered                              datetime             0
Reviewed_stamp                           datetime             0
Visit_Date                               datetime             0
Date_Of_Birth                            datetime2            0
Medical_Record_Number                    float                53
Shift_From_Hour                          int                  10
Shift_From_Minute                        int                  10
Shift_To_Hour                            int                  10
Shift_To_Minute                          int                  10
VisitDateInt                             int                  10
Client_First_Name_Init                   nvarchar             5
Client_Last_Name_Init                    nvarchar             5
Shift_From_Init                          nvarchar             5
Shift_To_Init                            nvarchar             5
Visit_Date_Init                          nvarchar             5
Status                                   nvarchar             50
Acknowledge_Accuracy                     nvarchar             255
CCM_Informed                             nvarchar             255
Client_First_Name                        nvarchar             255
Client_Last_Name                         nvarchar             255
Nurse_Name_Stamp_SNV                     nvarchar             255
Nurse_Signature_First_Name               nvarchar             255
Nurse_Signature_Last_Name                nvarchar             255
Nurse_User_ID_num_SNV                    nvarchar             255
Reason                                   nvarchar             255
Reviewed_By                              nvarchar             255
SNV_ID                                   nvarchar             255
Trach                                    nvarchar             255
Abdominal_Assessment                     nvarchar             MAX
Ambulatory                               nvarchar             MAX
Bedbound                                 nvarchar             MAX
Bipap                                    nvarchar             MAX
BP                                       nvarchar             MAX
Cardiovascular_Edema                     nvarchar             MAX
Cardiovascular_Skin_Color                nvarchar             MAX
Contractures                             nvarchar             MAX
Cpap                                     nvarchar             MAX
Current_Feeds                            nvarchar             MAX
Feeding_Tolerance                        nvarchar             MAX
Gastric_Residuals_Present                nvarchar             MAX
GI_Oral_Tube_Feeding                     nvarchar             MAX
GU_Incontinent                           nvarchar             MAX
Home_Environment_Safety                  nvarchar             MAX
Instructions                             nvarchar             MAX
Integument_Skin_Assessment               nvarchar             MAX
MD_Called                                nvarchar             MAX
Medication_Changes                       nvarchar             MAX
Medication_Changes_2                     nvarchar             MAX
Medication_Changes_Yes                   nvarchar             MAX
Mobility_Aids                            nvarchar             MAX
Musculoskeletal_Notes                    nvarchar             MAX
Neuro_Alert                              nvarchar             MAX
Neuro_Deficits                           nvarchar             MAX
Neuro_Dev_Delay                          nvarchar             MAX
Neuro_Notes                              nvarchar             MAX
Neuro_Oriented                           nvarchar             MAX
Neuro_Self_Directing                     nvarchar             MAX
Number_Of_Diaper_Changes                 nvarchar             MAX
O2_In_Use                                nvarchar             MAX
O2_Saturation                            nvarchar             MAX
Pain_Notes                               nvarchar             MAX
Pain_Score                               nvarchar             MAX
Patient_Response                         nvarchar             MAX
Plan_Goal                                nvarchar             MAX
Pulse                                    nvarchar             MAX
Purpose_Of_Visit_Goal                    nvarchar             MAX
Residual_Volume                          nvarchar             MAX
Respiratory_Breathing_Pattern            nvarchar             MAX
Respiratory_Lung_Sounds                  nvarchar             MAX
RR                                       nvarchar             MAX
Signature                                nvarchar             MAX
Skin_Care_Measures_Taken                 nvarchar             MAX
Skin_Temp                                nvarchar             MAX
Temp                                     nvarchar             MAX
Treatments_Administered                  nvarchar             MAX
Tube_Type                                nvarchar             MAX
Understanding                            nvarchar             MAX
Urine_Stool_Volume_Description           nvarchar             MAX
Vent                                     nvarchar             MAX
Vent_BIPAP_Settings                      nvarchar             MAX
WheelChair                               nvarchar             MAX

Select allOpen in new window

Missing is ID becuase that is an identity column, and, TS because that is a timestamp (aka rowversion) column.

The datatypes up until row 15 are : BIT, DATETIME, DATETIME2 (use datetime strategy), FLOAT (use INT strategy) and INT

So those datatypes should go through an explicit handling - and probably should make that DATETIME2 column, a DATETIME instead.

That leaves us with the NVARCHAR columns - the smaller ones might be truncated, so would be worthwhile using LEFT(<columne name>,5)

The NVARCHAR(255) should be OK, and the NVARCHAR(MAX) is clearly excessive for a lot of those remaining columns.

Now, we do have strategies for BIT, DATETIME and INT as exemplified by :

SELECT Sheet1.SNV_ID, IIf(Sheet1.Shift_From_Hour>=0 And Sheet1.Shift_From_Hour<=23,int(Sheet1.Shift_From_Hour),0) AS Shift_From_Hour, IIf(Sheet1.Shift_From_Minute>=0 And Sheet1.Shift_From_Minute<=59,int(Sheet1.Shift_From_Minute),0) AS Shift_From_Minute, IIf(Sheet1.Reviewed_Status=False,0,1) AS Reviewed_Status, format(Sheet1.date_signed,"yyyy-mm-dd HH:nn:ss") as Date_Signed
FROM Sheet1;

Select allOpen in new window

We also have a strategy for the shorter columns :  LEFT(<columne name>,5)

Now I cant code it up for you because I have to guess what the real datatypes are in Access and that could affect how the columns need to be actually handled from the source.

Now, I did change a few of the columns in the code below, but just typed it in in this post, so tripple check, and add in the rest of the strategies....

So, to test, dont do the insert just yet, have a look at the data first

SELECT V_Visit_Note_Export.SNV_ID, V_Visit_Note_Export.Client_Last_Name, format([V_Visit_Note_Export].[Visit_Date],"yyyy-mm-dd HH:nn:ss") AS Expr1, format([V_Visit_Note_Export].[Date_Of_Birth]),"yyyy-mm-dd HH:nn:ss") AS Expr2, int(V_Visit_Note_Export.Medical_Record_Number), IIf(V_Visit_Note_Export.Shift_From_Hour>=0 And V_Visit_Note_Export.Shift_From_Hour<=23,int(V_Visit_Note_Export.Shift_From_Hour),0) AS Shift_From_Hour,IIf(V_Visit_Note_Export.Shift_To_Hour>=0 And V_Visit_Note_Export.Shift_To_Hour<=23,int(V_Visit_Note_Export.Shift_To_Hour),0) AS Shift_To_Hour, V_Visit_Note_Export.Purpose_Of_Visit_Goal, V_Visit_Note_Export.Temp, V_Visit_Note_Export.Pulse, V_Visit_Note_Export.RR, V_Visit_Note_Export.BP, V_Visit_Note_Export.O2_Saturation, V_Visit_Note_Export.O2_In_Use, V_Visit_Note_Export.Respiratory_Lung_Sounds, V_Visit_Note_Export.Respiratory_Breathing_Pattern, V_Visit_Note_Export.Bipap, V_Visit_Note_Export.Cpap, V_Visit_Note_Export.Vent, V_Visit_Note_Export.Vent_BIPAP_Settings, V_Visit_Note_Export.Cardiovascular_Skin_Color, V_Visit_Note_Export.Skin_Temp, V_Visit_Note_Export.Cardiovascular_Edema, V_Visit_Note_Export.Neuro_Alert, V_Visit_Note_Export.Neuro_Oriented, V_Visit_Note_Export.Neuro_Self_Directing, V_Visit_Note_Export.Neuro_Dev_Delay, V_Visit_Note_Export.Neuro_Deficits, V_Visit_Note_Export.Neuro_Notes, V_Visit_Note_Export.GI_Oral_Tube_Feeding, V_Visit_Note_Export.Current_Feeds, V_Visit_Note_Export.Gastric_Residuals_Present, V_Visit_Note_Export.Residual_Volume, V_Visit_Note_Export.MD_Called, V_Visit_Note_Export.Abdominal_Assessment, V_Visit_Note_Export.Feeding_Tolerance, V_Visit_Note_Export.GU_Incontinent, V_Visit_Note_Export.Number_Of_Diaper_Changes, V_Visit_Note_Export.Urine_Stool_Volume_Description, V_Visit_Note_Export.Bedbound, V_Visit_Note_Export.WheelChair, V_Visit_Note_Export.Ambulatory, V_Visit_Note_Export.Mobility_Aids, V_Visit_Note_Export.Contractures, V_Visit_Note_Export.Musculoskeletal_Notes, V_Visit_Note_Export.Pain_Score, V_Visit_Note_Export.Pain_Notes, V_Visit_Note_Export.Medication_Changes, V_Visit_Note_Export.Medication_Changes_2, V_Visit_Note_Export.Integument_Skin_Assessment, V_Visit_Note_Export.Skin_Care_Measures_Taken, V_Visit_Note_Export.Home_Environment_Safety, V_Visit_Note_Export.Treatments_Administered, V_Visit_Note_Export.Patient_Response, V_Visit_Note_Export.Instructions, V_Visit_Note_Export.Understanding, V_Visit_Note_Export.Plan_Goal, V_Visit_Note_Export.Nurse_Signature_Last_Name, V_Visit_Note_Export.Signature, format([V_Visit_Note_Export].[Date_Signed],"yyyy-mm-dd HH:nn:ss") AS Expr3, V_Visit_Note_Export.Nurse_Name_Stamp_SNV, V_Visit_Note_Export.Nurse_User_ID_num_SNV, V_Visit_Note_Export.Medication_Changes_Yes, V_Visit_Note_Export.Acknowledge_Accuracy, V_Visit_Note_Export.Trach, V_Visit_Note_Export.CCM_Informed, V_Visit_Note_Export.Tube_Type, V_Visit_Note_Export.Reviewed_Status, V_Visit_Note_Export.Reviewed_By, V_Visit_Note_Export.Reviewed_stamp, V_Visit_Note_Export.Client_First_Name, V_Visit_Note_Export.Nurse_Signature_First_Name, V_Visit_Note_Export.Shift_From_Minute, V_Visit_Note_Export.Shift_To_Minute, V_Visit_Note_Export.Patient_Unable_To_Sign, V_Visit_Note_Export.Reason, V_Visit_Note_Export.Status
FROM V_Visit_Note_Export 
WHERE ((((V_Visit_Note_Export.Status) Is Null Or (V_Visit_Note_Export.Status)<>"draft") ) 
OR (((V_Visit_Note_Export.Status)="draft") AND ((V_Visit_Note_Export.Deleted)=True)))
AND NOT EXISTS (select NULL from Skilled_Nursing_Visit_Note where Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID)

Select allOpen in new window

@Mark,

Was trying to import this file to SQL and executing from there as was suggested above by Dale

If you copy the query to SSMS, will it run?  If not, it might provide you with a better error message than what you are getting in Access.

But getting error

Executing (Error)
Messages
Error 0xc020901c: Data Flow Task 1: There was an error with output column "Neuro_Notes" (111) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)
 
Error 0xc020902a: Data Flow Task 1: The "output column "Neuro_Notes" (111)" failed because truncation occurred, and the truncation row disposition on "output column "Neuro_Notes" (111)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - V_Visit_Note_Export" (1) returned error code 0xC020902A.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 

Select allOpen in new window

Tried under edit mappings  changing column type to text (as no MAX was not avail) and still had this problem.
Perhaps you know a command to import w/o using the wizard?

And we already know the issue is a datatype mis-match  in fact, it set 33 fields to NULL and also detected duplicate keys (no doubt not helped by those NULL values)

I'm not fully convinced yet that the whole issue is datatype related, as usually converted fields to null is a message that comes up when the insert was successful, see attached what message comes up when records not successful due to duplicate keys.

Besides the only unique index in that table is the ID, and we are not inserting anything there...

However just to make sure the problem lays in datatype I will try to remove all those fields mentioned from my insert query, run and see...

BTW, will it help if I can get those guys to change that script and modify the logic a little, for example only change vowels and in addition ignore yes/no fields..?

Thanks,
Ben
Untitled.png

Just updating,
I ran the below, removing all date and integer columns, and still had same problem.

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID, Client_Last_Name, Medical_Record_Number, Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, Vent_BIPAP_Settings, Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, Neuro_Notes, GI_Oral_Tube_Feeding, Current_Feeds, Gastric_Residuals_Present, Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, Number_Of_Diaper_Changes, Urine_Stool_Volume_Description, Bedbound, WheelChair, Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, Pain_Notes, Medication_Changes, Medication_Changes_2, Integument_Skin_Assessment, Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, Patient_Response, Instructions, Understanding, Plan_Goal, Nurse_Signature_Last_Name, Signature, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, Reviewed_Status, Reviewed_By, Client_First_Name, Nurse_Signature_First_Name, Patient_Unable_To_Sign, Reason, Status )
SELECT [V_Visit_Note_Export].[SNV_ID] & "11111" AS Expr1, V_Visit_Note_Export.Client_Last_Name, V_Visit_Note_Export.Medical_Record_Number, V_Visit_Note_Export.Purpose_Of_Visit_Goal, V_Visit_Note_Export.Temp, V_Visit_Note_Export.Pulse, V_Visit_Note_Export.RR, V_Visit_Note_Export.BP, V_Visit_Note_Export.O2_Saturation, V_Visit_Note_Export.O2_In_Use, V_Visit_Note_Export.Respiratory_Lung_Sounds, V_Visit_Note_Export.Respiratory_Breathing_Pattern, V_Visit_Note_Export.Bipap, V_Visit_Note_Export.Cpap, V_Visit_Note_Export.Vent, V_Visit_Note_Export.Vent_BIPAP_Settings, V_Visit_Note_Export.Cardiovascular_Skin_Color, V_Visit_Note_Export.Skin_Temp, V_Visit_Note_Export.Cardiovascular_Edema, V_Visit_Note_Export.Neuro_Alert, V_Visit_Note_Export.Neuro_Oriented, V_Visit_Note_Export.Neuro_Self_Directing, V_Visit_Note_Export.Neuro_Dev_Delay, V_Visit_Note_Export.Neuro_Deficits, V_Visit_Note_Export.Neuro_Notes, V_Visit_Note_Export.GI_Oral_Tube_Feeding, V_Visit_Note_Export.Current_Feeds, V_Visit_Note_Export.Gastric_Residuals_Present, V_Visit_Note_Export.Residual_Volume, V_Visit_Note_Export.MD_Called, V_Visit_Note_Export.Abdominal_Assessment, V_Visit_Note_Export.Feeding_Tolerance, V_Visit_Note_Export.GU_Incontinent, V_Visit_Note_Export.Number_Of_Diaper_Changes, V_Visit_Note_Export.Urine_Stool_Volume_Description, V_Visit_Note_Export.Bedbound, V_Visit_Note_Export.WheelChair, V_Visit_Note_Export.Ambulatory, V_Visit_Note_Export.Mobility_Aids, V_Visit_Note_Export.Contractures, V_Visit_Note_Export.Musculoskeletal_Notes, V_Visit_Note_Export.Pain_Score, V_Visit_Note_Export.Pain_Notes, V_Visit_Note_Export.Medication_Changes, V_Visit_Note_Export.Medication_Changes_2, V_Visit_Note_Export.Integument_Skin_Assessment, V_Visit_Note_Export.Skin_Care_Measures_Taken, V_Visit_Note_Export.Home_Environment_Safety, V_Visit_Note_Export.Treatments_Administered, V_Visit_Note_Export.Patient_Response, V_Visit_Note_Export.Instructions, V_Visit_Note_Export.Understanding, V_Visit_Note_Export.Plan_Goal, V_Visit_Note_Export.Nurse_Signature_Last_Name, V_Visit_Note_Export.Signature, V_Visit_Note_Export.Nurse_Name_Stamp_SNV, V_Visit_Note_Export.Nurse_User_ID_num_SNV, V_Visit_Note_Export.Medication_Changes_Yes, V_Visit_Note_Export.Acknowledge_Accuracy, V_Visit_Note_Export.Trach, V_Visit_Note_Export.CCM_Informed, V_Visit_Note_Export.Tube_Type, V_Visit_Note_Export.Reviewed_Status, V_Visit_Note_Export.Reviewed_By, V_Visit_Note_Export.Client_First_Name, V_Visit_Note_Export.Nurse_Signature_First_Name, V_Visit_Note_Export.Patient_Unable_To_Sign, V_Visit_Note_Export.Reason, V_Visit_Note_Export.Status
FROM Skilled_Nursing_Visit_Note RIGHT JOIN V_Visit_Note_Export ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
WHERE (((V_Visit_Note_Export.Status) Is Null Or (V_Visit_Note_Export.Status)<>"draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null)) OR (((V_Visit_Note_Export.Status)="draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null) AND ((V_Visit_Note_Export.Deleted)=True));

Select allOpen in new window

Thanks,
Ben

Yes Ben, I do have code. And no, I do not use the wizard.

But YOU said not to use SSMS - let alone the Wizard. So, why the distraction ?

Attached are three downloads I have recieved from you in this and other threads.

The code to read them as a datasource is :

select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=c:\mrwtest\ee\Skilled_Nursing_Visit_Note.xlsx;HDR=yes', 'SELECT * FROM [Sheet1$]') as a

select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=c:\mrwtest\ee\V_Visit_Note_Export.xlsx;HDR=yes', 'SELECT * FROM [Sheet1$]') as a

select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=c:\mrwtest\ee\Ben_Book1.xlsx;HDR=yes', 'SELECT * FROM [Sheet1$]') as a

Select allOpen in new window

NOW.....

I dont know what you are doing or trying.

What I want you to do is to finish off that query above which I started to edit and left it to you to finish and to triple check.

How are you progressing with the immediate task at hand ?

Before we go off on tangents, you have to stay tuned to the tasks at hand.

If we are going to abandon the Access Query, then give me the *real* V_Visit_Note_Export data and I will code it up in SQL Server - this current approach is getting us NO WHERE and it has to stop.

By all means try the openrowset, but can you PLEASE get that query (above) finished ?

And I dont understand what that extra file V_Visit_Note_Export is. If you can already populate Skilled_Nursing_Visit_Note using Access, and have to assume you used to import V_Visit_Note_Export into Skilled_Nursing_Visit_Note then how is this so very different ?

If you want me to code it up, provide me an accurate rendition V_Visit_Note_Export (by all means change names to Jack and Jill etc), along with a list of all columns and their data types and size and sit back and wait.
Skilled_Nursing_Visit_Note.xlsx
Ben_Book1.xlsx
V_Visit_Note_Export.xlsx

>> I ran the below, removing all date and integer columns, and still had same problem.

Didnt I say :  JUST RUN THE QUERY WITHOUT THE INSERT ?

You need to see the data and check that your source data looks reasonable.

JUST RUN THE QUERY WITHOUT THE INSERT ?

As mentioned in SSMS I cannot manage to import yet, and when trying to run that from Access as is I get the attached error.
Even when trying removing all columns from select statement just leaving one, I get the error in 2nd attachment.
However I cannot figure out why are those errors coming up, as field definitely exists, see 3rd attachment.

you used to import V_Visit_Note_Export into Skilled_Nursing_Visit_Note then how is this so very different ?

Correct, but till now the Skilled_Nursing_Visit_Note table was in Access and there were no problems importing into it, now when trying to use this as a SQL table is where the problem begins...

Thanks,
Ben
Untitled.png
Untitled1.png
Untitled.png

Ben,

Before you started the migration to SQL Server, you were able to import the data from Excel into Access without any problem?  How did you do that, did you link the Excel file and then append or did you simply import directly from Excel into Access?

In my experience, Importing from Excel can be painful, but if you predefine the fields in an Access table, I usually use ShortText for all fields, but if you know a field contains more, you can make it a memo field.  I then run a series of checks to make sure each field contains the values I expect, so if it is supposed to be a numeric value, I check to make sure that all values in that field are numeric.  If not, then I flag the record (usually an additional Yes/No field in the Access table) and move on to the next field.  This is not usually necessary for text fields, but for numbers and date fields I find it especially helpful.

When I have completed that process, I then write an append query to take the data from that staging table and push it into the production table, using the Access conversion functions clng(), cdbl(), cdate() to convert the text fields from the staging table into the appropriate data type in the production table.  This can be time consuming, and I will generally build this append query one field at a time (with a copy of my production table) so that I know the conversion and upload is working and so that I can isolate problem columns.

Hope that helps some.

Hi Experts,

While working on this I have encountered a serious issue which may be affecting production.
https://www.experts-exchange.com/questions/29127720/Mapping-Excel-fields-when-linking-to-Access-app.html?anchor=a42745528¬ificationFollowed=219019413&anchorAnswerId=42745528#a42745528
Therefore I will have to switch tasks for now.
All I know, that issue may have been a big contributor to this one as well...
(It probably time to close this thread anyway by now and reward the contributors-:)
Will post again when that gets resolved...
Thanks to all participants!!!
(And special thanks to Mark for his extraordinary involvement here...)
Ben

Ben,

I can appreciate timings and frustrations. Data can be a horrid beast.

But it can also be mastered, beaten into submission.

Just a painful journey along the way.

To those attachments....

Attachment 1 is interesting - and clearly missing a bracket - we can fix that.

Attachment 2 is less interesting because the query wont work - trying to pick a column that doesnt exist in the table you are selecting from.

Attachment 3 shows the first 25 columns of more than 75 columns. So we dont get to see two-thirds of the column definitions.

We do need to take a very methodical approach, that's why we want to take a step back and just focus on being able to SELECT first.

Now, looking at the SQL Server Table, its structure is very ordinary and so, have to assume that at some stage, realising that you have VARCHAR(MAX) for pulse and Blood pressure will need to be addressed. Otherwise with so many MAX columns, SQL Server will fail to deliver improvements that you seek.

Let's start with what columns have to be long text or memo type fields..... We will keep those as varchar(max) but need to trim the others....

Because we CAN change the SQL Server table definition if needed (and certainly some parts are needed to change).

We can do this, but you need to remain focussed on the tasks at hand.

There is NO silver bullet. Just a lot of elbow grease, hair pulling, and frustrations aplenty.

I dont think we are that far away. Honestly.

But, it is hard to get the message across, and need you to focus and discuss one task at a time....

For a start, and taking a step back.... Where is V_Visit_Note_Export coming from ? Does it have a file layout definition ? Are you currently and successfully importing it into Access now ?

Hi,

Attachment 1 is interesting - and clearly missing a bracket - we can fix that.

Attachment 2 is less interesting because the query wont work - trying to pick a column that doesnt exist in the table you are selecting from.

Attachment 3 shows the first 25 columns of more than 75 columns. So we dont get to see two-thirds of the column definitions.

Attachment 1- I also thought its just a missing bracket, and therefore removed all columns including brackets from the select statement and still encountered problems as shown in 2nd attachment, while the 3rd attachment was only meant to show it is not due to column not existing as seen it that attachment column snv_id...

Where is V_Visit_Note_Export coming from ?  

V_Visit_Note_Export is comming from 3rd party software exported into Excel file.

Does it have a file layout definition ?

Currently most fields are defined as short text.

So we dont get to see two-thirds of the column definitions.

Will it help if i send a pic of the rest of the columns?

Are you currently and successfully importing it into Access now ?

Absolutely. (except for the issue just detected in the other thread).

As mentioned, I'm planning to open a new thread for the remaining task when I will be ready for it.

Thanks,
Ben

I have to admit that I haven't been paying close attention to this thread but it seems that you need to figure out what data types you need and use your append query to coerce those data types.  If you have a string and you need a date, use cDate() or if you have a date and need a string, use Format() for example.  Also, I pointed out either in this thread or another of yours on a similar topic, you MUST either use the correct driver or modify the SQL Server table datatypes to be comparable with the driver you are using.  For example, the default "SQL Server" driver only recognizes DateTime.  It does not recognize ANY of the other date time data types.  If you need to post data to those fields, you MUST use a newer driver AND that driver MUST be installed on ALL computers that will run the app.

Attachment 2 was a wrong query

Trying to select V_Visit_Note_Export.SNV_ID

But trying to get it from Skilled_Nursing_Visit_Note  instead of V_Visit_Note_Export.

Thats what I am trying to say to you....

Slow down - it will be quicker with less distractions.