Access Append Query Failure

  • Thread starter Thread starter Leaders01
  • Start date Start date
L

Leaders01

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];
 
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 said:
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];
Click to expand...
 
Back
Top