ACCESS to SQL (1 Viewer)

MannyST

Member
Local time
Today, 06:15
Joined
May 23, 2020
Messages
42
Hello everyone...hope all is well....so I recently went from ACCESS to SQL in trying to accomplish my task. Problem is I never used SQL before and it's going very slow....I had help writing a query/script that solved my previous learning curve, but no need to figure it out in SQL and language/terms are a tad different. I still learning a bit everyday, but have a long road ahead of me.

I will do my best to explain steps and end result desired or you can view previous thread here...https://www.access-programmers.co.uk/forums/threads/facet-query-in-access.312597/

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

IDMinMaxDescription
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.
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

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 hope someone gets the concept. I hope someone can 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.

Thanks
 

plog

Banishment Pending
Local time
Today, 05:15
Joined
May 11, 2011
Messages
11,611
You need to clearly and better state what it is you need. I read your post, glimpsed the one you linked to and cannot discern your issue. I searched your post for question marks and found 7---3 at the end of one statement and 4 at the end of another. So there's no question to answer. I have no idea what it is you need help with.

Your first paragraph makes it seem it has to do with getting the Description value alonside your other data. But then you make a hard left and bring in this PRODUCT_TYPE field like we know what you are talking about.

It seems like you have 2 issues. If that's the case I suggest you put aside the 2nd one and focus on the first. I suggest you start again. In your next post make your first line be your ultimate question--don't just jump into explaining things--ask the ultimate question first. Then go into an explanation. Sample data would help as well.
 

MannyST

Member
Local time
Today, 06:15
Joined
May 23, 2020
Messages
42
You need to clearly and better state what it is you need. I read your post, glimpsed the one you linked to and cannot discern your issue. I searched your post for question marks and found 7---3 at the end of one statement and 4 at the end of another. So there's no question to answer. I have no idea what it is you need help with.

Your first paragraph makes it seem it has to do with getting the Description value alonside your other data. But then you make a hard left and bring in this PRODUCT_TYPE field like we know what you are talking about.

It seems like you have 2 issues. If that's the case I suggest you put aside the 2nd one and focus on the first. I suggest you start again. In your next post make your first line be your ultimate question--don't just jump into explaining things--ask the ultimate question first. Then go into an explanation. Sample data would help as well.
Ok....let me ask first. Did you catch the part that I am attempting to re-create what I created in ACCESS in SQL now?

Trying to discern where you are lost at.
 

Minty

AWF VIP
Local time
Today, 10:15
Joined
Jul 26, 2013
Messages
10,353
I think you are trying to create a concatenated (Allen Browne Function) type output, but I don't understand the purpose of storing it.

However, you can achieve this easily in SQL Server depending on what version you are running?
 

plog

Banishment Pending
Local time
Today, 05:15
Joined
May 11, 2011
Messages
11,611
Lost at pretty much the the beginning:

Understand you have 2 tables.
Understand you want to JOIN them in a query
Think you want to calculate PRODUCT_TYPE in that query
Don't understand how its to be calculated or how MAPPING_PRICE_FACET figures into it
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:15
Joined
Feb 19, 2002
Messages
42,970
If your FE is still Access, you should be using tables linked to SQL Server and you can continue to use Access' variant of SQL so I'm not sure what your goal is. Why are you trying to change all the queries? Converting from using Jet/ACE to SQL Server isn't about changing your SQL although some might need to be changed. The conversion is about changing how your forms and reports work so that they use good client/server techniques rather than the old style Access techniques.

I build all my apps with ACE tables but good client/server techniques. When I'm ready to move to user testing, I convert the tables to linked SQL Server (or whatever RDBMS the client prefers). This rarely takes more than a couple of hours and that is only because I have to make sure to test all my DAO code. Occasionally, I need to create a view to make a query more efficient and a couple of times for very complex reports, i've created stored procedures but mostly, I just use the queries that worked when the BE was ACE. Access attempts to pass-through all queries. You can defeat this if you don't understand the process. For example, you have to avoid UDF's and VBA functions that do not have SQL Server equivalents in your base queries. The point is to avoid using functions that SQL Server cannot process and which will force Access to request entire tables from the server rather than just the rows and columns you actually need.
 

Users who are viewing this thread

Top Bottom