Appending text to a Lookup field

[Emmy]

I need to append data from a sheet to an existing table. One of the fields in the existing table is a lookup field (its data type is a number.)

How can I write a query to append a string of text to this field?

If I append the primary key which corresponds to the text, it works, but the inserting the text itself doesn't work.

(i.e. apple's primary key is 62. If I have "apple" in my sheet that I want to append to the table, it doesn't work - nothing shows up. But if I have "62" in my sheet, then it appends it.)

Thanks very much in advance.

 

[Pat Hartman]

In your append query, add the lookup table and join to it on the text field. Then rather than referencing the text field in the append, reference the numeric field.

INSERT INTO YourTable ( Fld1, Fld2 )
SELECT S.Fld1, L.FldNum
FROM Sheet1 as S INNER JOIN tblLookup as L ON S.TextVal = L.Description;

 

[Emmy]

Thanks very much for your reply. Sorry, I don't know how to use SQL...currently I am just using "Design View" to create my queries in Access so I am a bit lost.

>In your append query, add the lookup table >and join to it on the text field. Then >rather than referencing the text field in >the append, reference the numeric field.

Sorry, I don't really understand what you mean...the text field is what I need to append, I have no numeric fields - the only numeric field is the lookup table in the sheet that I want to append the text field to.

 

[David R]

But the numeric field is what is stored in your table as the lookup value. That is why you have to append it, instead of the value you're used to seeing, "apple".

Try going into your current query, and look at it in SQL view (don't worry, you can change it back before your brain explodes!). Compare that to what Pat showed you, and try to create a query in SQL view like Pat's but with your proper field/table names. Then convert it BACK to Design View and see what it looks like.

Post back if you have further troubles,
David R

 

[Emmy]

Thanks for your reply. Sorry, I am still lost and am not sure what I need to do. From Pat's example, which table is the lookup table, S or L? How does Access know that when it sees "apple" from the imported table to append "69" to the existing table? Is this through a join? Thanks very much for your help.

 

[Emmy]

I followed Pat's example, and I get the error message "Type Mismatch" - from design view, the text field is joined to the lookup field, but since one is text, and the other is a number, there doesn't seem to be a way around this......? Any suggestions would be much appreciated, thanks so much.

 

[Pat Hartman]

You need to join the two tables on the TEXT fields BUT append the NUMERIC field from the lookup table NOT the text field from the spreadsheet link.

 

[Emmy]

Thanks for your reply. Sorry, I am still very confused. The two tables I have are:
Table 1: contains the imported data from my spreadsheet
Table 2: The table I want to append to, which contains the lookup field
By appending the numeric field (the lookup field) to itself, how does the table that contains the imported data (which is what I want to append to table 2) fit in?

 

[David R]

OK, point of clarification: Are we adding a line to the lookup table, or to the data table? That will affect how you go about it.

I think perhaps we misunderstood the question.

David R

 

[Pat Hartman]

Aren't there really three tables. The two you mentioned plus the lookup table. Let me start with a very simple but more specific example. You have a lookup table for sex code as follows:

SexCodeId, SexCode
1, Female
2, Male
3, Unknown

You have an external datasource that happens to be an excel table. It has several fields as follows:
StudentId, LastName, FirstName, SexCode
2345, Smith, Mary, Female
1233, Jones, Tom, Male

You want to append the data from the spreadsheet to your access table which has the following fields:
StudentId, LastName, FirstName, SexCodeId
3344, Jagger, Mick, 2
4456, George, Boy, 3

To do this, add the lookup table to the append query. Join the spreadsheet to the lookup table on SexCode (the text value). Then rather than appending SexCode (the text value) from the spreadsheet table to your main table, append SexCodeId (the numeric value) from the lookup table. By adding the lookup table to the append query, you have given your query the ability to translate the text value that you have to the numeric value that you need.

 

[Emmy]

Thanks very much for your help, I really appreciate it!! I followed your example, but depending on how I join the tables, it either appends the same record dozens of times, or I receive the error message:

"The SQL statement could not be executed because of ambigious outer joins. To force one of the joins to be performed first, create a separate query that performs the first join, and then include that query in your SQL statement".

I have sheet1 (the external datasource) left joined to the lookup table. The lookup table is inner joined with the table I want to append to (if I don't inner join it, I don't receive an error message, but it appends the same record many, many times)

I don't know SQL (I created it using Design View in MS Access), and I can't look at the SQL view either b/c of that error message; is there a way to force the left join to occur first? For my final query, I actually have 5 tables in total, 3 of which are lookup. In this situation, how I can force the joins from sheet 1 to the lookups to occur first, then the joins from the lookup to the table I need to append to?
Thanks very much for your help and time.

 

[Pat Hartman]

Remove the table that you are appending to from the QBE grid. The QBE grid should contain only the spreadsheet table and the lookup table. The table being appended to is specified by clicking on the query menu and choosing the append type. Access will prompt for the name of the table that you want to append to.

There are three items that are set for each field - Field, Table, and AppendTo. Field and Table specify where a field comes from and AppendTo specifies where it goes. The combobox of the AppendTo will show all the fields in the table that you chose following the query type selection from the query menu. The Table field controls which fields show in the combo box for the Field field. If you select spreadsheet as the table, the spreadsheet table's fields will show in the Field list. If you select the lookup table, those fields will show. Does this give you enough information to follow my directions? If not, post your SQL and we'll correct it.

 

[Emmy]

Thanks so much Pat; it works! Thank-you so much for your help, I really appreciate it!

Thanks again,
Emmy