Narrowing query results

[RandieRae]

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?

 

[strive4peace]

Hi Randie,

can you provide an example of what you are trying to achieve? thank you


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
*

 

[RandieRae]

Hey Crystal,

Let's say I have one product, with identical information except for 1 field
(the rating). I have a form for searching product information, and one of
the search criteria is the score. I'm looking for anything <=the score put
on the form. This is giving me all products that fall into this range. I
want to take identical products that only differ in score and reduce the
results to only the score closest to what was input on the form. Does that
make sense? There are 6 other fields of search criteria, the query will run
whether all 7 fields are filled in, or even if none of them are. But I need
to limit my search results, otherwise my reports get pretty long and hard to
read.

Thanks a lot for your help,
Randie

 

[strive4peace]

Hi Randie,

sorry I was not clear ... I was hoping you would make up some data
scenarios and demonstrate what it is you are trying to accomplish...
thank you

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
*

 

[RandieRae]

Okay, lets say Field1 is Product, Field 2 is Company, Field3 is Color, Field4
is Score, Field5 is Percent. So:

Product Company Color Score Percent

Sweater Smiths Red 28 85
Sweater Smiths Blue 60 92
Sweater Smiths Blue 56 90
Sweater Smiths Green 82 95


In the form, I want to search for products with a score <=75. The query
will return the red and blue, but not the green. In my product table, I have
over 3000 products. There are 2 blue sweaters, just with different scores.
I want to just show the product that is nearest the value entered on the form
(75). So I would want to see the red sweater, but only 1 blue sweater, the
one with the closest score.

I hope this better demonstrates what I'm trying to do.

Thanks Crystal!

 

[John Spencer (MVP)]

You could try a query that looked like:

SELECT Y.Product, Y.Company, Y.Color, Y.Score, Y.Percent
FROM [YourTable] as Y Inner JOIN (SELECT Product
, Company
, Color
, Score
, Abs(Score-75) as HowClose) 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)]

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

 

[strive4peace]

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
*

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

You could try a query that looked like:

 

[RandieRae]

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

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
*

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

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?

 

[strive4peace]

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
*

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

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
*

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?

 

[RandieRae]

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?

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
*

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

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?

 

[strive4peace]

Hi Randie,

hopefully, John will step back in ... I am stretched for time right now..


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
*

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?

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
*

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

:

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?

 

[strive4peace]

Hi Randie,

the way that John wrote the SQL, it looks like it will match on Product,
Company, and Color

have you tried it?


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
*

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?

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
*

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

:

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?

 

[John Spencer]

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
'====================================================

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?

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
*

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

:

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?

 

[RandieRae]

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

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
'====================================================

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?

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

:

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?

 

[John Spencer]

You can copy your existing SQL statement and post it to the newsgroup.

Select View: SQL from the menu and copy and paste the SQL statement.

Perhaps Crystal or I can see the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[RandieRae]

SELECT ProductList.ProductID, ProductList.MinBeacon, ProductList.MaxLTV,
[Lender Info].Lender, ProductList.Product, ProductList.Purpose,
ProductList.Occupance, ProductList.Units, ProductList.Employment,
ProductList.[Special Notes], ProductList.[Select]
FROM [Lender Info] INNER JOIN ProductList ON [Lender Info].Lender =
ProductList.Lender
WHERE (((ProductList.MinBeacon)<=([Forms]![ClientInfo]![Beacon]))
AND ((ProductList.MaxLTV)>=([Forms]![ClientInfo]![LTV]))
AND ((([ProductList].[Purpose])=[Forms]![ClientInfo]![Purpose] Or
[Forms]![ClientInfo]![Purpose] Is Null)<>False) AND
((([ProductList].[Occupance])=[Forms]![ClientInfo]![Occupance] Or
[Forms]![ClientInfo]![Occupance] Is Null)<>False)
AND ((([ProductList].[Units])=[Forms]![ClientInfo]![Units] Or
[Forms]![ClientInfo]![Units] Is Null)<>False)
AND ((([ProductList].[Employment])=[Forms]![ClientInfo]![Employment] Or
[Forms]![ClientInfo]![Employment] Is Null)<>False))


This is just the first part of it, but should be enough to give you an idea
what I'm trying to do.

Thanks.