Split Delimited Field to New Record (1 Viewer)

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.

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.

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.

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???

ProdNo is an additional PK.

Are you suggesting the juction table should have an Autonumber PK as well?

Put an autonumber PK in every table.

Ok. Done. What's next?

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?

Me!Fiesty said:

ProdNo is an additional PK.

Are you suggesting the juction table should have an Autonumber PK as well?

Click to expand...

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.

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.

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.