Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-08-2019, 01:44 PM   #16
EzGoingKev
Newly Registered User
 
Join Date: Nov 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
EzGoingKev is on a distinguished road
Re: "Unconcatenating" Data In A Field

The zip folder is in a OneDrive folder. I tried to post the share link to it but like I said, you need (10) posts to post an image or a link.

EzGoingKev is offline   Reply With Quote
Old 11-08-2019, 01:47 PM   #17
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: "Unconcatenating" Data In A Field

Quote:
Originally Posted by EzGoingKev View Post
The site will not let me post a link due to my post count.
Did you try attaching it as a zipped file?
__________________
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 offline   Reply With Quote
Old 11-08-2019, 01:57 PM   #18
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,390
Thanks: 434
Thanked 804 Times in 779 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: "Unconcatenating" Data In A Field

Quote:
Originally Posted by EzGoingKev View Post
The zip folder is in a OneDrive folder. I tried to post the share link to it but like I said, you need (10) posts to post an image or a link.
You upload it as an attachment via the Manage attachments button in Advanced post mode.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

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.
Gasman is offline   Reply With Quote
Old 11-08-2019, 01:57 PM   #19
EzGoingKev
Newly Registered User
 
Join Date: Nov 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
EzGoingKev is on a distinguished road
Re: "Unconcatenating" Data In A Field

OK I attached it.

#1 is how the data is in the linked table. I can do nothing about that.
#2 is an example of how I would like it unconcatenated.
#3 is the final format I will put it in so I can query it against another table.
Attached Files
File Type: zip unconcat_sample.zip (19.6 KB, 6 views)
EzGoingKev is offline   Reply With Quote
Old 11-08-2019, 02:22 PM   #20
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: "Unconcatenating" Data In A Field

Quote:
Originally Posted by EzGoingKev View Post
OK I attached it.

#1 is how the data is in the linked table. I can do nothing about that.
#2 is an example of how I would like it unconcatenated.
#3 is the final format I will put it in so I can query it against another table.
Hi. Thanks. Check out the attached.


There are several ways to approach this. I'll describe two methods, and explain the one I used.


  1. The first method would be a full VBA solution where you open the first table and loop through the records and create a separate record for each pair of MODEL_CODE and ENH_MODEL
  2. This is the method I decided to use:
  • Using Allen Browne's ConcatRelated() function or my SimpleCSV() function, I created a long string combining all ENH_MODEL from Table1
  • I then use the Split() function to put each value in an array and then looped through the array to create a table of ENH_MODELs
  • Now, using a query, I am able to duplicate your Table2
  • From here, you should be able to create a query that looks like Table3

Hope it helps...
Attached Files
File Type: zip unconcat_sample.zip (28.5 KB, 7 views)
__________________
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 offline   Reply With Quote
Old 11-08-2019, 02:56 PM   #21
EzGoingKev
Newly Registered User
 
Join Date: Nov 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
EzGoingKev is on a distinguished road
Re: "Unconcatenating" Data In A Field

DBGuy -

Thanks for sending that over.

The funny thing is that after I posted the sample data up I thought about taking the other data set I was going to link to, combining the coding with the backward slash in the middle and doing what you did in your query.

Last edited by EzGoingKev; 11-08-2019 at 03:43 PM.
EzGoingKev is offline   Reply With Quote
Old 11-08-2019, 03:28 PM   #22
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: "Unconcatenating" Data In A Field

I'd say give it a try and let us know how it goes.

__________________
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 offline   Reply With Quote
Old 11-08-2019, 03:48 PM   #23
EzGoingKev
Newly Registered User
 
Join Date: Nov 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
EzGoingKev is on a distinguished road
Re: "Unconcatenating" Data In A Field

My way does not work.

One model code is paired with 73/23475. Using the Like "*" & [FieldName] & "*" it comes back with both 73\23475 and 3\23.

DBGuy, I just added the those codes to the tables and ran the query. Yours does the same as mine.

Last edited by EzGoingKev; 11-08-2019 at 04:28 PM.
EzGoingKev is offline   Reply With Quote
Old 11-08-2019, 05:07 PM   #24
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: "Unconcatenating" Data In A Field

Quote:
Originally Posted by EzGoingKev View Post
My way does not work.

One model code is paired with 73/23475. Using the Like "*" & [FieldName] & "*" it comes back with both 73\23475 and 3\23.

DBGuy, I just added the those codes to the tables and ran the query. Yours does the same as mine.
Hi. That's why it's important we get to see a good set of sample data. Can you repost the db with the new set of data that is not working, so I can check why it doesn't work? Before you do though, please make a table with the result you're getting, so I could have something to compare with. 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 offline   Reply With Quote
Old 11-14-2019, 04:07 PM   #25
EzGoingKev
Newly Registered User
 
Join Date: Nov 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
EzGoingKev is on a distinguished road
Re: "Unconcatenating" Data In A Field

Sorry I had a lot going on. My niece wrote a VBA for me that took care of it.

Thanks for the help.
EzGoingKev is offline   Reply With Quote
Old 11-14-2019, 04:13 PM   #26
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,036
Thanks: 115
Thanked 3,019 Times in 2,746 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: "Unconcatenating" Data In A Field

It might help others in the future if you posted the code your niece kindly wrote for you
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


Website links:
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.
,
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.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
Old 11-14-2019, 05:24 PM   #27
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: "Unconcatenating" Data In A Field

Quote:
Originally Posted by EzGoingKev View Post
Sorry I had a lot going on. My niece wrote a VBA for me that took care of it.

Thanks for the help.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

__________________
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 offline   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
If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2" shdale Queries 8 10-29-2014 05:20 AM
Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"? derekbeck Queries 5 03-26-2012 03:23 PM
Adding "attachments" field to table is "corrupting" it? starrcruise Tables 3 11-14-2011 06:39 AM
Qry To Exclude Record If All Fields On Page Is Blank Except Field "xx", "yy" And "zzz vancey Queries 0 03-17-2011 01:43 AM
Defaulting a "Medium Time" field to be "p.m." instead of "a.m."? Cobo Tables 4 04-26-2010 02:37 PM




All times are GMT -8. The time now is 03:23 AM.


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