Normalizing a linked table with comma delimited values (1 Viewer)

MilaK

Registered User.
Local time
Yesterday, 22:01
Joined
Feb 9, 2015
Messages
285
Hello,

I would like to normalize a linked table from another database. The values that I need to link to another table are in comma delimited “Value” field. Could you please suggest how to accomplish this?

I’m thinking I need to create a Temp table and run code/query to normalize the table. I’m not sure which query would work for this.

Please see the attached image below.

Thanks,

Mila
 

Attachments

  • Table1.png
    Table1.png
    28.4 KB · Views: 112

plog

Banishment Pending
Local time
Today, 00:01
Joined
May 11, 2011
Messages
11,613
I don't think you need a temp table, you can build the correct table and dump the properly normalized data in there. A few questions:

1. Is this a one time thing? Or will you need to go through this process many times (daily, monthly, etc)?

2. Whats the most values [Value] holds? I only see 2 in the data you posted, but could there be more?
 

MilaK

Registered User.
Local time
Yesterday, 22:01
Joined
Feb 9, 2015
Messages
285
It will be done periodically monthly but maybe weekly. There is no limitation of how many values "Values" field can hold.
 

plog

Banishment Pending
Local time
Today, 00:01
Joined
May 11, 2011
Messages
11,613
With that information and looking at the data closer, I would do a full warehouse on this data. Build a normalized table structure to accomodate all the data you need--with the correct data types (Signed Off Date and Patient id look like they are stored as text).

Then, everytime I wanted to load the data I would truncate all my tables' data, then run my import process. For everything but the [Value] field that would be simple APPEND queries. For the [Value] field you would need to use VBA because you are uncertain how many values are in the Value field. This will involve the using a recordset to get all the values you will need, looping through those records, using the Split function (https://www.techonthenet.com/access/functions/string/split.php) to get the unique [Value] values and then another loop do do the DoCmd.RunSQL of an INSERT statement.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:01
Joined
May 7, 2009
Messages
19,169
Bring your table in design view to see the "id" and "description" of the lookup table. When you had identified you can create a query:

Select value.value , lookuptable.desciption from yourtable left join lookuptable on yourtable.value.value=lookuptable.id;
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:01
Joined
Feb 19, 2002
Messages
42,981
You need to do this with a code loop. Inside the loop that reads the linked table, you need to use the split function to separate the comma delimited field into an array and then loop through the array to write 1 to n records.
 

Users who are viewing this thread

Top Bottom