If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Append Query Failure
When I tried to append a table I (Insert Into), I get a get this error
reading, "Microsoft Access can't append all records in the append query... Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 21 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) to due to validation rule violations." I ran the duplicates query as suggested in a previous post and it does not show any duplicates. I have noticed that the [End Date] column is not updating correctly. Any ideas on what could be? I've attached SQL below. INSERT INTO [Bill after 1st Query] ( [Occ Factor], SqrFeet, [Occ Factor Rate], [Occ Part of Bill], [SqrFeet Factor], [SqrFeet Part of Bill], [Billing Fee], [Trash Fee], [Late Fee], Delinquents, [Activation Charge], [Billing Start], [End Date], [Numof Billing Days], [Unit Number], [Month Code], [Resident Name], [Billing Address], [Billing City], [Billing State], [Billing Zip], Duplicates, Adjustments ) SELECT [Water Bill Calcs All One Line].[Occ Factor], [Water Bill Calcs All One Line].SqrFeet, [Water Bill Calcs All One Line].[Occ Factor Rate], [Water Bill Calcs All One Line].[Occ Part of Bill], [Water Bill Calcs All One Line].[SqrFeet Factor], [Water Bill Calcs All One Line].[SqrFeet Part of Bill], [Water Bill Calcs All One Line].[Billing Fee], [Water Bill Calcs All One Line].[Trash Fee], [Water Bill Calcs All One Line].[Late Fee], [Water Bill Calcs All One Line].Delinquents, [Water Bill Calcs All One Line].[Activation Charge], [Water Bill Calcs All One Line].[Billing Start], [Water Bill Calcs All One Line].[End Date], [Water Bill Calcs All One Line].[Numof Billing Days], [Water Bill Calcs All One Line].[Unit Number], [Water Bill Calcs All One Line].[Month Code], [Data Query].[Resident Name], [Billing Address].[Billing Address], [Billing Address].[Billing City], [Billing Address].[Billing State], [Billing Address].[Billing Zip], [Water Bill Calcs All One Line]![Month Code] & "-" & [Water Bill Calcs All One Line]![Unit Number] AS Duplicates, 0 AS Adjustments FROM ([Billing Address] INNER JOIN [Data Query] ON ([Billing Address].[Unit Number] = [Data Query].[Unit Number]) AND ([Billing Address].[Unit Number] = [Data Query].[Unit Number])) INNER JOIN [Water Bill Calcs All One Line] ON [Data Query].[Unit Number] = [Water Bill Calcs All One Line].[Unit Number]; -- Thank you in advance for any help you can offer. New to Access |
#2
|
|||
|
|||
Access Append Query Failure
Leaders01,
The error is not indicating duplicates. What it is saying say is. There is a field or two that have been indexed and the data you are trying to insert in the field(s) cannot go in because it will violate those indexes. Example... Field1 can only accept values A and B. But I am trying to append C to it. I will get the append error you are getting below. In your case you have 21 records that won't append because of this. No one here can assist with that... You will have to review the data you are trying to append and the table you are trying to append said data to and find the offending records. Now you could run an Unmatched query to figure out which 21 records did not get into your table BUT you will still be the only one who can scan the data. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Leaders01" wrote in message ... When I tried to append a table I (Insert Into), I get a get this error reading, "Microsoft Access can't append all records in the append query... Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 21 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) to due to validation rule violations." I ran the duplicates query as suggested in a previous post and it does not show any duplicates. I have noticed that the [End Date] column is not updating correctly. Any ideas on what could be? I've attached SQL below. INSERT INTO [Bill after 1st Query] ( [Occ Factor], SqrFeet, [Occ Factor Rate], [Occ Part of Bill], [SqrFeet Factor], [SqrFeet Part of Bill], [Billing Fee], [Trash Fee], [Late Fee], Delinquents, [Activation Charge], [Billing Start], [End Date], [Numof Billing Days], [Unit Number], [Month Code], [Resident Name], [Billing Address], [Billing City], [Billing State], [Billing Zip], Duplicates, Adjustments ) SELECT [Water Bill Calcs All One Line].[Occ Factor], [Water Bill Calcs All One Line].SqrFeet, [Water Bill Calcs All One Line].[Occ Factor Rate], [Water Bill Calcs All One Line].[Occ Part of Bill], [Water Bill Calcs All One Line].[SqrFeet Factor], [Water Bill Calcs All One Line].[SqrFeet Part of Bill], [Water Bill Calcs All One Line].[Billing Fee], [Water Bill Calcs All One Line].[Trash Fee], [Water Bill Calcs All One Line].[Late Fee], [Water Bill Calcs All One Line].Delinquents, [Water Bill Calcs All One Line].[Activation Charge], [Water Bill Calcs All One Line].[Billing Start], [Water Bill Calcs All One Line].[End Date], [Water Bill Calcs All One Line].[Numof Billing Days], [Water Bill Calcs All One Line].[Unit Number], [Water Bill Calcs All One Line].[Month Code], [Data Query].[Resident Name], [Billing Address].[Billing Address], [Billing Address].[Billing City], [Billing Address].[Billing State], [Billing Address].[Billing Zip], [Water Bill Calcs All One Line]![Month Code] & "-" & [Water Bill Calcs All One Line]![Unit Number] AS Duplicates, 0 AS Adjustments FROM ([Billing Address] INNER JOIN [Data Query] ON ([Billing Address].[Unit Number] = [Data Query].[Unit Number]) AND ([Billing Address].[Unit Number] = [Data Query].[Unit Number])) INNER JOIN [Water Bill Calcs All One Line] ON [Data Query].[Unit Number] = [Water Bill Calcs All One Line].[Unit Number]; -- Thank you in advance for any help you can offer. New to Access |
Thread Tools | |
Display Modes | |
|
|