Type conversion failure on Append

[albert.repasky]

I am getting a Type conversion failure on all 10 records when I append
records to a table.

Message is:
Set 0 field(s) to Null due to a type conversion failure, and it
didn't add 10 record(s) to the table due to key violations, 0
records(s) due to lock violations, and 0 record(s) due to validation
rule violations.

It does not seem to be a key violation and no fields are required and
all fields Allow Zero Length.

I have seen the other postings and still can not resolve this. I am
importing another table and it works fine. Both tables come from 2
different Excel WS's and originally come from Peoplesoft.

I am using Access 2003.

Any ideas out there?

arep

 

[KARL DEWEY]

When it says 'it didn't add 10 record(s) to the table due to key violations'
you can pretty much believe it.
Run a match query on the two tables to see if the primary key field or any
field with a unique index already has those records.

 

[albert.repasky]

When it says 'it didn't add 10 record(s) to the table due to key violations'
you can pretty much believe it.
Run a match query on the two tables to see if the primary key field or any
field with a unique index already has those records.
--
KARL DEWEY
Build a little - Test a little











- Show quoted text -

I am not a super expert with queries. How do I wirte a match query?

Thanks,
arep

 

[KARL DEWEY]

Create a new query using both tables.
You can start with the wizard for Unmatched query selecting the two tables,
the fields to compare, and fields to display. Then open the query in design
view. Click on the line connecting the two tables (has an arrow head) and
change the selection to 'Only include rows where the joined fields from both
tables are equal.' Close that window. Remove the 'Is Null' from the
criteria row of the design view grid.
Run the query to find the records that are in both tables.

 

[albert.repasky]

Create a new query using both tables.  
You can start with the wizard for Unmatched query selecting the two tables,
the fields to compare, and fields to display.  Then open the query in design
view.  Click on the line connecting the two tables (has an arrow head) and
change the selection to 'Only include rows where the joined fields from both
tables are equal.'  Close that window.   Remove the 'Is Null'  from the
criteria row of the design view grid.
Run the query to find the records that are in both tables.
--
KARL DEWEY
Build a little - Test a little







- Show quoted text -

I have version 2003. I do not see an Unmatched query in the wizard?
arep

 

[John Spencer]

In the database window,
-- switch to the query tab
-- click the new button
-- click on Find Unmatched Query Wizard (5th item)
-- click the OK button and follow the instructions.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[KARL DEWEY]

Can you create a query using two tables? Join them as I said and then do the
rest of my post.

 

[albert.repasky]

Can you create a query using two tables?  Join them as I said and then do the
rest of my post.
--
KARL DEWEY
Build a little - Test a little






- Show quoted text -

Ok, I found the wizard.

I ran it and I get 134 records. The table has 52 records and the
query has 15.

Here is the SQL:
SELECT [Import_FilledReqs Query].[Job Req #], [Import_FilledReqs
Query].[Job Req Status], [Import_FilledReqs Query].[Finance ID],
[Import_FilledReqs Query].[Prioritization ID], [Import_FilledReqs
Query].[BAU CC], [Import_FilledReqs Query].[Manager Name],
[Import_FilledReqs Query].Grade, [Import_FilledReqs Query].City,
[Import_FilledReqs Query].DeptID, [Import_FilledReqs Query].[Close
Date], [Import_FilledReqs Query].[Job Code], [Import_FilledReqs Query].
[Job Title], [Import_FilledReqs Query].[Hired ID], [Import_FilledReqs
Query].[Hired Name], [Import_FilledReqs Query].[Start Date]
FROM [Import_FilledReqs Query] INNER JOIN tblHRRequisition ON
[Import_FilledReqs Query].[Finance ID] = tblHRRequisition.fldSAID;


What does this mean?

arep

 

[KARL DEWEY]

You joined on [Finance ID] = fldSAID and got those numbers so then that field
must not be your primary key field.
You need to join on the primary key field or which ever field that you have
a unique index.


--
KARL DEWEY
Build a little - Test a little

Can you create a query using two tables? Join them as I said and then do the
rest of my post.
--
KARL DEWEY
Build a little - Test a little


















- Show quoted text -

Ok, I found the wizard.

I ran it and I get 134 records. The table has 52 records and the
query has 15.

Here is the SQL:
SELECT [Import_FilledReqs Query].[Job Req #], [Import_FilledReqs
Query].[Job Req Status], [Import_FilledReqs Query].[Finance ID],
[Import_FilledReqs Query].[Prioritization ID], [Import_FilledReqs
Query].[BAU CC], [Import_FilledReqs Query].[Manager Name],
[Import_FilledReqs Query].Grade, [Import_FilledReqs Query].City,
[Import_FilledReqs Query].DeptID, [Import_FilledReqs Query].[Close
Date], [Import_FilledReqs Query].[Job Code], [Import_FilledReqs Query].
[Job Title], [Import_FilledReqs Query].[Hired ID], [Import_FilledReqs
Query].[Hired Name], [Import_FilledReqs Query].[Start Date]
FROM [Import_FilledReqs Query] INNER JOIN tblHRRequisition ON
[Import_FilledReqs Query].[Finance ID] = tblHRRequisition.fldSAID;


