Append to Table

[ziad alsayed]

dear all

i am trying to import data from excel. the problem i am facing is: in my access table i have a field that is looking data from another table, so when i append data to my table this field will remain empty, is this normal in access? should i enter them manualy??

appreciate any help.

 

[Joe4]

If the data is in another table, can you get to it by linking the two tables (if there are fields to link on)?

If you can create what you need in a query, there shouldn't be any need to physically populate that field in the one table.

If I am not understanding the question, it might help to post an example.

 

[ziad alsayed]

dear Joe4

i have two tables only, the first is "EngineSerialNumber" which has 2 fields , the ID field (AutoNumber) and the EngineSN field which is a text field (Yes No Duplicate).
the second table is the "Invoices" which has the following field
InvoiceID(AutoNumber),AccountNumber(text),CustomerName(text),EngineSN ( the lookupfield that get data from the first table),InvoiceNumber(Number),InvoiceDate(Date/time),Amount(Number)

so the relation is one to many.

now i have an excel workbook that has the same number of column like the "Invoices" table except for the InvoiceID( Not included in the excel), when i try to append it to the invoices table it is accpeting all the fileds but not the EngineSN field.

Appreciate any help
NB: i am ready to send you the datebase with the excel if possible, it still less than 1MB.

 

[Joe4]

How is the EngineSN field set up in your table?
Is it an Autonumber field?
Does it allow duplicates?
Are there are any other validations that would prevent the field from being appended?
Are they formatted the same in Access/Excel (number-to-number or text-to-text)?

 

[ziad alsayed]

dear Joe4

the field EngineSN had the following Property
- required : yes
-allow zero Length: No
-Indexed: Yes ( No Duplicates).

it is a Text Field.

i have no other validation for the above field.

the data are formatted in same way between excel and Access.

 

[Joe4]

So, when you are trying to Append the data, does it give you errors for this field that it won't import?

Did you check to make sure that the records you are trying to append don't contain values that are already included in the table you are trying to write to?

 

[ziad alsayed]

dear Joe

i am receiving the below message:

Microsoft Access was unable to append all the data to the table

the contents of fields in 10 records were deleted, and 0 records were lsot due to key Violations
* if data was deleted, the data you pasted or imported doesen't match the field data types or fieldsize property in the destination table.
* if records were lost, either the records you pasted contain primary key values that already exist in the destination table or they violate the referential integrity rules for a relationship defined between tables.
Do you want to procceed anyway.


as for the second question the table is still empty, any way of course it will have duplicate data , like accountnumber or customer name or enginesn...

 

[Joe4]

Right, it is telling you what the problem is, you have key violations.

For example, if the one you posted earlier:

the field EngineSN had the following Property
- required : yes
-allow zero Length: No
-Indexed: Yes ( No Duplicates).

Let's say that your existing table has a value of "ABC" for one record in the EngineSN field. Now, let's say that one of the recordws you are trying to Append also has the value of "ABC" for the EngineSN field. That will not work, as you have indicated that the EngineSN field is set to not allow duplicates in this field.

Likewise, if you tried to import a record where the EngineSN field was blank, that would also reject, as that field is Required and does not allow blank entries.

Those are the sort of things you need to look for. So you need to analyze your data. You may want to try append one record at a time, to help you narrow the scope of what the problem may be.

 

[ziad alsayed]

dear joe

thanks for you assintance,please allow me to disaggree with you,

the EngineSN field in the "EngineSerialNumber" Table has the below property
- required : yes
-allow zero Length: No
-Indexed: Yes ( No Duplicates).

but the EngineSN field in the "Invoices" table has the below property
required: yes
indexed:yes ( Duplicates Ok).

Note: after i finish the appending process, if i check each record i will be able to choose the EngineSN from the drop down and i even can save the table.


so i don't think that is the reason. i hope you have other suggestion.

 

[Joe4]

thanks for you assintance,please allow me to disaggree with you,

I am not sure what you are disagreeing with. Everything I said in my last post is true.

Since we have no idea what your data looks like, I gave hypothetical examples of what would cause those type of errors. If those scenarios exist, then they would cause those type of errors. There is no disputing that, that is a fact (note I am not saying that this is how your data is - I don't know what it looks like because we haven't seen it).

Maybe I am not clear on what your problem is. I think you need to clarify exactly what your data looks like and what exactly you are trying to do. I got the impression that were trying to Append an Excel file into your "Invoices" table. So I am not really sure what role, if any, this other table has in this.

Can I ask you to post the SQL code for your append Query? That might shed some light as to what you are trying to do.