Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-23-2019, 10:53 AM   #1
lilclie
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
lilclie is on a distinguished road
Splitting Multiple Values in a Single Cell to Many Name Columns

Hi Community,

We have an external data source that may have a random combination of values that are separated by semi-colons. I would like to transpose this information into a table where a single value has dedicated column and if present would show 1 for Yes, 0 for no. Is this possible?

lilclie is offline   Reply With Quote
Old 05-23-2019, 10:58 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,541
Thanks: 29
Thanked 626 Times in 609 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

Hi. Yes, it's possible, but it doesn't sound like doing it the way you're thinking would make it a properly designed database. Instead, you could think about splitting the values into separate records (rows) rather than individual yes/no columns.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
lilclie (05-23-2019)
Old 05-23-2019, 11:11 AM   #3
lilclie
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
lilclie is on a distinguished road
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

Quote:
Originally Posted by theDBguy View Post
Hi. Yes, it's possible, but it doesn't sound like doing it the way you're thinking would make it a properly designed database. Instead, you could think about splitting the values into separate records (rows) rather than individual yes/no columns.
Current constraints with the design of the external data and the interpretation of that data do need an overhaul. I have to workaround them for the time being.

Are you able to provide a solution into multiple columns?

Thank you in advance.

lilclie is offline   Reply With Quote
Old 05-23-2019, 11:22 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,541
Thanks: 29
Thanked 626 Times in 609 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

Quote:
Originally Posted by lilclie View Post
Current constraints with the design of the external data and the interpretation of that data do need an overhaul. I have to workaround them for the time being.

Are you able to provide a solution into multiple columns?

Thank you in advance.
I see. I am not sure there is an automatic or built-in way to do it, so I am thinking you'll need a custom approach. Would you be able to post a sample db for us to play with?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 05-23-2019, 01:14 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,759
Thanks: 76
Thanked 1,529 Times in 1,418 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

This sounds like you would need, in essence, a brute-force method. Are we talking a text file (.TXT or similar) with variable-length records?

It is also not clear what you wanted to see coming out of this process. Can you fake up a few sample records of input and what you might want for output?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-23-2019, 02:21 PM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,749
Thanks: 40
Thanked 3,484 Times in 3,371 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

if the values are known e.g.

datafield
a;b;c
d;e;a
b;c
a;d

etc

then in a query the calc for the 'a' column might be

a: abs(instr(datafield,"a")>0)

substitute "a", with "b" etc for the other columns

to make it fully unique then use

a: abs(instr(";" & datafield & ";",";a;")>0)

or use a function to achieve much the same thing
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 05-24-2019, 04:39 AM   #7
lilclie
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
lilclie is on a distinguished road
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

Thanks for the answers everyone. Instead of building something custom, I will take the suggestion of DBguy and move to a column/row solution.

DBguy, if you are still available, are you able to assist?

lilclie is offline   Reply With Quote
Old 05-24-2019, 06:27 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,541
Thanks: 29
Thanked 626 Times in 609 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

Quote:
Originally Posted by lilclie View Post
Thanks for the answers everyone. Instead of building something custom, I will take the suggestion of DBguy and move to a column/row solution.

DBguy, if you are still available, are you able to assist?
Hi. Yes, but I asked if you could provide a sample db, so we can have a better idea of what you need. (Rather than me just making something up, which you then say you can't use.) So, are you able to provide it?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 05-24-2019, 08:24 AM   #9
lilclie
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
lilclie is on a distinguished road
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

Quote:
Originally Posted by theDBguy View Post
Hi. Yes, but I asked if you could provide a sample db, so we can have a better idea of what you need. (Rather than me just making something up, which you then say you can't use.) So, are you able to provide it?

Yes I can. I'll upload one later tonight
lilclie is offline   Reply With Quote
Old 05-24-2019, 08:30 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,541
Thanks: 29
Thanked 626 Times in 609 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Splitting Multiple Values in a Single Cell to Many Name Columns

Quote:
Originally Posted by lilclie View Post
Yes I can. I'll upload one later tonight
Excellent. Thanks!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Difficulty splitting text string into multiple columns a2z Queries 15 04-28-2018 10:58 AM
Splitting a single field into multiple report textboxes or multi-line textbox DKeith Reports 1 06-04-2013 10:02 AM
Data copied from single cell pastes into multiple cells RaunLGoode Excel 0 05-23-2013 09:04 AM
Splitting One Column Into Multiple Columns hycho Queries 7 09-27-2011 08:11 AM
Splitting +/- values into separate columns rob.lyles Queries 13 04-16-2010 10:15 AM




All times are GMT -8. The time now is 06:51 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World