What does this mean?

arep

 

[albert.repasky]

You joined on [Finance ID] = fldSAID and got those numbers so then that field
must not be your primary key field.
You need to join on the primary key field or which ever field that you have
a unique index.

--
KARL DEWEY
Build a little - Test a little

Ok, I found the wizard.

I ran it and I get 134 records.  The table has 52 records and the
query has 15.

Here is the SQL:
SELECT [Import_FilledReqs Query].[Job Req #], [Import_FilledReqs
Query].[Job Req Status], [Import_FilledReqs Query].[Finance ID],
[Import_FilledReqs Query].[Prioritization ID], [Import_FilledReqs
Query].[BAU CC], [Import_FilledReqs Query].[Manager Name],
[Import_FilledReqs Query].Grade, [Import_FilledReqs Query].City,
[Import_FilledReqs Query].DeptID, [Import_FilledReqs Query].[Close
Date], [Import_FilledReqs Query].[Job Code], [Import_FilledReqs Query].
[Job Title], [Import_FilledReqs Query].[Hired ID], [Import_FilledReqs
Query].[Hired Name], [Import_FilledReqs Query].[Start Date]
FROM [Import_FilledReqs Query] INNER JOIN tblHRRequisition ON
[Import_FilledReqs Query].[Finance ID] = tblHRRequisition.fldSAID;

What does this mean?

arep- Hide quoted text -

- Show quoted text -

Here is the new sql. I got 13 records matching. I only have 27
records in the table.

