Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    87

    Unanswered: How do I Split Up File Names in One Column Field to Multiple Column Fields

    Hello,

    Thanks for checking this out. This is one of my last things to do with Access for now! I can't find anything remotely close to this online. Maybe a pro can chime in.

    Here's the challenge
    In a column of data, I have a field, the field contains image names. Sometimes 1 and sometimes 2 or more, each separated by a ", " (comma and a space). Sometimes there are as many as twenty images in the field. God only knows why this was setup this way.

    Ultimately, I'd like to make an update query that puts each image in their own column. For instance, if there is 1 image, then it would stay where it is, if two, then the first stays, but the second get put into column2. If three, then 2 goes in column2 and 3 goes in columns 3 and so on up to 20 total columns.

    If this were possible, I'd also have to clear out any commas and spaces and add a "/" before each of the image names. But I can do that in a different update query if need be.

    Example 1
    Data: IMAGENAME1.JPG
    What happens? image stays where it is because there's just one.

    Example 2
    Data: IMAGENAME1.JPG, IMAGENAME2.JPG
    Result: IMAGENAME1.JPG stays where it is, IMAGENAME2.JPG moved to "Column2"

    EXAMPLE 3
    Data: IMAGENAME1.JPG, IMAGENAME2.JPG, IMAGENAME3.JPG
    Result: IMAGENAME1.JPG stays where it is, IMAGENAME2.JPG moved to "Column2", IMAGENAME3.JPG moved to "Column3".



    As a backup plan. If this is not possible, is there a way to make an update query that says "Anything after the first comma, including the first comma is deleted"? This way I can use at least one alternate image.


    Thoughts?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the split() function to break a string apart. into an array
    you wil have to do this using a VBA.

    how do you propose storing the resultant values?

    as you are aware I have reservations about your table design., calling it [one big table] fires off warning bells in my mind. I fear your design is not normalised.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2010
    Posts
    87
    I know you have reservation about "ONE BIG TABLE". It was a collection of other tables. I merged all the data in one spot because separated I had 40,000 records and combined it was 59000 records which all need tweaks and such that could not be done separately since there was data that was needed to combined associations (for a related item type of thing) as well as individual descriptions that vary depending on multiple factors. Believe me, the Access dB that I was provided with is both very nicely done and extremely illogical at the same time. Many times I wonder why on earth things were done.

    If you explain what normalized is, I can tell you if it is or not. I basically ran a query to join multiple tables into one to work with it easier!

    As for VBA. I don't know anything about that or even where to begin! Why is it always at the end of a project that one finds out they have to learn a new language to complete something. LOL

    I guess since I don't know how to, then can you provide an update query that says to delete everything from "comma" or , onward? I'll at least have one image to work with. I can figure out VBA another time I guess.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK I'd suggest you stop doing for now and start thinking
    read up on normalisation
    The Relational Data Model, Normalisation and effective Database Design
    Fundamentals of Relational Database Design -- r937.com

    I fear you have take a normalised database and denormalised it.

    from what you have said so far I think you are creating a product file for a web shop
    I guess due to your lack of knowledge on how db's work you've assumed that doens't look right and proceeded to stuff every thing into one table, that means duplicating data.
    SQL is a specialised data manipulation language, its optimised to merge / manipulate data.

    so I'd expect your design to have something like
    a table for manufacturers, including logos
    a table for product types (or category_name if you prefer)
    a table for products
    ...linked to product types in product type id
    ...linked to manufacturer on manufacturer id
    a table for product images linked to products by the product ID

    if you start repeating columns such as image1, image2, column1,column2 then thats a sign the design is not normalised.

    if you start storing the same data in the same column across many rows then thats a sign of a problem (eg you shouldn't have a manufacturers logo associated with a product but with a manufacturer). you can always retrieve the logo as required fromt he manufacturers table.

    you don't need to store everything in one table you can retrieve stuff from a table in a query such as
    Code:
    SELECT my, column, list FROM mytable
    you can limit rows using a where clause
    Code:
    SELECT my, column, list FROM mytable
    WHERE manufactuerID = 1
    you can set a sort order

    Code:
    SELECT my, column, list FROM mytable
    WHERE manufactuerID = 1
    ORDER BY Prodcut_Description
    you can bring together the values from several tables into one query

    Code:
    SELECT Product_Description, products.ID, price, Manufactuers.name, Manufacturers.Logo FROM Products
    JOIN Manufactuers on Manufacturers.ID = Products.ManufacturerID
    WHERE manufactuerID = 1
    ORDER BY Prodcut_Description
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2010
    Posts
    87

    Talking

    I understand and appreciate your instruction but each row / record of data needs to be in a specific format for import. It's got to be this way (at this time).

    The way the import utility works on the cart, each field needs data, otherwise it won't associate anything correctly. It works off MySQL but the dB I've been provided with is Access. My task it to edit the heck out of the Access dB and make it import friendly to the cart import utility which requires data in every row and field (or blank in some).

    So that means for me to remove conflicting characters, preface certain things with things like a "/", make other things lowercase, clean characters out of other fields, combine multiple fields into single fields, delete some original data (because it was merged) and so on. It's complicated and I'm really not a dB guy! (can you tell?) LOL

    I know you're right that I should only assign a "Brand Logo" once and so on, but it would actually be harder to do because of each one of these records is filling a blank in a product / category field in the dB and I don't have the skills to reinvent the cart's dB structure. I have to follow their way! Their entire system works off this data, including item relationships, page specific data calls (like the brand image), product images, alternate images. Heck there are 190 fields here and it's a 140MB .mdb file with 59000 records.

    At this time, I cannot redo! I'm supposed to be done today! Looking more like tomorrow though!

    Hey, maybe once this gets rolling, you can do it in the future it would probably be faster and better. Actually I know it would be. lol

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK its fine to bring together all the data into one row if thats required by another application. BUT that doesn't mean you mangle all your data into one table int he Access DB. you can present everything int he form required using a query when you need to export the data to the outside world, but leave your data normalised.

    why is this an issue?
    well say one of your suppliers changes their logo
    in your approach you have to write a query, repalce every occurance of the logo text. not difficult, but possibly prone to error. if you associate the manufacturer logo with a manufacturer and change it via a form, when you extract your data using a query it automatically appears changed. it also means that the users of the data are responsible for maintaining their data. that means they don't come to you at the last minute demanding x,y & z are changed right now (because its their data, their responsibility). you can build in error checking in the form to ensure that the correct text is specified.

    seriously in my books you need to stop thinking about 'one big table' right noe. tell your boss that you've made an error and you need to rethink the whole strategy. yes its going to affect the delivery time of the project but its going to hgave a serious maintenance issue over time.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if all you want to do is chop off remaining characters then you can do this via an update statement

    Code:
    update mytable
    set mycolumn = left(mycolumn,instr(mycolumn,",")-1)
    where mycolumn like ("*,*")
    the onlynew bit here is
    left(mycolumn,instr(mycolumn,",")-1)
    where we are saying use the value of mycolumn upto buyt not including the "," symbol.
    the instr function searches for the first occurance of the specified characters in the sepcified string. in this case we are lookign for "," in mycolumn. as we don't want to include the comma in the column we subtract one from the value. so thats telling the SQL engine to set the new value of mycolumn to be the same value as the first blockl of text minus the column.

    the where clause is used to process rows which have a comma in them. you wouldnt' want to run the update without the where clause as instr returns 0 if it cannot find the specified characters in the string
    ]
    remember I suggested you should read up on Access string functions.....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •