Re: Narrowing query results

Tech-Archive recommends: Fix windows errors by optimizing your registry

John, if I email you my existing SQL, would it be easier? I'm having a
little bit of trouble figuring out how and where to insert your Min()
function. It makes sense when I look at it here, but when I look at my
query, I have no idea.

Thanks :)

"John Spencer" wrote:

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?

.

Relevant Pages

  • Re: Narrowing query results
    ... the way that John wrote the SQL, it looks like it will match on Product, Company, and Color ... Video Tutorials on YouTube! ... controlname is the Name property of the control ... John, when you use the "Y" attached to each field, what does it represent? ...
    (microsoft.public.access.queries)
  • Re: Select query populating list box
    ... John tried your code. ... Additional assumption is that txtInstitution is never null. ... Every time you add one more criteria to the where clause, ... However if you look at my original code the text control needs to search ...
    (microsoft.public.access.queries)
  • Re: Isolate Patterns
    ... Do you want someone to write the SQL for the query? ... Did you copy John Nurick's code and put it into module? ... I would probably use an additional column and apply criteria to it on the theory that SQL engine is going to weed things down for me before I do the rgxExtract. ...
    (microsoft.public.access.queries)
  • RE: Help Needed
    ... Yes this is an Access form, and this form is being used to gather criteria to ... be used in the SQL Query. ... By removing the 'Control Source' property, I can no longer change the ... it sounds like this is kind of a query by form setup. ...
    (microsoft.public.access.queries)
  • Re: Date/Time Selection problem!!!!!
    ... Thank you so much John for your help! ... In query design view you would need to repeat the date criteria on the ... The SQL should look something like this. ... it does not pull data that are within these boundaries.. ...
    (microsoft.public.access.queries)