SELECT [Import_FilledReqs Query].[Job Req #], [Import_FilledReqs
Query].[Job Req Status], [Import_FilledReqs Query].[Finance ID],
[Import_FilledReqs Query].[Prioritization ID], [Import_FilledReqs
Query].[BAU CC], [Import_FilledReqs Query].[Manager Name],
[Import_FilledReqs Query].Grade, [Import_FilledReqs Query].City,
[Import_FilledReqs Query].DeptID, [Import_FilledReqs Query].[Close
Date], [Import_FilledReqs Query].[Job Code], [Import_FilledReqs Query].
[Job Title], [Import_FilledReqs Query].[Hired ID], [Import_FilledReqs
Query].[Hired Name], [Import_FilledReqs Query].[Start Date]
FROM [Import_FilledReqs Query] INNER JOIN tblHRRequisition ON
[Import_FilledReqs Query].[Job Req #] = tblHRRequisition.fldReqID;

I have been deleting everything in the table and it still doesn't
work. When I copy the records back to the table, I get errors that a
field in not 9999999999;; I did not have a primary key on either
tables, so I put them on the Job Req # and fldReqID.

This is very confusing.

 

[KARL DEWEY]

Here is the new sql. I got 13 records matching. I only have 27 records in
the table.
This does not match your eariler post "The table has 52 records and the
query has 15." It shows that 13 records have duplicate [Import_FilledReqs
Query].[Job Req #] = tblHRRequisition.fldReqID when you ran it.
Why? Is the field index set to unique?

Open your table in design view, click on menu VIEW - Indexes. Click on each
one and see if any are set to unique. Do any fields have a little key icon
to the left?
--
KARL DEWEY
Build a little - Test a little

Here is the new sql. I got 13 records matching. I only have 27
records in the table.

SELECT [Import_FilledReqs Query].[Job Req #], [Import_FilledReqs
Query].[Job Req Status], [Import_FilledReqs Query].[Finance ID],
[Import_FilledReqs Query].[Prioritization ID], [Import_FilledReqs
Query].[BAU CC], [Import_FilledReqs Query].[Manager Name],
[Import_FilledReqs Query].Grade, [Import_FilledReqs Query].City,
[Import_FilledReqs Query].DeptID, [Import_FilledReqs Query].[Close
Date], [Import_FilledReqs Query].[Job Code], [Import_FilledReqs Query].
[Job Title], [Import_FilledReqs Query].[Hired ID], [Import_FilledReqs
Query].[Hired Name], [Import_FilledReqs Query].[Start Date]
FROM [Import_FilledReqs Query] INNER JOIN tblHRRequisition ON
[Import_FilledReqs Query].[Job Req #] = tblHRRequisition.fldReqID;

I have been deleting everything in the table and it still doesn't
work. When I copy the records back to the table, I get errors that a
field in not 9999999999;; I did not have a primary key on either
tables, so I put them on the Job Req # and fldReqID.

This is very confusing.

 

[albert.repasky]

the table.
This does not match your eariler post "The table has 52 records and the
query has 15."    It shows that 13 records have duplicate [Import_FilledReqs
Query].[Job Req #] = tblHRRequisition.fldReqID when you ran it.

                   Why?                     Is the field index set to unique?

Open your table in design view, click on menu VIEW - Indexes.  Click on each
one and see if any are set to unique.  Do any fields have a little key icon
to the left?
--
KARL DEWEY
Build a little - Test a little

Here is the new sql.  I got 13 records matching.  I only have 27
records in the table.

SELECT [Import_FilledReqs Query].[Job Req #], [Import_FilledReqs
Query].[Job Req Status], [Import_FilledReqs Query].[Finance ID],
[Import_FilledReqs Query].[Prioritization ID], [Import_FilledReqs
Query].[BAU CC], [Import_FilledReqs Query].[Manager Name],
[Import_FilledReqs Query].Grade, [Import_FilledReqs Query].City,
[Import_FilledReqs Query].DeptID, [Import_FilledReqs Query].[Close
Date], [Import_FilledReqs Query].[Job Code], [Import_FilledReqs Query].
[Job Title], [Import_FilledReqs Query].[Hired ID], [Import_FilledReqs
Query].[Hired Name], [Import_FilledReqs Query].[Start Date]
FROM [Import_FilledReqs Query] INNER JOIN tblHRRequisition ON
[Import_FilledReqs Query].[Job Req #] = tblHRRequisition.fldReqID;

I have been deleting everything in the table and it still doesn't
work.  When I copy the records back to the table, I get errors that a
field in not 9999999999;;  I did not have a primary key on either
tables, so I put them on the Job Req # and fldReqID.

This is very confusing.- Hide quoted text -

- Show quoted text -

I checked both tables. I took the primary key off both. The
tblHRRequisition table had the primary key on fldSAID. So I took it
off. And I took the primary key I put on Import_FilledReqs Query
table. It still does not import.

I did some testing before I went home on Fri. It looks like I have to
have all the following fields to import.

fldGroupID
fldCityID
fldLocationID
fldBuildingID
fldReqID
fldSAID

Arep

 

[albert.repasky]

the table.
This does not match your eariler post "The table has 52 records and the
query has 15."    It shows that 13 records have duplicate [Import_FilledReqs
Query].[Job Req #] = tblHRRequisition.fldReqID when you ran it.

I did not have a primary key on either tables, so I put them on the Job Req # and fldReqID.

                   Why?                     Is the field index set to unique?

Open your table in design view, click on menu VIEW - Indexes.  Click on each
one and see if any are set to unique.  Do any fields have a little keyicon
to the left?
--
KARL DEWEY
Build a little - Test a little

Here is the new sql.  I got 13 records matching.  I only have 27
records in the table.
SELECT [Import_FilledReqs Query].[Job Req #], [Import_FilledReqs
Query].[Job Req Status], [Import_FilledReqs Query].[Finance ID],
[Import_FilledReqs Query].[Prioritization ID], [Import_FilledReqs
Query].[BAU CC], [Import_FilledReqs Query].[Manager Name],
[Import_FilledReqs Query].Grade, [Import_FilledReqs Query].City,
[Import_FilledReqs Query].DeptID, [Import_FilledReqs Query].[Close
Date], [Import_FilledReqs Query].[Job Code], [Import_FilledReqs Query]..
[Job Title], [Import_FilledReqs Query].[Hired ID], [Import_FilledReqs
Query].[Hired Name], [Import_FilledReqs Query].[Start Date]
FROM [Import_FilledReqs Query] INNER JOIN tblHRRequisition ON
[Import_FilledReqs Query].[Job Req #] = tblHRRequisition.fldReqID;
I have been deleting everything in the table and it still doesn't
work.  When I copy the records back to the table, I get errors that a
field in not 9999999999;;  I did not have a primary key on either
tables, so I put them on the Job Req # and fldReqID.
This is very confusing.- Hide quoted text -

- Show quoted text -

I checked both tables.  I took the primary key off both.  The
tblHRRequisition table had the primary key on fldSAID.  So I took it
off.  And I took the primary key I put on Import_FilledReqs Query
table.  It still does not import.

I did some testing before I went home on Fri.  It looks like I have to
have all the following fields to import.

fldGroupID
fldCityID
fldLocationID
fldBuildingID
fldReqID
fldSAID

Arep- Hide quoted text -

- Show quoted text -

I was able to append. They 6 fields that I mentioned on my last post
have to be there. So I added the missing fields to the table and
query and it did append the records.

I also had another table that would not append and added the missing
fields and it now appends all the records.

So what I learned from this is that if you have ID's that point back
to other tables, you have to have those fields. This had nothing to
do with the primary key. In other words, if there are relationships
they have to be satified.

So I thank Karl and John for you help. I could not have done this
without your help.

Arep