compare price falling within the range

[hoachen]

How can do the comparaion on the price that on the same table but with same
item no? The price will be different from 5cents to 5 dollars. i would it be
able to consider a match even though the price are different from 5 cents to
5 dollars.

The property can be jointed in the query is the "item no"

Example:

Item no: 345 with the price of 3.50
Item no: 158 with the price of 5.00
Item no: 345 with the price of 7.50 (4 different from 3.5 and fall within
5cents to 5 dollars)
Item no: 345 woth the price of 4.75 (1.25 different from 3.5 and fall within
5cents to 5 dollars))

The result should display:

Item no: 345 with the price of 3.5 (the min amt)
Item no: 158 with the price 5.00

 

[KARL DEWEY]

I think this is what you are looking for --
SELECT [Item no], (SELECT TOP 1 [XX].[Price] FROM YourTable AS [XX] WHERE
([XX].[Price] Between .05 AND 5.0) AND [YourTable].[Item no] = [XX].[Item no]
ORDER BY [XX].[Price]) AS Min_Price
FROM YourTable
ORDER BY [Item no];

 

[hoachen]

Thanks again for looking into my question. I tried your solution but it does
not work and i have future question bout the solution. Here is it

It has an error after i run it "At most one record can be returned by this
subquery". Then i click "OK" it display on ItemNo field "#Name?" and
Min_Price" #Name?"

Why it display this ?

I think this is what you are looking for --
SELECT [Item no], (SELECT TOP 1 [XX].[Price] FROM YourTable AS [XX] WHERE
([XX].[Price] Between .05 AND 5.0) AND [YourTable].[Item no] = [XX].[Item no]
ORDER BY [XX].[Price]) AS Min_Price
FROM YourTable
ORDER BY [Item no];

--
Build a little, test a little.

How can do the comparaion on the price that on the same table but with same
item no? The price will be different from 5cents to 5 dollars. i would it be
able to consider a match even though the price are different from 5 cents to
5 dollars.

The property can be jointed in the query is the "item no"

Example:

Item no: 345 with the price of 3.50
Item no: 158 with the price of 5.00
Item no: 345 with the price of 7.50 (4 different from 3.5 and fall within
5cents to 5 dollars)
Item no: 345 woth the price of 4.75 (1.25 different from 3.5 and fall within
5cents to 5 dollars))

The result should display:

Item no: 345 with the price of 3.5 (the min amt)
Item no: 158 with the price 5.00

 

[KARL DEWEY]

Post back the SQL you used.

--
Build a little, test a little.

Thanks again for looking into my question. I tried your solution but it does
not work and i have future question bout the solution. Here is it

It has an error after i run it "At most one record can be returned by this
subquery". Then i click "OK" it display on ItemNo field "#Name?" and
Min_Price" #Name?"

Why it display this ?

I think this is what you are looking for --
SELECT [Item no], (SELECT TOP 1 [XX].[Price] FROM YourTable AS [XX] WHERE
([XX].[Price] Between .05 AND 5.0) AND [YourTable].[Item no] = [XX].[Item no]
ORDER BY [XX].[Price]) AS Min_Price
FROM YourTable
ORDER BY [Item no];

--
Build a little, test a little.

How can do the comparaion on the price that on the same table but with same
item no? The price will be different from 5cents to 5 dollars. i would it be
able to consider a match even though the price are different from 5 cents to
5 dollars.

The property can be jointed in the query is the "item no"

Example:

Item no: 345 with the price of 3.50
Item no: 158 with the price of 5.00
Item no: 345 with the price of 7.50 (4 different from 3.5 and fall within
5cents to 5 dollars)
Item no: 345 woth the price of 4.75 (1.25 different from 3.5 and fall within
5cents to 5 dollars))

The result should display:

Item no: 345 with the price of 3.5 (the min amt)
Item no: 158 with the price 5.00

 

[hoachen]

here is the query:

SELECT [Item No], (SELECT TOP 1 temp.price FROM table1 AS temp WHERE
(temp.price Between .05 AND 5.0) AND table1.[Item No] = temp.[Item No]
ORDER BY temp.price) AS Min_Price
FROM table1
ORDER BY [Item No];

Post back the SQL you used.

--
Build a little, test a little.

Thanks again for looking into my question. I tried your solution but it does
not work and i have future question bout the solution. Here is it

It has an error after i run it "At most one record can be returned by this
subquery". Then i click "OK" it display on ItemNo field "#Name?" and
Min_Price" #Name?"

Why it display this ?

I think this is what you are looking for --
SELECT [Item no], (SELECT TOP 1 [XX].[Price] FROM YourTable AS [XX] WHERE
([XX].[Price] Between .05 AND 5.0) AND [YourTable].[Item no] = [XX].[Item no]
ORDER BY [XX].[Price]) AS Min_Price
FROM YourTable
ORDER BY [Item no];

--
Build a little, test a little.


:

How can do the comparaion on the price that on the same table but with same
item no? The price will be different from 5cents to 5 dollars. i would it be
able to consider a match even though the price are different from 5 cents to
5 dollars.

The property can be jointed in the query is the "item no"

Example:

Item no: 345 with the price of 3.50
Item no: 158 with the price of 5.00
Item no: 345 with the price of 7.50 (4 different from 3.5 and fall within
5cents to 5 dollars)
Item no: 345 woth the price of 4.75 (1.25 different from 3.5 and fall within
5cents to 5 dollars))

The result should display:

Item no: 345 with the price of 3.5 (the min amt)
Item no: 158 with the price 5.00

 

[KARL DEWEY]

If the field names are correct for your data try this --
SELECT [Item No], (SELECT TOP 1 temp.price FROM table1 AS temp WHERE
(temp.price Between .05 AND 5.0) AND table1.[Item No] = temp.[Item No] ORDER
BY temp.price) AS Min_Price
FROM table1
WHERE table1.price Is Not Null
ORDER BY [Item No];

--
Build a little, test a little.

here is the query:

SELECT [Item No], (SELECT TOP 1 temp.price FROM table1 AS temp WHERE
(temp.price Between .05 AND 5.0) AND table1.[Item No] = temp.[Item No]
ORDER BY temp.price) AS Min_Price
FROM table1
ORDER BY [Item No];

Post back the SQL you used.

--
Build a little, test a little.

Thanks again for looking into my question. I tried your solution but it does
not work and i have future question bout the solution. Here is it

It has an error after i run it "At most one record can be returned by this
subquery". Then i click "OK" it display on ItemNo field "#Name?" and
Min_Price" #Name?"

Why it display this ?

:

I think this is what you are looking for --
SELECT [Item no], (SELECT TOP 1 [XX].[Price] FROM YourTable AS [XX] WHERE
([XX].[Price] Between .05 AND 5.0) AND [YourTable].[Item no] = [XX].[Item no]
ORDER BY [XX].[Price]) AS Min_Price
FROM YourTable
ORDER BY [Item no];

--
Build a little, test a little.


:

How can do the comparaion on the price that on the same table but with same
item no? The price will be different from 5cents to 5 dollars. i would it be
able to consider a match even though the price are different from 5 cents to
5 dollars.

The property can be jointed in the query is the "item no"

Example:

Item no: 345 with the price of 3.50
Item no: 158 with the price of 5.00
Item no: 345 with the price of 7.50 (4 different from 3.5 and fall within
5cents to 5 dollars)
Item no: 345 woth the price of 4.75 (1.25 different from 3.5 and fall within
5cents to 5 dollars))

The result should display:

Item no: 345 with the price of 3.5 (the min amt)
Item no: 158 with the price 5.00

 

[hoachen]

Still have the same problem, no result which after i click "OK" it display on
ItemNo field and get "#Name?" and "Min_Price" and get #Name?"

If the field names are correct for your data try this --
SELECT [Item No], (SELECT TOP 1 temp.price FROM table1 AS temp WHERE
(temp.price Between .05 AND 5.0) AND table1.[Item No] = temp.[Item No] ORDER
BY temp.price) AS Min_Price
FROM table1
WHERE table1.price Is Not Null
ORDER BY [Item No];

--
Build a little, test a little.

here is the query:

SELECT [Item No], (SELECT TOP 1 temp.price FROM table1 AS temp WHERE
(temp.price Between .05 AND 5.0) AND table1.[Item No] = temp.[Item No]
ORDER BY temp.price) AS Min_Price
FROM table1
ORDER BY [Item No];

Post back the SQL you used.

--
Build a little, test a little.


:

Thanks again for looking into my question. I tried your solution but it does
not work and i have future question bout the solution. Here is it

It has an error after i run it "At most one record can be returned by this
subquery". Then i click "OK" it display on ItemNo field "#Name?" and
Min_Price" #Name?"

Why it display this ?

:

I think this is what you are looking for --
SELECT [Item no], (SELECT TOP 1 [XX].[Price] FROM YourTable AS [XX] WHERE
([XX].[Price] Between .05 AND 5.0) AND [YourTable].[Item no] = [XX].[Item no]
ORDER BY [XX].[Price]) AS Min_Price
FROM YourTable
ORDER BY [Item no];

--
Build a little, test a little.


:

How can do the comparaion on the price that on the same table but with same
item no? The price will be different from 5cents to 5 dollars. i would it be
able to consider a match even though the price are different from 5 cents to
5 dollars.

The property can be jointed in the query is the "item no"

Example:

Item no: 345 with the price of 3.50
Item no: 158 with the price of 5.00
Item no: 345 with the price of 7.50 (4 different from 3.5 and fall within
5cents to 5 dollars)
Item no: 345 woth the price of 4.75 (1.25 different from 3.5 and fall within
5cents to 5 dollars))

The result should display:

Item no: 345 with the price of 3.5 (the min amt)
Item no: 158 with the price 5.00