Table Lookup not working as Expected

[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

 

[spikepl]

Table lookups are a major PITA and should not be used. ( "Table lookups" is when you in the table define that a field is a lookup from some other table. This is an Access-only feature and is a tool of Beelzebub. For non-religious arguments google table lookups at this site.)

The requirement is that this now become a lookup field

I doubt that the requirement was worded like that. The requirement is presumably to normalize your data, so that repeated information is looked up in some appropriate table using a query (in a report/form) using a key, so you store the key instead of the original information. Google, read and assimilate "database normalization". When done, you should have a much better idea of what to utlimately aim for. Work out a small basic example. And then come back here and ask if stuck.

 

[Pat Hartman]

When converting columns from text strings to numericIDs that point to another table, you need to go through a couple of steps and you skipped one.
1. Create a totals query to select the distinct supplier values.
2. Change this to a make table query and run it.
3. Open the new supplier table and add an autonumber PK. Name it SupplierID.
4. Add a new column defined as Long Integer to the original table and name it SupplierID.
5. Create a query that joins the new supplier table to the original table on the text supplier name value. Select the SupplierID column from the original table.
6. Change the query to an update query. In the Update To cell, type the name of SupplierID column from the new supplier table - tblSupplier.[SupplierID] and run the query.

You now have the original text supplier field and the new SupplierID field both populated and you only need one so delete the old text supplier field. At this point, I would change my forms/queries/etc to use the new SupplierID field. I use the Find and Replace add in. If you don't want to do this, rename the SupplierID column back to the original name and hope for the best.

Any queries that rely on showing the text value for supplier rather than the text value will need to be modified. You'll probably have to change forms and reports also.

Even though this seems too much like work, don't revert to a lookup at the table level. Just fix the mess now and move on sure in the knowledge that this is the path to the light.