Insert from excel to table with look up

[chris89]

Hello everyone!
I'm trying to import data from excel to an existing table (table1) where one of the fields in the table is lookup in other table(table2) which has Number as datatype (bound value) although in excel it is as text, so when I import I get error (unmatched data types).I thought to create a query for table2 and set the lookup of table 1 to this query, but I' m not really sure if this is the best way to do this!
Is there smthing else I can do?

Thank you in advance

 

[jleach]

Hi,

Generally we avoid using lookups on a table level (ref: http://access.mvps.org/access/lookupfields.htm )

That said, there's quite a few times when a direct import to a final table from some outside source doesn't work as well as we'd like. A common solution is to import "as is" into a temp table, then use Access to convert/select as required to insert the stuff to it's final destination.

hth

 

[chris89]

I 've looked in the Northwind db where in the Purchase Table - Supplier ID field the look up uses fields from Suppliers Extended query .

Would it be meaningful to look up (text) values on a query that was created from a related table?

 

[jleach]

I'm not familiar with the Northwind DB really, but just as a general statement: don't confuse lookups with lookup fields.

A lookup field is when you set the lookup information during the table design, and this is what the link I have talks about. However, just because it screws with relationships doing it at table level doesn't mean you can't handle it on form level (remember that users should never have direct access to tables or queries, but always through a form or report - this is one of many reasons why).

Now, lets say you want some lookup... call it ItemType, for a table of Items.

tblItems
.fldID
.fldItemType

ideally, fldItemType will look up off a table of.. well, Item Types:

tblItemTypes
.fldID
.fldDescription

(the preference of using a numberic or text ID is just that: a preference. I've included a numeric ID here though because numberic comparisons, sorts and indexing are generally faster than strings (text)).

So now, rather than setting up a lookup field through tblItems' table design IU, you just leave it a numeric field, maybe create a relationship (1 ItemType has Many Items) if you see fit.

Then, because you follow good practice and never let a user see a table or query directly, you go ahead and set up a combobox we'll say (or listbox, if you prefer), and: 1) base the ControlSource of the box to tblItems.fldItemType (where the value is saved), and 2) base the RecordSource of the box to tblItemTypes (where you get the value that will be saved).

There you have it... a lookup without the evil that goes with it on a table level.

Now, I didn't quite understand your question, but I'm hoping the above will cover it...

cheers,

 

[chris89]

Very nicely explained!
I 'll try that and post back , however I think I will have a problem on importing the existing data from excel
Thanks for your time!

 

[jleach]

No problem. Just remember that all this stuff would be AFTER you've imported (possibly to a temp table to shape up the import) and placed the data in their final tables.

Good luck!

 

[chris89]

I'm not familiar with the Northwind DB really, but just as a general statement: don't confuse lookups with lookup fields.

A lookup field is when you set the lookup information during the table design, and this is what the link I have talks about. However, just because it screws with relationships doing it at table level doesn't mean you can't handle it on form level (remember that users should never have direct access to tables or queries, but always through a form or report - this is one of many reasons why).

Now, lets say you want some lookup... call it ItemType, for a table of Items.

tblItems
.fldID
.fldItemType

ideally, fldItemType will look up off a table of.. well, Item Types:

tblItemTypes
.fldID
.fldDescription

(the preference of using a numberic or text ID is just that: a preference. I've included a numeric ID here though because numberic comparisons, sorts and indexing are generally faster than strings (text)).

So now, rather than setting up a lookup field through tblItems' table design IU, you just leave it a numeric field, maybe create a relationship (1 ItemType has Many Items) if you see fit.

Then, because you follow good practice and never let a user see a table or query directly, you go ahead and set up a combobox we'll say (or listbox, if you prefer), and: 1) base the ControlSource of the box to tblItems.fldItemType (where the value is saved), and 2) base the RecordSource of the box to tblItemTypes (where you get the value that will be saved).

There you have it... a lookup without the evil that goes with it on a table level.

Now, I didn't quite understand your question, but I'm hoping the above will cover it...

cheers,

Please forgive my ignorance but where should I create a combobox with ControlSource of the box to tblItems.fldItemType and RecordSource of the box to tblItemTypes??

 

[jleach]

Please forgive my ignorance but where should I create a combobox with ControlSource of the box to tblItems.fldItemType and RecordSource of the box to tblItemTypes??

A combobox is a control, and thus would be placed on a form.

Do you have any forms set up? Do you understand how a form binds to a table or query? If not, no biggie, I'll be more than happy to explain.

Cheers,

 

[chris89]

Could you please post a sample database on how the tables are and the relationships between them ?

+1000 thank you

 

[jleach]

See the attached.

Note:
tblItems.fldType has a number value which corresponds with tblItemTypes.fldID. There are no relationships.

Also Note:
The ComboBox on the form, as mentioned, has a ControlSource (where the data is saved), and a RowSource (where it gets its list of stuff from). There's two columns in the rowsource (tblItemTypes.fldID and tblItemTypes.fldDescription). Note the property that says "Bound Column" and is set to "1" - this means the first column (tblItemTypes.fldID) is bound to the ControlSource (that is, when it saves the value to the control source, it will save the value in column 1).

Also note the format of the combobox is set to hide column 1 (the ID), it has a width of 0. Simple as that.

The binding can be a little confusing, but it's an important concept. Look at the form's properties and you'll see that the form itself is Bound to the tblItems table (the recordset property of the form is tblItems).

If you see people talking about what a form or report is bound to, they're talking about the table or query that drives it. Likewise, when they talk about a Bound Contol, they're talking about fields in the form's recordset that are bound to each specific control...

hope that helps. Let me know if you need a hand further deciphering.

Cheers

 

[chris89]

Ok now I get it!I used the Control Wizards by now for comboboxes storing the value at prefered place..
Well thank you so much ! I will try your way now