Lookup Not working as I would like

[ladyfrankie]

Hello there,

I hope I describe this well the issue I am facing. Here goes! I am a relevatively new Access Developer, so please bare with me. I hope I have posted this in the correct place.



The Database that I am currently working on is currently in SQL but as an Access 2003 front end.
I have one table called Transactions_Grants. Any transaction that are generated, the details of these are entered into this table. One of the fields in this table is called Supplier Name. Currently this is a free text field, where users can enter in the name of the Supplier.

The requirement is that this now become a lookup field. I have decided that we go with the option of having a one time extract of Suppliers and then this would be maintained by the system Administrator.

So in my test environment (Access database) I created a table called Supplier (it has two columns, PK_Supplier_ID, Supplier Name). Ran an extract from the Finance Database to get all of the suppliers associated with GAP and then loaded it into the Supplier table. I then created another access table called Transactions_Grants (has about 16 Columns, PK-Transaction_ID, Grant Ref.....Supplier_name), and took an extract from the live system from the same table and loaded this in (works out to be about 16,000 rows).

I then went to edit the properties of the Transactions_Grant table (in Access) and change the Supplier column data type to LookUp Wizard. I cycled through the wizard. I came to the point of saving the table and then I got the error message which said, 16000 rows of data would be deleted. When I clicked yes, all the rows that contained information of a supplier, now had no supplier details. To me I think it makes sense, because I had to clean up the Supplier Data before I added to the Supplier table. So my questions are as follows:

* Is there a way of linking the two tables in the way that I want to, without it deleting the contents of that column? Is there room of trying to automate the process of examining the contents of the cell and then cross checking this with potential Suppliers listed in the Suppliers table? Would I have to do this before linking the tables? Would it work then?
* I am currently using Access tables. All the tables are in a SQL database. I am guessing it would work in a similar way? Is what I am trying to do still possible or would I have to have the supplier table in Access and then leave the Transaction_Grant table in SQL?
*I still want the other rows of data that contain a NULL value in the Supplier column to remain Null. Do I need to add a Null value in the Supplier table as a 'Dummy' supplier value?
Any help would be gratefully received.
Kind Regards
LadyFrankie

[pbaldy]

Among the many reasons most of us don't use lookup fields is that they don't work with non-Access back ends:

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

Is having the supplier table in SQL Server an option? You can have one in Access and the other in SQL Server, but with a lot of data you'll have performance problems if you join the two together in a query.