Re: Narrowing query results
- From: John Spencer <spencer@xxxxxxxxxx>
- Date: Tue, 03 Feb 2009 13:10:30 -0500
As I recall the query did not differentiate on the basis of the product. It should return ALL items that match criteria unless you enter criteria (a where clause) on the outer query.
For instance adding - WHERE Y.Product = "Sweater" should return all products that are Sweater for each company and color.
SELECT Y.Product, Y.Company, Y.Color, Y.Score, Y.Percent
FROM [YourTable] as Y Inner JOIN
(SELECT Product
, Company
, Color
, Min(Abs(Score-75)) as HowClose
GROUP BY Product, Company, Color
) as qClose
ON Y.Product = qClose.Product
AND Y.Company = qClose.Company
AND Y.Color = qClose.Color
AND Abs(Y.Score-75) = qClose.HowClose
FROM [YourTable]
WHERE Y.Product = "Sweater"
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
RandieRae wrote:
Hey Crystal,.
I've been doing some reading on the MIN function, and I think I get how to do it. But is this just going to return one matching product? Or will it still filter all criteria, and only filter the Score field to closest value? In my previous example, I had given some different colored sweaters. I want to see all sweaters that come close to the score entered in the form, as well as all other products that match the other criteria, for example scarves, gloves, and t-shirts, as long as the score criteria matches. Does this make sense?
"strive4peace" wrote:
Hi Randie,
you're welcome.
Y is an "alias" (a short 'nickname') for the table (make up whatever you want) so you can type less in the the SQL statement <smile>
I have a chapter in Access Basics on SQL ...
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
~~
> And how do I reference the value for
> Score when it is dependant on the value put into the form?
to reference a form control:
forms!formname!controlname
WHERE
formname is the Name of your form in the database window (main form)
controlname is the Name property of the control (you can change this to be logical if it is something ambiguous like Test123)
if the control is on a subform:
forms!formname!subform_controlname.form.controlname
WHERE
subform_controlname is the Name property of the subform control
Warm Regards,
Crystal
remote programming and training
Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
*
(: have an awesome day :)
*
RandieRae wrote:Thank you both for your help. I've never written an SQL query before, so am a little foggy on some of the variables. John, when you use the "Y" attached to each field, what does it represent? And how do I reference the value for Score when it is dependant on the value put into the form? My query is structured Forms!MyForm!Score, and it works, so how does this translate into SQL?
I've really just learned Access in the last 2 months, and find it absolutely fascinating, and full of possibilities. But I do need a little help sometimes. So thank you for everything.
Randie
"strive4peace" wrote:
thanks for jumping in, John!
Close is a reserved word, so a different name should be chosen for the subquery ... maybe qClose
Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html
SELECT Y.Product, Y.Company, Y.Color, Y.Score, Y.Percent
FROM [YourTable] as Y Inner JOIN
(SELECT Product
, Company
, Color
, Min(Abs(Score-75)) as HowClose
GROUP BY Product, Company, Color
) as qClose
ON Y.Product = qClose.Product
AND Y.Company = qClose.Company
AND Y.Color = qClose.Color
AND Abs(Y.Score-75) = qClose.HowClose
FROM [YourTable]
Warm Regards,
Crystal
remote programming and training
Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
(: have an awesome day :)
*
John Spencer (MVP) wrote:I hate when I do that. I left out part of the SQL statement
SELECT Y.Product, Y.Company, Y.Color, Y.Score, Y.Percent
FROM [YourTable] as Y Inner JOIN
(SELECT Product
, Company
, Color
, Min(Abs(Score-75)) as HowClose
GROUP BY Product, Company, Color
) as Close
ON Y.Product = Close.Product
AND Y.Company = Close.Company
AND Y.Color = Close.Color
AND Abs(Y.Score-75) = Close.HowClose
FROM YourTable
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John Spencer (MVP) wrote:You could try a query that looked like:
RandieRae wrote:I have a query that finds product information based on criteria put into a form. Quite often, there are multiple results for one product, different only in 1 field. I'm trying to narrow the results to only the nearest value to that put into the field on the form. Will it be easier to run the query once, and then another query of the resulting recordset? I'm kind of a VBA beginner, but would welcome any help I can get. Any ideas?
- Follow-Ups:
- Re: Narrowing query results
- From: RandieRae
- Re: Narrowing query results
- References:
- Re: Narrowing query results
- From: RandieRae
- Re: Narrowing query results
- Prev by Date: Re: Ranking records based on month
- Next by Date: Re: Calculating an average across multiple fields
- Previous by thread: Re: Narrowing query results
- Next by thread: Re: Narrowing query results
- Index(es):
Relevant Pages
|
