|
 |
access thread: sql statement
Message #1 by "chris angus" <chris@a...> on Sun, 28 Oct 2001 01:02:53
|
|
the select statement below returns two columns, is it possible to show
another column (psupp) from the same table, ive tried subkey, psupp but
it doesn't work
Data1.RecordSource = "select subkey, min(a1)as minimum from prices group
by subkey"
this returns the minimum price there is for each subkey but i need to see
the supplier from the psupp column
Message #2 by "Ian Ashton" <ian@c...> on Sun, 28 Oct 2001 13:38:32 -0000
|
|
Chris,
My last reply, suggesting:
SELECT subkey, Min(a1) AS minimum
FROM prices
GROUP BY subkey
HAVING Min(a1)<>"0";
Of course will not do what (I now realise) you require.
To ignore the zero prices but still return the minimum for prices which do
exist, you would need the following:
SELECT subkey, Min(IIf([a1]>0,[a1])) AS minimum
FROM prices
GROUP BY subkey;
However, this still does not identify the supplier to which that minimum
price is attached.
I have not yet found a way to do this with a single SQL statement - if
anyone out there has cracked this, I should be very pleased to hear about
it.
It is possible with the following hack:
1) Create a saved query in your Access mdb file with the SQL as above.
2) If the name of this saved query is "qryMinPrice" then use
Data1.RecordSource = _
"SELECT prices.* FROM qryMinPrice INNER JOIN prices ON
(qryMinPrice.minimum = prices.a1) AND (qryMinPrice.subkey = prices.subkey);"
This will, of course, return more than one supplier for a given subkey if
they each have a price matching the minimum.
Ian Ashton
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Sunday, October 28, 2001 1:03 AM
To: Access
Subject: [access] sql statement
the select statement below returns two columns, is it possible to show
another column (psupp) from the same table, ive tried subkey, psupp but
it doesn't work
Data1.RecordSource = "select subkey, min(a1)as minimum from prices group
by subkey"
this returns the minimum price there is for each subkey but i need to see
the supplier from the psupp column
Message #3 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 29 Oct 2001 08:18:14 -0800
|
|
There's no dishonor in the 2-query solution, IMHO--I will admit to using it
all the time. I believe it is also possible to do in a single query with a
subquery, e.g.:
(warning--air-SQL!)
SELECT p.Subkey, p.Price, p.a1
FROM prices as p INNER JOIN
(SELECT Subkey, Min(Price) as MinPrice
FROM prices
WHERE price > 0
GROUP BY Subkey) as s
ON p.Subkey = s.Subkey AND
p.Price = s.MinPrice ;
but I would call that no easier to maintain than the 2-query solution (and I
could be wrong that this is valid Jet SQL).
Also, if I understand the problem correctly, I think you want to use a WHERE
clause to disregard rows where price=0 rather than a HAVING clause. HAVING
ditches rows *after* the grouping, WHERE ditches rows *before* the grouping.
I *think* the below will just return rows for groups who didn't have any
records w/price=0. But I may not have read the problem carefully enough...
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Sunday, October 28, 2001 5:39 AM
To: Access
Subject: [access] RE: sql statement
Chris,
My last reply, suggesting:
SELECT subkey, Min(a1) AS minimum
FROM prices
GROUP BY subkey
HAVING Min(a1)<>"0";
Of course will not do what (I now realise) you require.
To ignore the zero prices but still return the minimum for prices which do
exist, you would need the following:
SELECT subkey, Min(IIf([a1]>0,[a1])) AS minimum
FROM prices
GROUP BY subkey;
However, this still does not identify the supplier to which that minimum
price is attached.
I have not yet found a way to do this with a single SQL statement - if
anyone out there has cracked this, I should be very pleased to hear about
it.
It is possible with the following hack:
1) Create a saved query in your Access mdb file with the SQL as above.
2) If the name of this saved query is "qryMinPrice" then use
Data1.RecordSource = _
"SELECT prices.* FROM qryMinPrice INNER JOIN prices ON
(qryMinPrice.minimum = prices.a1) AND (qryMinPrice.subkey = prices.subkey);"
This will, of course, return more than one supplier for a given subkey if
they each have a price matching the minimum.
Ian Ashton
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Sunday, October 28, 2001 1:03 AM
To: Access
Subject: [access] sql statement
the select statement below returns two columns, is it possible to show
another column (psupp) from the same table, ive tried subkey, psupp but
it doesn't work
Data1.RecordSource = "select subkey, min(a1)as minimum from prices group
by subkey"
this returns the minimum price there is for each subkey but i need to see
the supplier from the psupp column
Message #4 by "Ian Ashton" <ian@c...> on Mon, 29 Oct 2001 20:09:03 -0000
|
|
Yes, Roy you are absolutely right in principle.
The actual (tested on Access 2000) statement for the fields given by Chris
is:
SELECT p.subkey, p.psupp, p.a1
FROM prices AS p INNER JOIN [SELECT Subkey, Min(a1) as MinPrice
FROM prices
WHERE a1 > 0
GROUP BY Subkey]. AS s ON (p.a1 = s.MinPrice) AND (p.subkey
s.Subkey);
(When I originally tried something like this with a sub query, in a moment
of aberration (hurry) I forgot the period after the right hand square
bracket ("GROUP BY Subkey].")
Ian Ashton
-----Original Message-----
From: Pardee, Roy E [mailto:roy.e.pardee@l...]
Sent: Monday, October 29, 2001 4:18 PM
To: Access
Subject: [access] RE: sql statement
There's no dishonor in the 2-query solution, IMHO--I will admit to using it
all the time. I believe it is also possible to do in a single query with a
subquery, e.g.:
(warning--air-SQL!)
SELECT p.Subkey, p.Price, p.a1
FROM prices as p INNER JOIN
(SELECT Subkey, Min(Price) as MinPrice
FROM prices
WHERE price > 0
GROUP BY Subkey) as s
ON p.Subkey = s.Subkey AND
p.Price = s.MinPrice ;
but I would call that no easier to maintain than the 2-query solution (and I
could be wrong that this is valid Jet SQL).
Also, if I understand the problem correctly, I think you want to use a WHERE
clause to disregard rows where price=0 rather than a HAVING clause. HAVING
ditches rows *after* the grouping, WHERE ditches rows *before* the grouping.
I *think* the below will just return rows for groups who didn't have any
records w/price=0. But I may not have read the problem carefully enough...
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Sunday, October 28, 2001 5:39 AM
To: Access
Subject: [access] RE: sql statement
Chris,
My last reply, suggesting:
SELECT subkey, Min(a1) AS minimum
FROM prices
GROUP BY subkey
HAVING Min(a1)<>"0";
Of course will not do what (I now realise) you require.
To ignore the zero prices but still return the minimum for prices which do
exist, you would need the following:
SELECT subkey, Min(IIf([a1]>0,[a1])) AS minimum
FROM prices
GROUP BY subkey;
However, this still does not identify the supplier to which that minimum
price is attached.
I have not yet found a way to do this with a single SQL statement - if
anyone out there has cracked this, I should be very pleased to hear about
it.
It is possible with the following hack:
1) Create a saved query in your Access mdb file with the SQL as above.
2) If the name of this saved query is "qryMinPrice" then use
Data1.RecordSource = _
"SELECT prices.* FROM qryMinPrice INNER JOIN prices ON
(qryMinPrice.minimum = prices.a1) AND (qryMinPrice.subkey = prices.subkey);"
This will, of course, return more than one supplier for a given subkey if
they each have a price matching the minimum.
Ian Ashton
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Sunday, October 28, 2001 1:03 AM
To: Access
Subject: [access] sql statement
the select statement below returns two columns, is it possible to show
another column (psupp) from the same table, ive tried subkey, psupp but
it doesn't work
Data1.RecordSource = "select subkey, min(a1)as minimum from prices group
by subkey"
this returns the minimum price there is for each subkey but i need to see
the supplier from the psupp column
Message #5 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 29 Oct 2001 13:34:56 -0800
|
|
Far out--I've never seen that square brackets/period syntax for subqueries
before. Thanks for taking the time to spell that out.
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Monday, October 29, 2001 12:09 PM
To: Access
Subject: [access] RE: sql statement
Yes, Roy you are absolutely right in principle.
The actual (tested on Access 2000) statement for the fields given by Chris
is:
SELECT p.subkey, p.psupp, p.a1
FROM prices AS p INNER JOIN [SELECT Subkey, Min(a1) as MinPrice
FROM prices
WHERE a1 > 0
GROUP BY Subkey]. AS s ON (p.a1 = s.MinPrice) AND (p.subkey
s.Subkey);
(When I originally tried something like this with a sub query, in a moment
of aberration (hurry) I forgot the period after the right hand square
bracket ("GROUP BY Subkey].")
Ian Ashton
-----Original Message-----
From: Pardee, Roy E [mailto:roy.e.pardee@l...]
Sent: Monday, October 29, 2001 4:18 PM
To: Access
Subject: [access] RE: sql statement
There's no dishonor in the 2-query solution, IMHO--I will admit to using it
all the time. I believe it is also possible to do in a single query with a
subquery, e.g.:
(warning--air-SQL!)
SELECT p.Subkey, p.Price, p.a1
FROM prices as p INNER JOIN
(SELECT Subkey, Min(Price) as MinPrice
FROM prices
WHERE price > 0
GROUP BY Subkey) as s
ON p.Subkey = s.Subkey AND
p.Price = s.MinPrice ;
but I would call that no easier to maintain than the 2-query solution (and I
could be wrong that this is valid Jet SQL).
Also, if I understand the problem correctly, I think you want to use a WHERE
clause to disregard rows where price=0 rather than a HAVING clause. HAVING
ditches rows *after* the grouping, WHERE ditches rows *before* the grouping.
I *think* the below will just return rows for groups who didn't have any
records w/price=0. But I may not have read the problem carefully enough...
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Sunday, October 28, 2001 5:39 AM
To: Access
Subject: [access] RE: sql statement
Chris,
My last reply, suggesting:
SELECT subkey, Min(a1) AS minimum
FROM prices
GROUP BY subkey
HAVING Min(a1)<>"0";
Of course will not do what (I now realise) you require.
To ignore the zero prices but still return the minimum for prices which do
exist, you would need the following:
SELECT subkey, Min(IIf([a1]>0,[a1])) AS minimum
FROM prices
GROUP BY subkey;
However, this still does not identify the supplier to which that minimum
price is attached.
I have not yet found a way to do this with a single SQL statement - if
anyone out there has cracked this, I should be very pleased to hear about
it.
It is possible with the following hack:
1) Create a saved query in your Access mdb file with the SQL as above.
2) If the name of this saved query is "qryMinPrice" then use
Data1.RecordSource = _
"SELECT prices.* FROM qryMinPrice INNER JOIN prices ON
(qryMinPrice.minimum = prices.a1) AND (qryMinPrice.subkey = prices.subkey);"
This will, of course, return more than one supplier for a given subkey if
they each have a price matching the minimum.
Ian Ashton
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Sunday, October 28, 2001 1:03 AM
To: Access
Subject: [access] sql statement
the select statement below returns two columns, is it possible to show
another column (psupp) from the same table, ive tried subkey, psupp but
it doesn't work
Data1.RecordSource = "select subkey, min(a1)as minimum from prices group
by subkey"
this returns the minimum price there is for each subkey but i need to see
the supplier from the psupp column
|
|
|
View Cart
