October 1, 2020 at 2:03 pm
I tried looking up a comparable function in SQL to the DLOOKUP in ACCESS. I had this working in ACCESS, but since I now am learning SQL i'm looking for some help to how to write this is SQL.
Overview: Query will be used on a Table called "A" to lookup field name 'Price' get value and then goto Table 'B' to determine price range/description/value to return to Table 'A' field 'Price facet'
The Primary key is "SKU"
I had a table in ACCESS and would like to stick with this table in SQL
Table B
ID Min Max Description
1 -999 99.99 Under $100
2 100 249.99 $100 - $250
3 250 499.99 $250 - $500
4 500 749.99 $500 - $750
5 750 999.99 $750 - $1000
6 1000 999999999 $1000 & Above
the following worked using VBA and another query
DLookUp("description","MAPPING_PRICE_FACET",[PRICE p] & "<=[max] and " & [PRICE p] & " >= [min]")
Where to start and please provide insight so I can keep growing at SQL
Thanks
October 1, 2020 at 6:59 pm
I assume that you mean you want the description column to be based on the min and max columns? Or something else?
Where is the dlookup value being used? Everything in SQL Server needs a place to live. You haven't really defined that.
October 6, 2020 at 12:29 pm
Hi, so here are tables and a brief explanation of the end goal without naming tables in sql as I had this working in ACCESS and now hit a wall.....I have no prior SQL knowledge and trying to learn over here.
First the table below creates the price_facet field using the price field of each record. Using the price it looks up the price and returns the Description in the price_facet field.
MAPPING_PRICE_FACET
ID Min Max Description
1? -999? 99.99? Under $100
2? 100? 249.99? $100 - $250
3? 250? 499.99? $250 - $500
4? 500? 749.99? $500 - $750
5? 750 999.99? $750 - $1000
6? 1000? 999999999? $1000 & Above
The configurable product type does not exist at first and is created after the simple product type (price_facet) is created.
Using the table above and a lookup function??? it would return the correct price_facet. that would be step one. Step two is to then create the configurable Product type using the SKU. SKU is primary key. Using grouping???? the first two parts of the SKU creates the configurable SKU and grouping all the various price_facets will give you the Price_facet configurable value.
SKU PRICE PRICE_FACET PRODUCT TYPE
Z1009 A431 64.45 Under $100|$100 - $250|$500 - $750|$250 - $500|$1000 & Above configurable
Z1009 A431 024036 EC 64.45 Under $100 simple
Z1009 A431 024060 EC 85.8 Under $100 simple
Z1009 A431 024072 EC 99 Under $100 simple
Z1009 A431 024096 EC 130.35 $100 - $250 simple
Z1009 A431 030120 EC 214.5 $100 - $250 simple
Z1009 A431 036060 EC 173.25 $100 - $250 simple
Z1009 A431 048072 EC 207.9 $100 - $250 simple
Z1009 A431 060096 EC 334.95 $250 - $500 simple
Z1009 A431 072108 EC 460.35 $250 - $500 simple
Z1009 A431 096120 EC 660 $500 - $750 simple
Z1009 A431 120168 EC 1146.75 $1000 & Above simple
So the configurable record doesn't exist in the table above in the beginning. It is created after the price_facet is created using the price field. A new record with product type configurable needs to be created and added to the table to distinguish it as the "Parent" record and the simple are all the "children" that belong to that parent. The first two parts of the SKU identifies which simple records are to be grouped.
I need help to write the function in a basic SQL language so a beginner such as myself can look at it and decipher the steps/key words and steps to figure out this task.
October 6, 2020 at 1:34 pm
Ok, but I think there's a fundamental mismatch between the way one would normally design a table structure, and what you appear to be doing here, although I can certainly leave room for the very distinct possibility that I'm just not understanding your data structure or your overall goal that the existing Access database accomplishes. I'll take a minute here and try to cover a quick explanation of how tables are supposed to work... A table should be a set of columns that all relate to a single instance of a real-world object. Whenever that real-world object has a 1 to many relationship to some other property or characteristic or even other object, that is typically done by relating the two tables using a FOREIGN KEY. MS Access supports this capability. In the case of your data, you appeared at first to just be seeking to categorize the price value, so to translate that into my concepts above, the price has a relationship to what you refer to as a "facet". As a price range implies a 1 to 1 relationship between the price and the range, a lookup table makes sense. In a query, such lookups can be done using a JOIN to the lookup table, and the JOIN CONDITION specifies that the price value from the table with the price in it is BETWEEN the min and max price values in the lookup table.
Then you posted again, and now it appears you need some kind of hierarchy, and that's where my wheels fell off the bus. I don't understand what the price range lookup function has to do with the idea of a product being either "configurable" or "simple". I didn't see anything that describes to me what those terms mean in the real world, nor anything that describes why the price facet values need to be concatenated. The only reason I can think of is to try and create a report header that is going to end up in a text file. That's not really necessary anymore given the existence of tools like SSRS (SQL Server Reporting Services).
So... to make a long story short... what, exactly, in real-world terms, does this table of products and prices, along with your facet lookup table, accomplish? Please describe the meaning of configurable versus simple, and why that has any bearing on the facet lookup. Please also include the overall objective of having this data... and be as thorough as you can. Thanks!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 6, 2020 at 2:39 pm
🙂 Thanks for the chuckle....let me try and help you put your wheels back on!
I get the first part of your response. Using a Join rather than a lookup will accomplish my goal. This is where my wheels fall off with the Statements as I don't have that foundation and ability like I did in Access.
So here is the real world scenario. I get a file. In that file are thousands of products. The task is to wash, format, manipulate, query or whatever term applies to the many tasks/steps that are needed to end up with a csv file that can be imported into an Ecommerce Software program. First you must setup your ecommerce catalog, attributes, etc which will create the types of items that will appear on the front end of your platform. The items in the ecommerce platform are described as simple or configurable products. A simple product is unique as there is only 1 of that product. Configurable products are a culmination of the simple products using the SKU which is the primary key for the simple products. So as in the previous tables if you look at the SKU you can see the relation is similar, but only for the first two parts which is the Style # & Color # of the item/simple product. The configurable product must be created in order for the ecommerce platform to show the details/picture of the Parent(configurable) item only across the board. If you click on the parent/configurable item you will then see the children/simple products that are the same Style # & Color #, the only thing that will vary is the size which is the other part of the SKU. So this SKU Z1009 A431 024036 EC goes as follows Z1009 is the Style #, A431 is the Color #, 024036 is the size of that item.
In order to continue with my real world task and complete what needs to be completed in my Production Table using only certain columns I imported from the original table is what I described before. There are other tasks that need to be accomplished to format/wash the data by using one of the fields in my production table and create what I term as facets which is what the ecommerce software needs to properly run the front end of all this.
The price facet field was step 1. It uses the price field of the item or simple record, because they are all simple records in the end and then returns a price facet $100-$250. The price facet is used on the front end and will query/filter all items if someone was to select items that are $100-$250. The configurable/parent record does not exist at all and it is created once you are done formatting many other fields in the Production table. Configurable records are necessary and need to be create so a configurable item is pulled up and can be viewed. If person clicks on configurable record, they will see the simple records associated with it.
In order to create the configurable you use the simple records and Group??? (I don't know the term in SQL) them by Style # & Color #. So price_facet is just one of the fields that needs to be created in the Production Table I am creating in SQL.
Step 2 of the process is then to add a new record to the production table which is a configurable. The configurable item has all the same fields as the simple items and is required for the Ecommerce platform and the Filtering/query options it has. The difference is it is a sum of the simple records that are related by the SKU Style # & Color #.
I don't know if this gives you more insight into the real world task I have at hand. The process again.....get a file.....append/import the fields I need into a Production File in SQL......create the additional fields that are required such as the price_facet and also a configurable item using the simple records......once completed....push out all data into a master table which is no ready to be exported into ecommerce software...….this is how I see things right now.....
I hope you didn't just go off the cliff and were able to steer your bus to a halt! lmao......
October 7, 2020 at 1:27 pm
Okay, that explains a great deal. However...
If you are going to have a product represented by a SKU, and that product is simple if it's unique, meaning that there are no size or color attributes, that's a way of saying that a given product can have a "TYPE" of either "S" for simple, or "C" for configurable. As represented in a database, that's not even necessary. You only need to have a size attribute and a color attribute on the main product table. These columns would be NULLable, and then the primary key for that table would be the combination of the SKU, Size, and Color columns. A price facet should remain in a lookup table that you join to, or alternatively, be a computed column in the main product table. With this structure, you can't actually have the Primary Key on the table use a NULLable column, so you create the unique clustered index on those 3 columns and supply an IDENTITY column as the actual PK. See the following example:
CREATE TABLE dbo.Product (
ProductID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
SKU varchar(20) NOT NULL,
Size varchar(5) NULL,
Color varchar(15) NULL,
ProductName varchar(30) NOT NULL,
Price decimal(15,6) NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX UCDX_Product_SKU_Size_Color ON dbo.Product
(
SKU ASC,
Size ASC,
Color ASC
);
GO
As to creating your import file, now that I understand the concepts fully, let's talk about the actual requirements of the import process for the software involved. It seems likely that the database design behind that software may well be "less than ideal". That kind of problem is far too common, and often not examined in much detail by smaller companies looking for a "quick fix" that doesn't require a lot of IT involvement to get it up and running, and ignoring the longer term pain such a system can bring. Your next task is to explain the underlying tables in the system you have to provide the import file, and then explain the structure of the import file from a database point of view.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply