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

 
Reply
 
Thread Tools Rating: Thread Rating: 17 votes, 5.00 average. Display Modes
Old 09-19-2013, 09:57 AM   #1
Me!Fiesty
Newly Registered User
 
Join Date: Mar 2012
Posts: 7
Thanks: 1
Thanked 0 Times in 0 Posts
Me!Fiesty is on a distinguished road
Split Delimited Field to New Record

I need help writing a function to split the image names. In essence, I need to create a separate record in a related image table. I have searched over and over again I can't seem to produce the results I need.

I would like to review the records in query so that I can add criteria before appending them.

BV-GB bv-gb.jpg
CSO-C sso-c, cso-c, eso-c.JPG

The number of images for each product varies as well.

Please help.

Thanks in advance.


Last edited by Me!Fiesty; 09-19-2013 at 10:05 AM. Reason: paste didn't work right
Me!Fiesty is offline   Reply With Quote
Old 09-19-2013, 11:58 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 90
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Split Delimited Field to New Record

Can you be very explicit in what you need?

It seems you have Products and each Product can have 0 or many images.

tblProduct -->tblImage

tblProduct would have fields such as

ProdId PK
ProdName
ProdDescription
other Prod specific info...

tblImage with fields such as

ImageId PK
ProdId FK to tblProduct
ImageName
ImageLocation (folder/file etc)

Hope this is helpful.
jdraw is online now   Reply With Quote
Old 09-20-2013, 12:57 PM   #3
Me!Fiesty
Newly Registered User
 
Join Date: Mar 2012
Posts: 7
Thanks: 1
Thanked 0 Times in 0 Posts
Me!Fiesty is on a distinguished road
Re: Split Delimited Field to New Record

So....my project....retrieving data from distributors and vendors (some small, some large), taking their spreadsheets and preparing for export to our applications and website.


tblNewData
ProdNo.........(text, not primary, indexed, duplicates ok)
Desc...........Copied from OrigDesc (changes are made to this field)
ImgName....text (the same data for each color product & size combo)
Cost
MSRP

Appending the data to local tables.


tblProduct
ProdID........(AutoNumber)
ProdNo........(PK)
Desc
Cost
MSRP


tblImages
ImgID.............AutoNumber
ImgName.........text (indexed, no duplicates - name may change)
RelPathLcl.......Relative local path


tblProdImage
ProdNo...........Lookup to tblProduct.ProdNo (Duplicates Ok)
ImgName........Lookup to tblImages.ImgName (Duplicates Ok)


tblNewData.ImgName contains data such as:


ProNo ImgName
BV-GB......bv-gb.jpg
BZ-SA......
CSO-C......sso-c, cso-c, eso-c.JPG


The number of images vary for each item. Some have none. I need to:


1. Split the tblNewData.ImgName field values and append to tblImages.ImgName with each unique imgname


2. Append tblProdImage with each unique combination of ProdNo & ImgName


I only just started this project and my experience with vba is to the past year....learning on the fly so to speak. I've written a few custom functions for processing data conversions but I can't seem to make this one work.

Me!Fiesty is offline   Reply With Quote
Old 09-20-2013, 07:58 PM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 90
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Split Delimited Field to New Record

Your junction table tblProdImage should have a compound PK made up of the Pk of tblProduct and the PK of tblImages.

Why do you have ProdId autonumber but ProdNo as the PK???
jdraw is online now   Reply With Quote
Old 09-21-2013, 08:41 AM   #5
Me!Fiesty
Newly Registered User
 
Join Date: Mar 2012
Posts: 7
Thanks: 1
Thanked 0 Times in 0 Posts
Me!Fiesty is on a distinguished road
Re: Split Delimited Field to New Record

ProdNo is an additional PK.

Are you suggesting the juction table should have an Autonumber PK as well?
Me!Fiesty is offline   Reply With Quote
Old 09-21-2013, 08:55 AM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Put an autonumber PK in every table.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 09-21-2013, 10:22 AM   #7
Me!Fiesty
Newly Registered User
 
Join Date: Mar 2012
Posts: 7
Thanks: 1
Thanked 0 Times in 0 Posts
Me!Fiesty is on a distinguished road
Re: Split Delimited Field to New Record

Ok. Done. What's next?

Me!Fiesty is offline   Reply With Quote
Old 09-21-2013, 11:49 AM   #8
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
I responded to your post number 5. As far as what's next, what is the current problem you are trying to solve? You are linking images to products with a many-to-many relationship. You have just added a unique ID to your junction table, tblProductImage. What's next?
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 09-21-2013, 12:09 PM   #9
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 90
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Split Delimited Field to New Record

Quote:
Originally Posted by Me!Fiesty View Post
ProdNo is an additional PK.

Are you suggesting the juction table should have an Autonumber PK as well?
Every table has a Primary Key (should have). But only 1. It is possible to have a compound PK in some tables. Compound simply means that in some cases a combination of fields is required to make a unique value (Primary Key).

In junction tables you can have a compound key, or you could have an autonumber as the unique PK. I use this technique quite often. If you do use an autonumber as PK, then you should make a compound unique index from the PKs of the related tables. This unique index is needed to prevent duplicates.

Good luck.
jdraw is online now   Reply With Quote
Old 09-22-2013, 08:12 AM   #10
Me!Fiesty
Newly Registered User
 
Join Date: Mar 2012
Posts: 7
Thanks: 1
Thanked 0 Times in 0 Posts
Me!Fiesty is on a distinguished road
Re: Split Delimited Field to New Record

Some of the data sets that I'm working with are massive (some in the millions) with dozens of different vendors. The way the data is delivered varies greatly as well. I have Central database with all the products and tables for commonly used product options and categories such as size, color, finish, material & so on. All of these tables have a single AutoNumber PK for each.

Each vendor has it's own seperate database for the purpose of prepping the data received with links to the central database for the necessary categories tables.

I import the data and append them to a single temporary table. No PK. Don't need it. The product number suffices at this stage. The NewData (temporary table) has the category fields linked to the central db. I update the data ie...format the descriptions, changing case, removes unwanted characters, spaces and so. I assign the necessary values to size, color etc.

I append the records to a final products table in each vendor's db which has an AutoNumber PK where only new records will be added or updated for pricing.

From there I append the data to the central db which has an autonumber PK in each of the table but storing the Autonumber PK and VendorID from each vendor's product record.

When you're building a house you start at the foundation and work your way up once peice, one section at a time.

So that being said....What's in a name?

Autonumber or no, PK or no, how do I go about splitting a delimited field (no matter which field) and creating records for each in a another table? The source field and target field (or even table names for that matter) may be different in different dbs but find/replace works great for that.

ImageName
abcd, abcf, efg.jpg

TO (in another table):
ProdNo abcd.jpg
ProdNo abcf.jpg
ProdNo efg.jpg

OR (I can update the extension later):
ProdNo abcd
ProdNo abcf
ProdNo efg

I would truly appreciate some guidance, a starting point to go from.

Last edited by Me!Fiesty; 09-22-2013 at 08:19 AM. Reason: clarification
Me!Fiesty is offline   Reply With Quote
Old 09-22-2013, 12:33 PM   #11
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Check out the VBA.Split() function, which returns an array of elements delimited by (a) character(s) you specify. Also, do you know how to use recordsets? There are a couple of topics to look into.

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Reply

Tags
array , new record , split

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
One column has comma delimited data, split into a distinct row Rx_ Code Repository 1 01-09-2015 10:06 AM
split delimited field into different rows mazz Modules & VBA 11 04-25-2014 12:16 PM
Question Populating Combo Box with delimited field NeutronFlux General 6 08-23-2012 09:13 AM
How to insert split MONTH/DAY/YEAR combobox values into date field in new record? arashii Forms 6 02-13-2007 08:19 AM
Trying to create a module that loops throw a record set to split a field in a table sam77755 Modules & VBA 7 02-09-2005 11:07 AM




All times are GMT -8. The time now is 04:36 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