Access VBA (1 Viewer)

[MannyST]

Hi all, so I'm new to VBA and other scripting, but must say I have learned a lot from those who have helped me. I saved the more complicated tasks that I'm trying to accomplish for last. So I'll do my best to describe what I'm trying to accomplish I'm dealing with a product table/file. 1000's of items with various fields. The field i'm working is "Categories" field which is comprised of joining data from other fields once scripts have been ran on table. The following is an example of the end result of what i'm trying to accomplish:
Default Category/Area Rugs/Area Rugs Catalog,
Default Category/Area Rugs/Rug Colors/Red Rugs,
Default Category/Area Rugs/Rug Sizes/3x4 Rugs,
Default Category/Area Rugs/Rug Patterns/Graphic Rugs,
Default Category/Area Rugs/Rug Brands/Mohawk,
Default Category/Area Rugs/Rug Styles/Contemporary Rugs

The fields that must be searched are named "Colors, Size, Pattern, Brand, Style. That is their name, but the example is how data needs to be returned to the "Categories" field in the exact format/name. I need to run a script for every item and then return the example above.

Can anyone guide me in the right direction?

 

[Micron]

You want to create a multi value field (a type of mvf where several values are concatenated into one string)? Usually a bad idea. Can provide some context as to why, assuming I've interpreted your end goal correctly? No idea how the "Default Category" string fits into the desired output as it doesn't seem to be a field name.

 

[Pat Hartman]

Mushing the fields together is not the correct solution.

If you want to make a list that looks like what you posted, you would use a query.

Select Colors, Size, Pattern, Brand, Style From Yourtable
Group By Colors, Size, Pattern, Brand, Style
Order by Colors, Size, Pattern, Brand, Style;

If you insist on mushing the fields, you can do it, but you might end up with strange sorting due to the variable data contents size for a given field.

Select Colors & "/" & Size & "/" & Pattern & "/" & Brand, Style As Category From Yourtable
Group By Colors & "/" & Size & "/" & Pattern & "/" & Brand, Style
Order by Colors & "/" & Size & "/" & Pattern & "/" & Brand, Style;

 

[MannyST]

Mushing the fields together is not the correct solution.

If you want to make a list that looks like what you posted, you would use a query.

Select Colors, Size, Pattern, Brand, Style From Yourtable
Group By Colors, Size, Pattern, Brand, Style
Order by Colors, Size, Pattern, Brand, Style;

If you insist on mushing the fields, you can do it, but you might end up with strange sorting due to the variable data contents size for a given field.

Select Colors & "/" & Size & "/" & Pattern & "/" & Brand, Style As Category From Yourtable
Group By Colors & "/" & Size & "/" & Pattern & "/" & Brand, Style
Order by Colors & "/" & Size & "/" & Pattern & "/" & Brand, Style;

Thanks, So two are telling me not a good idea. Let me try to provide some more details if I can keep it short. The end result that I'm trying to duplicate is the following:
Default Category/Area Rugs/Area Rugs Catalog,
Default Category/Area Rugs/Rug Colors/Red Rugs,
Default Category/Area Rugs/Rug Sizes/3x4 Rugs,
Default Category/Area Rugs/Rug Patterns/Graphic Rugs,
Default Category/Area Rugs/Rug Brands/Mohawk,
Default Category/Area Rugs/Rug Styles/Contemporary Rugs

It must be in this format and include "Default Category/Area Rugs/Area Rugs Catalog,Default Category/Area Rugs/Rug Colors........./(Red Rugs), will get its value from going to the Color_facet field in the table and add "Red" as this would be the data in field for this particular example. It would then need to add Rugs, The other data/fields to obtain info from and enter into example above would be (3x4=size_facet field) (Graphic=pattern_facet field) (Mohawk=brand_facet field) (Contemporary=Style field) The end result above needs to be populated in a field in table called "categories"

I don't know how this is currently being done, but this field is used for the website and searching purposes for each item/product in table. The website has x options of colors to select from, x options of sizes to select, and so on. I hope that helps clarify my goal and not sure the query is an option. I also assume this is not being done in Access, but it is what I'm using until I can crack the code! Let me know your thoughts now.

 

[The_Doc_Man]

This looks like a "cascading combo box" problem with perhaps three or four such boxes. You can search this forum for "Cascading Combo Box" to see articles on how this is managed.

Part of the way it is managed is that the underlying table will have fields with values like "Colors" and "Sizes" and "Patterns" and "Brands" and "Styles" and then you would have various values appropriate for each table. OR you can use the Entity-Attribute-Value concept, in which case the query to find things would be a little harder but it would be more flexible. You can look up "Attribute Value" in this forum for more reading on that concept.

 

[Pat Hartman]

If you look again at my response, I told you how to concatenate the fields in a query. We have no idea what you need to do with the "category" field though. You mentioned a website. The website should be using combos for searching but most websites (including this one) don't work the way I want themto anyway so who am I to say. If you are sending replacement data to the website database, use what I suggested in the query you use to export the data.

 

[MannyST]

If you look again at my response, I told you how to concatenate the fields in a query. We have no idea what you need to do with the "category" field though. You mentioned a website. The website should be using combos for searching but most websites (including this one) don't work the way I want them to anyway so who am I to say. If you are sending replacement data to the website database, use what I suggested in the query you use to export the data.

Hi Pat, yeah, i'm just trying to figure out someones end result and trying to format/normalize/map data file in access to achieve the same result and to gain knowledge by going through these steps. So far so good, but now it goes a little deeper. So "category" field is where example I provided above return to. I spent this morning trying to add fields in db ([category_facet], [catalog_facet]) in order to achieve the above result. I'm starting to understand what you guys are trying to say. I have no idea how this person came up with that result, because I tried adding fields in db in order to achieve the end result and I'm stumped by the first line " Default Category/Area Rugs/Area Rugs Catalog," I thought I was onto something by adding the additional fields, but hit a wall. Here is a quick example of 2 rows of data w/field names. I added [category_facet] & [catalog_facet]. If we can do without these and achieve the end result, but I don't see how that would be possible. Please let me know if it is possible. I don't see how a query will return the exact same data.

[Category_facet]	[flooring type]	[catalog_facet]	[color_facet]	[size_facet]	[pattern_facet]	[brand_facet]	[Style]	[categories]
Default Category	Area Rugs	Area Rugs Catalog	Red	3x4	Graphic	Mohawk	Contemporary
Default Category	Area Rugs	Area Rugs Catalog	Blue	2x10	Graphic	Mohawk	Contemporary

 

[June7]

Nothing extraordinary about searching multiple fields http://allenbrowne.com/ser-62.html. But if you need concatenated values for web search tool, then use a query with this concatenation.

Entity/Attribute/Value concept.
Not saying this is what you should do, just what it might look like.
Concatenation of attribute values into a string can be done with VBA http://allenbrowne.com/func-concat.html.
A crosstab query could pivot the data.

tblProducts

ProductID	CatNumber	IsActive	ImageLink
1	10000-R	False
2	24317-X	True
3	76564-M	True

tblProductDetails

ProductID_FK	Attribute	Value
1	Type	Area Rug
1	Color	Red
1	Size	3x4
1	Pattern	Graphic
1	Brand	Mohawk

 

[MannyST]

Ok, so I appreciate the responses and suggestions. Maybe I am not being specific enough or maybe I just have a tremendous lack of knowledge. Maybe also just overthinking it?
Let's say this is my table. Reading my previous posts to get a better understanding of what I'm trying to do might give a better understanding.

[Category_facet]	[flooring type]	[catalog_facet]	[color_facet]	[size_facet]	[pattern_facet]	[brand_facet]	[Style]	[categories]
Default Category	Area Rugs	Area Rugs Catalog	Red	3x4	Graphic	Mohawk	Contemporary
Default Category	Area Rugs	Area Rugs Catalog	Blue	2x10	Graphic	Mohawk	Contemporary

The following is exactly what needs to appear in the [categories] field. Exactly like you see it with / & , and extra words that don't appear in the above fields.

Default Category/Area Rugs/Area Rugs Catalog,
Default Category/Area Rugs/Rug Colors/Red Rugs,
Default Category/Area Rugs/Rug Sizes/3x4 Rugs,
Default Category/Area Rugs/Rug Patterns/Graphic Rugs,
Default Category/Area Rugs/Rug Brands/Mohawk,
Default Category/Area Rugs/Rug Styles/Contemporary Rugs

As I stated previously, I added [Category_facet] & [cataglog_facet] fields to table thinking I had it solved. I don't have it solved and that is where I need help or suggestions on how to obtain exactly obtain the above.

 

[June7]

Not making any more sense to me. If you want the text "Rug Patterns" then it either needs to be in field or literal text concatenated. If you want to tack on "Rugs" to size value then either needs to be in field or literal text concatenated.

[Category_facet] & "/" & [flooring type] & "/" & [catalog_facet] & "/" & [color_facet] & " Rugs"

 

[Gasman]

Is this for Amazon? to upload to their website?
I think you would have to create a custom function as you are not consistent with the naming.?
The Brand section is Mohawk, not Mohawk Rugs, yet Style section is Contemporary Rugs not Contemporary, plus is it not even Style, but Styles.?
What would you expect to see if you had Cushion Flooring for Flooring Type?

 

[MannyST]

Is this for Amazon? to upload to their website?
I think you would have to create a custom function as you are not consistent with the naming.?
The Brand section is Mohawk, not Mohawk Rugs, yet Style section is Contemporary Rugs not Contemporary, plus is it not even Style, but Styles.?
What would you expect to see if you had Cushion Flooring for Flooring Type?

Hi Gasman, you are seeing what I'm seeing. Yes not consistent on how the person named the fields right! You are thinking along the same line I am. What are your thoughts....This isn't for Amazon, the only thing that gets put on to Amazon is pic URL once it is formated through Cloudinary, but that is another field and process I have yet to travel.
A custom function is probably the way to go, because I don't understand the logic this person has when he created this......

 

[Gasman]

Well I'd be thinking of making a standard and sticking to it, but I do not know what effect that would have when you upload the field, if it would be classed as a new category.?
Still use a custom function though.

 

[MannyST]

Well I'd be thinking of making a standard and sticking to it, but I do not know what effect that would have when you upload the field, if it would be classed as a new category.?
Still use a custom function though.

Well I appreciate everyone's input and feedback. I guess being new creates a challenge. I have read the Allen Browne articles and I need to digest....lol....I came home today and looked at the file/db i'm trying to replicate by figuring things out. You mentioned Amazon and June7 mentioned a crosstab query could pivot the data . After dissecting db a little more I think I have lead you guys the wrong way and thus the answers provided?
I'm going to call them Parent/Child or Simple/Configurable items. Please correct me if needed. Apparently, it is getting a bit more complicated for me as the result I was trying to achieve was a Parent Record or Configurable record based on the fields provided. I'm not sure how to achieve this function. I'm guessing it is somehow using the [style #] Z0832 [color code] A416 fields to create a Parent [sku] Z0832 A416. It then goes and searches the [sku] and if [style #] [color code] match it will then return the variations for each record/row and thus create the [categories] field. Am I on the right track now and does that make more sense with what I'm trying to achieve? I'm stumped at the part as to how you would drill down and search each field and bring back the pertinent info if the [style #] & [color code] match to create a [categories] field with all the variations of all the child/simple records........Also the [categories] field only returns the data to Parent/configurable items record and none of the Child/simple records have anything entered into the [categories] field.

 

[Isaac]

For whatever it's worth, I have worked for a number of clients who had Amazon or Ebay businesses and needed a huge amount of data manipulation in order to upload all kinds of things. I honestly never even considered trying to use Access for this. The data was nasty, dirty, difficult to correspond to relationships if not impossible, changed constantly (!), and needed a lot of custom code. The formats they needed things in were so crazy - all kinds of conditional repeating of weird things ....
I used Excel VBA every time and wouldn't do it any differently now. I'm not saying it would have been impossible to create tables and queries and relationships to get it done, just wasn't how I'd choose to do it. I am not certain what you are dealing with on input, output, and general purpose of this app but the memories of the Amazon inventory work I've done came to mind when reading this thread. Who knows, maybe you are dealing with a more stable set of facts that do in fact lend themselves to a database - I'm not trying to discourage you from it, just adding perspective.

 

[Gasman]

One of my last attempts to obtain employment was for a small firm that Amazon fulfilled for.
I actually got the job on the Thursday and by Friday, the owner had changed her mind. That pretty much persuaded me to say 'to hell with it' She was using Excel as the source with of course multiple rows with same values.

Anyway your data looked familiar as she gave me a test to upload data to their system that talked to Amazon.

From what I can see, the basics are not hard, you are just concatenating values. Where the complications come in, is they are not consistent, so a lot of Ifs are going to be needed.?

Perhaps a better way would be to store the fieldname in a table and it's respective concatenation value.?
Still use a custom function, just that process would simplify it.?

Attached is a sample file for upload.

Shame about that job, as I saw I could help them a lot and also would enjoy the job, but that is the way it goes.

 

[arnelgp]

hi, me ninja in.
create a Public Function in a Module:

Public Function fnkConcat( _
                        ByVal categ As String, _
                        ByVal floor As String, _
                        ByVal catalog As String, _
                        ByVal color As String, _
                        ByVal size As String, _
                        ByVal pattern As String, _
                        ByVal brand As String, _
                        ByVal style As String) As String
                      
    Dim strRet1 As String
    Dim strRet2 As String
    Dim strKeyword As String
  
    strRet1 = strRet1 & categ & "/"
    strRet1 = strRet1 & floor & "/"
  
    strKeyword = Split(floor, " ")(1)
  
    strRet2 = strRet1 & catalog & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Colors/" & color & " " & strKeyword & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Sizes/" & size & " " & strKeyword & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Patterns/" & pattern & " " & strKeyword & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Brands/" & brand & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Style/" & style & " " & strKeyword
  
    fnkConcat = strRet2
End Function

next, create an Update Query that will update your Category field:

UPDATE
    yourTableName
    SET
    yourTableName.category = fnkConcat([Category_facet],[flooring_type],[catalog_facet],[color_facet],[size_facet],[pattern_facet],[brand_facet],[Style]);

run the this query,
output:

/// edit: look at my field [category] while yours is [Categories]

 

[James Dickinson]

why not just use a table to store each category against the id. Whats the point of this really?

--ahh, should probably have read the above comments first. sorry guys

 

[MannyST]

hi, me ninja in.
create a Public Function in a Module:

Public Function fnkConcat( _
                        ByVal categ As String, _
                        ByVal floor As String, _
                        ByVal catalog As String, _
                        ByVal color As String, _
                        ByVal size As String, _
                        ByVal pattern As String, _
                        ByVal brand As String, _
                        ByVal style As String) As String
                     
    Dim strRet1 As String
    Dim strRet2 As String
    Dim strKeyword As String
 
    strRet1 = strRet1 & categ & "/"
    strRet1 = strRet1 & floor & "/"
 
    strKeyword = Split(floor, " ")(1)
 
    strRet2 = strRet1 & catalog & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Colors/" & color & " " & strKeyword & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Sizes/" & size & " " & strKeyword & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Patterns/" & pattern & " " & strKeyword & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Brands/" & brand & "," & vbNewLine
    strRet2 = strRet2 & strRet1 & strKeyword & " Style/" & style & " " & strKeyword
 
    fnkConcat = strRet2
End Function

next, create an Update Query that will update your Category field:

UPDATE
    yourTableName
    SET
    yourTableName.category = fnkConcat([Category_facet],[flooring_type],[catalog_facet],[color_facet],[size_facet],[pattern_facet],[brand_facet],[Style]);

run the this query,
output:

/// edit: look at my field [category] while yours is [Categories]
View attachment 83036

Hi Ninja, thank you for this example. This is the end result I am looking to achieve. I have no idea how to figure out your script yet. What I think I forgot to add is: I'm looking to create a configurable/parent record from items. So your "category" field would be the parent which is created from the children records/items. Does this make sense and is there any good references on creating Parent/Children items out there with example of vba or sql?