Creating all possible Descriptions & Codes

[gstreichan]

Dear Experts, I am hoping for a solution on this. I have been trying to get a help with GPT and still cannot as well. Please see explanation below:

In MS Access file attached MaterialAttr table I have the following field names and their descriptions:

Position: It is the sorting order the other fields should be worked out;
MAIN_DESCRIPTION: It is the main description of material;
MAIN_DESCRIPTION_CODE: It is the code of MAIN_DESCRIPTION. To be the first part of material code (Material Code = MAIN_DESCRIPTION_CODE & ATTR_CODES);
ATTR_LABEL: It is the label of ATTR_DESC, what actually the ATTR_DESC refers to in the material part;
ATTR_DESC: It is the attribute description, to be placed after a “, “ of MAIN_DESCRIPTION, in other words a continuation of MAIN_DESCRIPTION. Please note 1 MAIN_DESCRIPTION may have one or more ATTR_DESC_CODE: It is the code of ATTR_DESC and shall be placed in addition to MAIN_DESCRIPTION_CODE, no space, just added in on right side of MAIN_DESCRIPTION_CODE.

Explaining the above, now I just need to give you some additional information. The intent of this query is to create a table that will combine sorted by Position, all the possible MAIN_DESCRIPTION &”, “& ATTR_DESC and all the MAIN_DESCRIPTION_CODE & ATTR_DESC_CODE. Please keep in mind, 1 MAIN_DESCRIPTION may have 1 or multiple ATTR_LABEL then it should combine all possible descriptions and codes. One example below (this case MAIN_DESCRIPTION has 2 ATTR_LABEL only but a different main description can have 5, 6, 7, etc:

MAIN_DESCRIPTION: PIPE which has MAIN_DESCRIPTION_CODE: PP

ATTR_LABEL: SIZE, which has:
ATTR_DESC: 1, which has ATTR_DESC_CODE: A
ATTR_DESC: 2, which has ATTR_DESC_CODE: B
ATTR_DESC: 3, which has ATTR_DESC_CODE: C

ATTR_LABEL: THICKNESS, which has:
ATTR_DESC: STD, which has ATTR_DESC_CODE: T1
ATTR_DESC: XL, which has ATTR_DESC_CODE: T2
ATTR_DESC: XLS, which has ATTR_DESC_CODE: T3

Then all results on table should be:

Material_Description: PIPE, 1, STD with Material_Code: PPAT1
Material_Description: PIPE, 1, XL with Material_Code: PPAT2
Material_Description: PIPE, 1, XLS with Material_Code: PPAT3
Material_Description: PIPE, 2, STD with Material_Code: PPBT1
Material_Description: PIPE, 2, XL with Material_Code: PPBT2
Material_Description: PIPE, 2, XLS with Material_Code: PPBT3
Material_Description: PIPE, 3, STD with Material_Code: PPCT1
Material_Description: PIPE, 3, XL with Material_Code: PPCT2
Material_Description: PIPE, 3, XLS with Material_Code: PPCT3

Material_Description and Material_Code is to be referred side by side in separate columns. Whenever a MAIN_DESCRIPTION has more than one ATTR_LABEL, then descriptions should be combined always and not only 1 ATTR_LABEL selected with its code.
Can you help me with a VBA for a query in access that will create such result? I am really exhausted on this, and I am not finding any solution.
This would be tremendous if someone can help.

 

[arnelgp]

it would be easier if you put a "sample" output of your query to an excel workbook.

 

[gstreichan]

it would be easier if you put a "sample" output of your query to an excel workbook.

 

[gstreichan]

That's with only 2 different labels but sometimes will have 3, 4, 5, 6... It is exponential. In case there are 6 different labels then: 1st label has 3 different descriptions and codes, 2nd has 4, 3rd has 7, 4th has 3, 5th has 5, 6th has 2... then all possible descriptions and codes would be: 3x4x7x3x5x2 = 2520 different descriptions and codes. Description may not contain the same attributes written in different order so it should always prioritize the earlier position labels until the next ones have been exhausted. Would mean, the fixed assignment to all descriptions and codes as the MAIN_DESCRIPTION & MAIN_DESCRIPTION_CODE then (if 6 labels): 1st description & code of 1st label, 1st description & code of 2nd label, 1st description & code of 3rd label, 1st description & code of 4th label, 1st description & code of 5th label, 1st description & code of 6th label THEN: MAIN_DESCRIPTION & MAIN_DESCRIPTION_CODE then: 1st description & code of 1st label, 1st description & code of 2nd label, 1st description & code of 3rd label, 1st description & code of 4th label, 1st description & code of 5th label, 2nd description & code of 6th label THEN: MAIN_DESCRIPTION & MAIN_DESCRIPTION_CODE then: 1st description & code of 1st label, 1st description & code of 2nd label, 1st description & code of 3rd label, 1st description & code of 4th label, 2nd description & code of 5th label, 1st description & code of 6th label THEN: MAIN_DESCRIPTION & MAIN_DESCRIPTION_CODE then: 1st description & code of 1st label, 1st description & code of 2nd label, 1st description & code of 3rd label, 1st description & code of 4th label, 2nd description & code of 5th label, 2nd description & code of 6th label, etc, etc...
always exhausting the last labels first and then changing the upper label... and so on.

 

[MajP]

To do this in a query for 2 or 3 labels is doable, but will become a pain beyond that. You will either need to write code to build the query or just do this via code to build a table. I would go with the latter. Here is the query for 2 labels and you would have to add for more.

SELECT [A].[main_description] & "," & [A].[attr_desc] & "," & [b].[attr_desc]
       AS
       Mat_Desc,
       A.attr_label,
       [a].[main_description_code] & [a].[attr_desc_code] & [B].[attr_desc_code]
       AS
       Material_Code,
       B.position
FROM   f_optic AS A,
       f_optic AS B
WHERE  ( ( ( A.attr_label ) <> [B].[attr_label] )
         AND ( ( A.position ) < [B].[position] ) )
ORDER  BY A.position,
          B.position;

With another table you bring in [C] to the where the concatenations and to the sort. After about three it would make my head spin doing it in a query.

Even in code I think I need a few helper queries, or may have to do it recursively since you do not know the amount of nested loops.

Do you have an example with more than 2 labels?

 

[gstreichan]

To do this in a query for 2 or 3 labels is doable, but will become a pain beyond that. You will either need to write code to build the query or just do this via code to build a table. I would go with the latter. Here is the query for 2 labels and you would have to add for more.

SELECT [A].[main_description] & "," & [A].[attr_desc] & "," & [b].[attr_desc]
       AS
       Mat_Desc,
       A.attr_label,
       [a].[main_description_code] & [a].[attr_desc_code] & [B].[attr_desc_code]
       AS
       Material_Code,
       B.position
FROM   f_optic AS A,
       f_optic AS B
WHERE  ( ( ( A.attr_label ) <> [B].[attr_label] )
         AND ( ( A.position ) < [B].[position] ) )
ORDER  BY A.position,
          B.position;

With another table you bring in [C] to the where the concatenations and to the sort. After about three it would make my head spin doing it in a query.

Even in code I think I need a few helper queries, or may have to do it recursively since you do not know the amount of nested loops.

Do you have an example with more than 2 labels?

Yes, those are in all my first post. Perhaps VBA can't help on this.

 

[LarryE]

Can you please post a screenshot of your table and relationship structure so we can assess whether your design is even capable of doing what you want it to do. I suspect it cannot because you are using ACCESS like a spreadsheet, which, of course, cannot work.

 

[MajP]

Yes, those are in all my first post. Perhaps VBA can't help on this.

Sorry I missed the first post, I was looking at the Excel. Yes I think I can do this in vba using a recursive call. I will post what I get.

 

[LarryE]

Here's your data converted to a relational database format with:

1. Multiple products
2. Each product may have multiple SequenceLabels
3. Each SequenceLabel may have multiple Attributes

Note the table and relationship design. It may work better for you without any coding or attempting to use ACCESS as a spreadsheet.

 

[MajP]

1st label has 3 different descriptions and codes, 2nd has 4, 3rd has 7, 4th has 3, 5th has 5, 6th has 2... then all possible descriptions and codes would be: 3x4x7x3x5x2 = 2520

However, that is far from the reality.
For example here is HV Power Cables
Query2 Query2

MAIN_DESCRIPTION	MAIN_DESCRIPTION_CODE	ATTR_LABEL	CountOfPosition
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_CONDUCTOR	3​
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_CONDUCTOR_NUMBER	58​
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_CONSTITUTION_CABLE	159​
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_CROSS_SECTION_MV_CABLE	45​
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_VOLTAGE_MV_CABLE	28​

There is 3*58*159*45*28
This will create records 34,859,160
Groups 162012, and 162103 will also create over 30M records.

I did a solution for this and works for 162104 which only has 2 labels and thus (58*4) = 232 records created, but for the large ones access blows up before finishing. So that everyone knows the final solution would be over 10s of million of records if I am not mistaken.

 

[LarryE]

What is the purpose of doing this? Even if you could get a final list, what purpose does the result fulfill?

 

[gstreichan]

What is the purpose of doing this? Even if you could get a final list, what purpose does the result fulfill?

If you casnnot understand, none of of your f business useless dog.

 

[MajP]

For demo purposes I reduced the number of records per group to a reasonable number. The amount of Main Descriptions, and group Labels stays the same. Just the number of records in each attribute are reduced. This demos that the code handles each Main Description and various amount of attr_labels per main description. So theorectically this meets the requirement. In practicality I doubt you can make 10s or 100s of millions of records.

Below table shows the number of records in each "Attr_Label" group
qryCountOfLabelsByGroup qryCountOfLabelsByGroup

MAIN_DESCRIPTION	MAIN_DESCRIPTION_CODE	ATTR_LABEL	CountOfPosition
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_CONDUCTOR	3​
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_CONDUCTOR_NUMBER	3​
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_CONSTITUTION_CABLE	5​
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_CROSS_SECTION_MV_CABLE	4​
HV POWER CABLES (VOLTAGE > 38 KV)	162101	E_VOLTAGE_MV_CABLE	4​
HV POWER CABLES (> 1KV <= 38 KV)	162102	E_CONDUCTOR	3​
HV POWER CABLES (> 1KV <= 38 KV)	162102	E_CONDUCTOR_NUMBER	5​
HV POWER CABLES (> 1KV <= 38 KV)	162102	E_CROSS_SECTION_MV_CABLE	7​
HV POWER CABLES (> 1KV <= 38 KV)	162102	E_VOLTAGE_MV_CABLE	4​
POWER AND CONTROL CABLES	162103	E_CONDUCTOR	3​
POWER AND CONTROL CABLES	162103	E_CONDUCTOR_NUMBER	6​
POWER AND CONTROL CABLES	162103	E_CONSTITUTION_CABLE	5​
POWER AND CONTROL CABLES	162103	E_CROSS_SECTION_LV_CABLE	7​
POWER AND CONTROL CABLES	162103	E_VOLTAGE_LV_CABLE	4​
FIBER OPTIC CABLES	162104	E_CONDUCTOR_NUMBER	4​
FIBER OPTIC CABLES	162104	E_SPECIAL_CABLE	3​
SPECIAL CABLES	162105	E_SPECIAL_CABLE	4​

Running the code will create all records for each main description as requested.
The code is run from the method LoopMain.

 

[cheekybuddha]

If you casnnot understand, none of of your f business useless dog.

 

[CJ_London]

Post reported. @gstreichan - abusive language will not be tolerated on this forum. Please apologise to @LarryE

 

[Gasman]

If you casnnot understand, none of of your f business useless dog.

Welcome to my IL.

 

[LarryE]

50 years ago, when I was just a young pup comin up (note the reference), a wise old dog told me to just give people like this enough rope. The rest takes care of itself. . Thanks for reporting it @CJ_London, we don't need it in here. I don't mind the dog part; it was the useless part. That wasn't nice. Only my wife gets to call me that.

 

[The_Doc_Man]

If you casnnot understand, none of of your f business useless dog.

As a moderator, sometimes I need to step in to warn people. In the bottom line of the forum page you will see several items on the right-hand side of the line including "terms and rules." Among those rules, in the 4th paragraph, is

You agree to not use the Service to submit or link to any Content which is defamatory, abusive, hateful, threatening, spam or spam-like, likely to offend, contains adult or objectionable content, contains personal information of others, risks copyright infringement, encourages unlawful activity, or otherwise violates any laws.

Your comment to LarryE violates that rule, with regards to "abusive" and "likely to offend." In fact, several people have been offended by your comments - including me. Whether you apologize to LarryE for the insult or not, you are now on notice that continued membership in this forum depends on your future behavior.

If you want other help than what we can offer, go find a pay site because I can guarantee that if you tried that abusive language on other Access sites, you would already be gone. Understand that folks on this forum are willing to help, but we are ALL volunteers. If LarryE wanted to find an alternative to whatever it was that you wanted, that is within his prerogative.

(Taking off my moderator hat): As to another comment:

Perhaps VBA can't help on this.

I'm pretty sure that SQL would have real trouble with this, but MajP is experienced in recursion using VBA and might be able to offer some help. The problem appears to be that you are attempting to generate what looks like a recursive Cartesian JOIN and Access SQL probably cannot help very much without some VBA help. Heck, some of the "big boy" versions of SQL can't do recursion either. So it is no surprise that you are not finding a lot of help. And that is why LarryE and the others are looking for alternatives.

 

[MajP]

MajP is experienced in recursion using VBA and might be able to offer some help

Actually I provided a lot more than help, I provided a complete solution. It does exactly what was asked as far as I can tell, and I demonstrated that in post 13 using a reduced data set. However, as I pointed out the full data set would generate over 100 million records so I choose not to run that assuming access would crash long before completing.

What is strange is that the OP has the time to attack Larry for asking the same question that we are all asking, but cannot be bothered to respond if the proposed solution meets their needs and mark the thread solved. Even though the OP comes across so desperate for help

I am really exhausted on this, and I am not finding any solution. This would be tremendous if someone can help.

I love these people who come asking for help and then only have the energy to criticize those trying to help. I guess they assume the big bucks we make working here should make us be more helpful.

 

[LarryE]

Actually I provided a lot more than help, I provided a complete solution. It does exactly what was asked as far as I can tell, and I demonstrated that in post 13 using a reduced data set. However, as I pointed out the full data set would generate over 100 million records so I choose not to run that assuming access would crash long before completing.

What is strange is that the OP has the time to attack Larry for asking the same question that we are all asking, but cannot be bothered to respond if the proposed solution meets their needs and mark the thread solved. Even though the OP comes across so desperate for help


I love these people who come asking for help and then only have the energy to criticize those trying to help. I guess they assume the big bucks we make working here should make us be more helpful.

When I looked at the file the OP provided, there were 1009 records but it was all in one table, so I thought well, if you are going to attempt something like this with ACCESS, maybe you would have a better chance if the tables were normalized, so I spent just a couple of minutes doing that. It was easy. 3 tables with relationships defined. Then I was delighted that MajP actually came up with a solution, but he stated that it would generate millions and millions of records even if ACCESS could do it. That's when I wondered why do this and how could matching every record to every other record be helpful to anyone. Maybe someone can explain it. I believe I actually understood WHAT the OP was asking, I just didn't understand what generating 100 million records would do for him or anyone. I am very happy MajP was able to provide the code to do it and was even happier he didn't try it. I don't know what would have happened.