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.

 

[ziad alsayed]

dear Joe,

maybe i didn't explain my problem well.please bear with me, i will try again.

i only have 2 tables in my database.
the first table "EngineSerialNumber" Has 2 fields
1- ID field, it is the primary key, autonumber datatype,indexed(yes No duplicates).
2- EngineSN field, text data type, required: yes, allow zero Length: No, Indexed: yes No Duplicates.

the second table "Invoices", had 7 fields
1-InvoiceID filed, it is the primary key,Indexed(yes, No duplicates)
2- AccountNumber Field, text data type,required: yes, allow zero length:No, indexed : NO.
3- CustomerName field, text data Type,required: yes, allow zero length:No, indexed : NO.
4- EngineSN filed, Number data type ( it is a lookup filed that get data from the EngineSerialNumberTable),required: yes,indexed No.
5-InvoiceNumber field, Number Data type,Required: yes,Indexed:No
6-InvoiceDate field, date/Time data type, required: yes,indexed:No
7-Amount field, Number data type, required : No, indexed: No.

my excel file has the same format of the "Invoices" table, only the InvoiceID field is no included.

i am trying to append the excel file to the Invoices table, the purpose is to have all data in the table ( this will be a daily process, each day i will append the new data to the table) for me to be able to start preparing the queries and reports.

the problem is that after appending to the invoices table the field EngineSN in the Invoices table is empty.

below are my two tables

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>EngineSerialNumber</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>Engine S/N</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>CM50330U140177H</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5>AG51038U614549L</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3</TD><TD borderColor=#d0d7e5>AH50798U986307H</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>4</TD><TD borderColor=#d0d7e5>RJ51407U475204S</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>5</TD><TD borderColor=#d0d7e5>YB50496U772264H</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>6</TD><TD borderColor=#d0d7e5>YB51044U845869M</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>7</TD><TD borderColor=#d0d7e5>YD51262U881468P</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>8</TD><TD borderColor=#d0d7e5>WS4486N1407554</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>9</TD><TD borderColor=#d0d7e5>WS4418N1408052</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>10</TD><TD borderColor=#d0d7e5>WS4418N1311344</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>11</TD><TD borderColor=#d0d7e5>FGA061016U0603K</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>12</TD><TD borderColor=#d0d7e5>FGB061202U4302L</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>13</TD><TD borderColor=#d0d7e5>FGBF3045U14128T</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>14</TD><TD borderColor=#d0d7e5>8706942</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>15</TD><TD borderColor=#d0d7e5>HGB061160U4099M</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>16</TD><TD borderColor=#d0d7e5>HGB061160U8539M</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>17</TD><TD borderColor=#d0d7e5>HGB61013U2388K</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>18</TD><TD borderColor=#d0d7e5>JGB062010U0160M</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>19</TD><TD borderColor=#d0d7e5>SGN120669U0030K</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>20</TD><TD borderColor=#d0d7e5>DGD062005U7334M</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>21</TD><TD borderColor=#d0d7e5>DGD062005U3242L</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>22</TD><TD borderColor=#d0d7e5>DGB082031U1215J</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>23</TD><TD borderColor=#d0d7e5>DGB082077U4727N</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>24</TD><TD borderColor=#d0d7e5>DGBM3015U10749T</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>25</TD><TD borderColor=#d0d7e5>DGBR2011U2412S</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>26</TD><TD borderColor=#d0d7e5>SGB060582U2790H</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>27</TD><TD borderColor=#d0d7e5>DGD062030U9637M</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>28</TD><TD borderColor=#d0d7e5>HGB061160U9006L</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>29</TD><TD borderColor=#d0d7e5>CM0330U139982H</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>30</TD><TD borderColor=#d0d7e5>08003090LPW3A27</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>31</TD><TD borderColor=#d0d7e5>HP51106U977442K</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>32</TD><TD borderColor=#d0d7e5>LD50317U904531G</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>33</TD><TD borderColor=#d0d7e5>AH51039U532385K</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>34</TD><TD borderColor=#d0d7e5>YB51044U806118K</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>35</TD><TD borderColor=#d0d7e5>CM51036U227652M</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>36</TD><TD borderColor=#d0d7e5>YB51044U851004M</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>37</TD><TD borderColor=#d0d7e5>AH51039U637789L</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Invoices</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>InvoiceID</TH><TH bgColor=#c0c0c0 borderColor=#000000>AccountNumber</TH><TH bgColor=#c0c0c0 borderColor=#000000>CustomerName</TH><TH bgColor=#c0c0c0 borderColor=#000000>EngineSN</TH><TH bgColor=#c0c0c0 borderColor=#000000>InvoiceNumber</TH><TH bgColor=#c0c0c0 borderColor=#000000>InvoiceDate</TH><TH bgColor=#c0c0c0 borderColor=#000000>Amount</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1 align=right>3</TD><TD borderColor=#eeece1>4111110571</TD><TD borderColor=#eeece1>ziad</TD><TD borderColor=#eeece1></TD><TD borderColor=#eeece1 align=right>222222</TD><TD borderColor=#eeece1 align=right>20/07/11</TD><TD borderColor=#eeece1 align=right>30</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Hope you can assist.

 

[Joe4]

OK, I think may see what is going on here. Since the EngineSN field is a lookup field, you may not be able to add new records to your Invoices table if the new EngineSN fields values are not already part of your lookup list.

So I think you may need to append the new EngineSN codes to your lookup table first, then you can append your records to the Invoices table.

 

[ziad alsayed]

dear Joe

all EngineSN used are part of the EngineSerialNUmber table, this is the first thing i checked .

 

[Joe4]

Can you tell me how the EngineSN lookup is set-up/defined on your Invoices table?
Specifically, what are the values for Row Source, Bound Column, and Column Count?

If it is bound to the ID column instead of the Engine S/N column, that could be the problem.

 

[ziad alsayed]

dear Joe

Rowsource: SELECT [EngineSerialNumber].[ID], [EngineSerialNumber].[Engine S/N] FROM EngineSerialNumber ORDER BY [Engine S/N];

Bound Cloumn: 1
Column Count: 2

Allow Multiple Values: No

 

[Joe4]

If you are bounding the first column, and the first column is ID, it is expecting the ID value (not the "Engine S/N" value) to be stored/imported into this table.

So what do the values on your Excel file you are trying to import look like?
If they look like "CM50330U140177H" instead of "1", it will not work.

Maybe you want to change your RowSource to:

Rowsource: SELECT [EngineSerialNumber].[Engine S/N] FROM EngineSerialNumber ORDER BY [Engine S/N];

and your Column Count to 1.

 

[ziad alsayed]

dear joe4

after doing that, the drop down list for the EngineSN filed will be empty , not showing anything even if i open the Dropdrow to choose.
i tried to import the fikestill the same problem, it even didn't import any field.

 

[Joe4]

Take this approach:

Try changing your Append Query to import the ID field instead of the of the Engine S/N field. You can do this by linking the Excel information to your lookup table first, and then have it use the ID from this table in the Append Query for this field.

 

[ziad alsayed]

sorry joe, it seems i am facing serious problem with office 2010, i need to re install it.

soorry again and thanks.