Solved Facet Query In. Access (1 Viewer)

MannyST

Member
Local time
Today, 05:07
Joined
May 23, 2020
Messages
42
Hi. I'm not sure if this is a thing in ACCESS "Facet Query" or it's called something else, but .the same function The concept is to create return data to field called [price_facet]. The data i'm working on will be used in Magento so essentially it needs to end up in a certain format (faceted format) which will be used for cataloging/categorizing. The categories for the [facet_price] field are (Under $100 | $100 - $250 | $250 - $500 | $500 - $750 | $750 - $1000 | $1000 & Above)
SKUPRICEPRICE_FACETPRODUCT TYPE
Z1009 A43164.45Under $100|$100 - $250|$500 - $750|$250 - $500|$1000 & Aboveconfigurable
Z1009 A431 024036 EC64.45Under $100simple
Z1009 A431 024060 EC85.8Under $100simple
Z1009 A431 024072 EC99Under $100simple
Z1009 A431 024096 EC130.35$100 - $250simple
Z1009 A431 030120 EC214.5$100 - $250simple
Z1009 A431 036060 EC173.25$100 - $250simple
Z1009 A431 048072 EC207.9$100 - $250simple
Z1009 A431 060096 EC334.95$250 - $500simple
Z1009 A431 072108 EC460.35$250 - $500simple
Z1009 A431 096120 EC660$500 - $750simple
Z1009 A431 120168 EC1146.75$1000 & Abovesimple
The above are selected records from a table. All records in the table are simple/children records. A configurable/Parent record is created. I do not know what that process is or how to even begin. I'm assuming the easiest way is to first "facet" the simple records with a function that I don't know what to call or how to create. Then once the simple records are "faceted", it then creates a configurable record as shown above, it uses the 1st record to do so. Thoughts/direction....Thanks.
 

Micron

AWF VIP
Local time
Today, 05:07
Joined
Oct 20, 2018
Messages
3,476
I don't get the first record, but the rest seem to make sense. One way would be to have a function return the text value (e.g. $250 - $500) in a query calculated field - not hard and would allow you to modify if need be when inflation rears its ugly head. A query field with a monster IIF function could also do this, but IMO you have too many possible returns for any sort of query field that uses such long expressions. Since my paradigm seems to be about code, I'll wait to see if anyone has a better idea. Then again, I know of someone who will throw code at every problem raised here, so I will likely be beaten to it anyway.

EDIT - would be helpful to know is whether your price field is text or numeric data type. Also, after reviewing I'm not as clear as I thought. What you show is the desired result but the facet field is not populated? Or it is, and that is the table of records, in which case it's not a desired result but an input. If that's the input, I don't understand what the output is supposed to be. Maybe you want to create dynamic ranges (the 'configurable' record) based on values which are unknown at the time. If that's it, I'm definitely not your guy.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:07
Joined
May 7, 2009
Messages
19,170
sheet1 table is the same as the one you posted
table1 is a lookup table.
there is a function in Module1, apPriceFacet.
the function is called in FinalQuery.
 

Attachments

  • price_facet.zip
    35.5 KB · Views: 301

MannyST

Member
Local time
Today, 05:07
Joined
May 23, 2020
Messages
42
sheet1 table is the same as the one you posted
table1 is a lookup table.
there is a function in Module1, apPriceFacet.
the function is called in FinalQuery.
ARNELGP Thank you for the actual example also. I was able to decipher the access part, but not the module as my understanding is very limited but it is easier to see and figure out. So, I appreciate the time you take and the example did work in Access as I added more records. The only issue is the "configurable" record. I'm not sure what needs to happen first faceting the simple records as it works in your example and is good. It would then need to create the "configurable" field/record as in my example based of all the "simple" records and Sku is the first two parts of the children that match. This would be unique and what it would need to search for I guess. If a simple record is created then a configurable record would also need to be created. At the same time if there are 5 simple records created then it would need to create a configurable record as in example. Apparently, this is the format that Magento needs for cataloging. I hope i'm making sense......Thoughts?
 

MannyST

Member
Local time
Today, 05:07
Joined
May 23, 2020
Messages
42
Would a Select Case work?
I have no idea....this is way above my scope and I'm trying to understand it. I know what I need it to do and how, but that's it until I see it in action. I will read up on Select Case.........Thanks for the thought....
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:07
Joined
May 7, 2009
Messages
19,170
it's not clear to me. do you mean the "configurable" record is not in the original table and need to be created?
 

MannyST

Member
Local time
Today, 05:07
Joined
May 23, 2020
Messages
42
it's not clear to me. do you mean the "configurable" record is not in the original table and need to be created?
Exactly! It doesn't exist and is created by grouping the simple records. So if there is one simple record, one Configurable gets created off that one simple record. If there is 10 simple records and the 1st two data parts of the SKU then creates the configurable with all the different variations in price. Therefore we get the price facets that each of the simple records belong to. So if you called up or searched the configurable SKU Z1009 A431 it would have 5 different price_facets that it categorizes in, but 11 different simple records as shown in my example! Configurable does not exist and is created using the SKU field, but only the 1st two parts of the simple sku (in this example Z1009 A431) but it doesn't matter what the 1st two parts of the sku are as there are 10,000 simple records......but the 1st two parts ALWAYS CREATE the configurable SKU and then we need the price_facet grouping if that is the right term.........You have figured out the 1st part on the simple records with the vba and access query.......but the configurable does not exist and thus needs to be created and that is something i am trying to learn the concept behind.
Faceting is a term being used in Magento for cataloging and I can't figure out what the Access term would be.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:07
Joined
May 7, 2009
Messages
19,170
run Query2 first to create the "configurable" records.
then run FinalQuery to show price_facet.
 

Attachments

  • price_facet.zip
    45 KB · Views: 262

MannyST

Member
Local time
Today, 05:07
Joined
May 23, 2020
Messages
42
run Query2 first to create the "configurable" records.
then run FinalQuery to show price_facet.
That is perfect! So the only thing i'm not really understanding is the VBA....what do you recommend I read to get a better understanding of structure and functions, so I can better understand your VBA Module? I will try to add the queries to my database which will be easy. I'm not sure how the VBA code gets added to an existing DB, but I will do some reading.....This will be the first time I take and add/copy such a code to an existing DB. Kind of exciting. Hopefully it works like your example!
 

Mike Krailo

Well-known member
Local time
Today, 05:07
Joined
Mar 28, 2020
Messages
1,030
Open your existing DB and on the ribbon, select external data. Then navigate to 'from database' and browse to the one containing Arnels code. Select all the queries and modules to import and finish following dialog prompts.
 

Users who are viewing this thread

Top Bottom