Converting data types

[Bob Wickham]

Hi,

I am appending data from an Excel spreadsheet to an Access table.
The problem is:
A field (called Lender) in the spreadsheet is formatted as Text which is
right because it contains only Text.
The field in the Access table I'm appending it to displays text but is
actually a Look Up to the Numerical ID field of another table.
When I run the Append everything is appended correctly except for this
field (which is left blank).
I'm assuming I have to convert the data types but Access "Help" is poor
in this area.

Any ideas.?

Bob

 

[Allen Browne]

Bob, you will need to import this into a temporary table with a text field
that can import the value.

Then create a query that joins the name in this field with the lookup table
that contains the persons' names. The query can then output the Number for
each person, and you can append the value to the real table (Append on query
menu, in query design.)

In practice, this is how most imports actually work. Because the database
has relational integrity that the spreadsheet or text files don't, you have
to use a temp table, and match the data. There is also an intermediate step
(often quite lengthy) to ensure the data is valid before import.

For example, the Lender name might be misspelled in the spreadsheet, or
there might be a new lender. In the query you created to join the temp table
with your Lender lookup table, double-click the line joining the 2 tables in
the upper pane of query design. Access pops up a dialog with 3 choices.
Choose the one that says:
All records from MyTempTable, and any matches from Lender.
The query will now identify any temp records that don't have a valid lender,
i.e. the fields from the Lender table will be Null where there is no match.
These records need fixing (e.g.correcting the spelling, or adding the new
lender) before the import into the real table can proceed.

 

[Bob Wickham]

Bob, you will need to import this into a temporary table with a text field
that can import the value.

Then create a query that joins the name in this field with the lookup table
that contains the persons' names. The query can then output the Number for
each person, and you can append the value to the real table (Append on query
menu, in query design.)

In practice, this is how most imports actually work. Because the database
has relational integrity that the spreadsheet or text files don't, you have
to use a temp table, and match the data. There is also an intermediate step
(often quite lengthy) to ensure the data is valid before import.

For example, the Lender name might be misspelled in the spreadsheet, or
there might be a new lender. In the query you created to join the temp table
with your Lender lookup table, double-click the line joining the 2 tables in
the upper pane of query design. Access pops up a dialog with 3 choices.
Choose the one that says:
All records from MyTempTable, and any matches from Lender.
The query will now identify any temp records that don't have a valid lender,
i.e. the fields from the Lender table will be Null where there is no match.
These records need fixing (e.g.correcting the spelling, or adding the new
lender) before the import into the real table can proceed.

Thanks Allen,
You've given me a fair bit to work on there and reinforced something I
learnt from Steve Schapel, that I need to understand JOINS. In fact, you
have almost answered my next question which concerns saving (somewhere)
the data that can't be appended.

Bob Wickham

 

[Allen Browne]

Sure, the outer join query chooses the records that cannot be appended. Once
you have that query, you can turn it into an Append query (Append on Query
menu in query design) to add the results to a table you set up for that
purpose.

(I'm guessing that you want to do that so you can empty out the temp table
and so continue with further imports.)

 

[Van T. Dinh]

Just for a bit of explantion, see The Access Web article:

http://www.mvps.org/access/lookupfields.htm

I would try to link the Excel spreadsheet into Access as a Linked Table then
create the Append Query based on this Linked Table and the LookUp Table (to
retrieve the Numerical ID) to append the rows from the Linked Tables with
the Lender text replaced by the LenderID into your final destination Table.

 

[Bob Wickham]

Sure, the outer join query chooses the records that cannot be appended. Once
you have that query, you can turn it into an Append query (Append on Query
menu in query design) to add the results to a table you set up for that
purpose.

(I'm guessing that you want to do that so you can empty out the temp table
and so continue with further imports.)

Yes, continue with further imports but mainly to save the record that
can't be appended so as to figure out why it can't be appended.

Thanks for your help. I'll see how I go.

Bob Wickham

 

[Bob Richardson]

Hi,

I am appending data from an Excel spreadsheet to an Access table.
The problem is:
A field (called Lender) in the spreadsheet is formatted as Text which is
right because it contains only Text.
The field in the Access table I'm appending it to displays text but is
actually a Look Up to the Numerical ID field of another table.
When I run the Append everything is appended correctly except for this
field (which is left blank).
I'm assuming I have to convert the data types but Access "Help" is poor in
this area.

Any ideas.?

It's my experience that it's bad technique to store the numerical ID in your
table. Store the text value instead. Sure this makes your table much bigger,
but it's a lot more flexible.

In your situation, what if a Lender goes out of business, or you decide to
add a new lender? You certainly don't want to keep the old Lender in the
drop down list of possible data entries for new records.

Store the text in the table and use a drop-down list JUST in your data entry
form.