ACCESS Expression convert to SQL
-
Need help on writing an expression created in ACCESS in SQL. I am new to SQL so bare with the long written queries. I think I need everything spelled out so I can get a good grasp on the basics.
I am at a lost with trying to figure out how to even begin re-writing a DLookup in SQL. I managed to create a query that will look up a specific value in another table and return a specified value. But having a table with a from to range and returning a value has me
Here is the expression that worked in ACCESS
Expr1: DLookUp("description","table1",[price] & "<=[max] and " & [price] & " >= [min]")
Here is the query that I am trying to figure out in SQL
UPDATE Mohawk_Production.dbo.[Mohawk_Prod]
SET Mohawk_Production.dbo.[Mohawk_prod].price_facet = MAPPING.dbo.[mapping_price_facet].??????
FROM Mohawk_Production.dbo.[Mohawk_Prod]
INNER JOIN MAPPING.dbo.[MAPPING_price_FACET]
ON Mohawk_Production.dbo.[Mohawk_prod].price = MAPPING.dbo.[mapping_price_facet].???????
Can someone give me an idea of which direction to head in.....
table used in access was
-
With your UPDATE you try to update your records in the table Mohawk_Prod. However you are asking to give create a query which returns the information the same as Access. I think your query should be like this:
SELECT *
FROM Mohawk_Production.dbo.[Mohawk_Prod] AS p
INNER JOIN MAPPING.dbo.[MAPPING_price_FACET] AS f ON p.price <= f.[Max] AND p.price <= f.[Min];Instead of * you can define which columns you want to return.
-
Hi Thanks for the response. So after searching and searching, I think what I need is to create a CASE SELECT Statement?
Just to be clear, I am not using ACCESS for anything anymore. Everything is in SQL including the Mapping_price_facet table which is exactly like the ACCESS table you see with price and price ranges.
The task is to take the price column in Mohawk prod table which is in Mohawk_Production DB. I need to take the value/price and go search in the Mapping_price_facet table which is in the MAPPING DB. Let's say I have price 89.00, I need it to return Under $100 and put it in the price_facet field in the Mohawk Prod table.
I can't try your query right now to see what it does or doesn't do. I'm a novice and while I can somewhat decipher it, you highspeed guys are always putting syntax that blows my mind cause haven't learned it yet.....lol
-
You're welcome.
I think my query does what you want. Let me know if you have checked and don hesitate if you have any questions about the query or syntax.
-
So I can kind of make out how to read your query and what the syntax
SELECT TOP (10) price, price_facet
FROM Mohawk_Production.dbo.[Mohawk_Prod] AS p
INNER JOIN MAPPING.dbo.[MAPPING_price_FACET] AS f ON p.price <= f.[Max] AND p.price <= f.[Min];
below is the result, which is strange.
90.85 NULL
90.85 NULL
90.85 NULL
90.85 NULL
90.85 NULL
173.25 NULL
173.25 NULL
173.25 NULL
173.25 NULL
346.5 NULL
This is what is in the Mohawk_Prod table. So the query isn't doing what I need it to do. It should be going into the Mapping_price_facet table, using the price field and searching where it fits in the > than, but < than, it should then return the value in the "Description" Under $100, $100-250, $250-$500, etc and put this value in the price_facet field of the Mohawk_Prod table. For some reason it is doing something to the prices and returning the wrong price that isn't even in the field? Do you think a CASE Statement is what I need....I just am not that high speed on this yet AS f ON p.price <= f.[Max] AND p.price <= f.[Min];
price price_facet
90.85 NULL
173.25 NULL
346.5 NULL
90.85 NULL
247.5 NULL
471.9 NULL
247.5 NULL
165 NULL
330 NULL
580.8 NULL
-
update....so I managed to create a CASE statement that worked.
Which accomplishes one of many other tasks I need to accomplish. I'm realizing a little more and more as I ask questions and read the responses I get. I must say if you high speed experts can tone it down a tad with newbies so they can build a good foundation with the basics and then get all complex that would be easier to understand for beginners such as myself.
I'm hoping you can add some more knowledge with what I soon realized with SQL tonight after managing to create the CASE statement that provided the result I was looking for. This is perhaps a lack of knowledge on my end, but obviously a query is a query. Unlike ACCESS which I am more familiar with, I realized that while I managed to create my query, it didn't change or add the result to the colunmn that I am trying to populate.
So, I tried adding Update, etc and didn't accomplish much as I am lacking the knowledge to understand SQL and it's obvious I am not thinking in SQL language yet.
What overview can you provide to learn a tad more about how you would run a query and get the results of a particular query value/end result to get appended to the field you want to populate.
The big picture with my project is, I don't want to create several databases that are a 1 to 1 or 1 to many relationship. I have a master table that has all the column and field names for the end result, but in order to get to that end result I have to run queries and what not to add values to fields. I know this is out of the ordinary, but I have a purpose.
So how would I get the result from my CASE statement to populate into a certain field "price_facet", do I change the language of the Case statement or add another query in order to append the desired result or update the value in a particular field?
thanks for any input
-
I now see I made a typo.
INNER JOIN MAPPING.dbo.[MAPPING_price_FACET] AS f ON p.price <= f.[Max] AND p.price <= f.[Min];
This part says: Give me the mapping there the price is lower than max and lower than min. That never is the case. Can you try it with this part?
INNER JOIN MAPPING.dbo.[MAPPING_price_FACET] AS f ON p.price <= f.[Max] AND p.price >= f.[Min];
(changed <= to >= for the minus case)
If you just want to include yout FACET, i don't think a case is needed. Can you give me the query with the case you made?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply