How to use dynamic query in Access 2007 Form?

[klim1167]

Dear Friends,
I have a table named "class_grades" and with the fields of "First Name",
"Last Name", "Age", "Math", "Physics", "Computer" and "State".
I would like to build an Access form with dynamic query...
1. For the age and state, it's "drop_down select"
2. For the Math, Physics and Computer, I can enter the number and click an
arrow button (to match the number with +/- 10%), then it will show all the
matched list.
All these "keyin" and "results" should be shown in the form (form + sub-form?)

I am sincerely looking for your help to guide me how to implement it.
Thank you very much.

First Name Last Name Age Math Physics Computer State
drop_down keyin # keyin # keyin # drop_down
select <-> <-> <-> select
Wendy Alexander 20 76 76.5 77.3 NV
Charles Alsberry 20 78 78.5 79.3 MT
..............................................................................
.....................................


Below is my table :
First Name Last Name Age Math Physics Computer State
Wendy Alexander 20 76 76.5 77.3 NV
Charles Alsberry 20 78 78.5 79.3 MT
Rebecca Alstrum 20 74 74.5 75.3 UT
Lindie Alvey 21 78 78.5 79.3 CA
Jennifer Armstrong 21 79 79.5 80.3 KS
Nancy Ash 21 73 73.5 74.3 KS
Mysty Atkins 21 87 87.5 88.3 NE
Lauritz Austensen 22 78 78.5 79.3 MT
Tom Baer 22 91 91.5 92.3 UT
Roger Baird 22 77 77.5 78.3 NM
Dana Baker 22 84 84.5 85.3 NE
Jennifer Barnes 22 79 79.5 80.3 OK
Christine Bayles 22 88 88.5 89.3 AZ
Justin Benjamin 21 76 76.5 77.3 WA
John Benson 21 89 89.5 90.3 NM
Amanda Berg 19 81 81.5 82.3 NV
Steve Bier 19 88 88.5 89.3 MT
Vicki Blaine 19 88 88.5 89.3 TX
Janna Borth 19 76 76.5 77.3 OR
Mark Briggs 20 88 88.5 89.3 NE
Chris Brown 20 88 88.5 89.3 NM
Kara Anne Brown 21 81 81.5 82.3 CA
Michelle Brown 20 86 86.5 87.3 AZ
Jennifer Burgess 21 86 86.5 87.3 TX
Jody Butts 21 89 89.5 90.3 AZ
Matthew Cain 21 85 85.5 86.3 NV
Jonathan Campbell 21 90 90.5 91.3 NE
Bethany Carpenter 19 82 82.5 83.3 NE
Kara Cheese 19 80 80.5 81.3 MT
Joseph Chevalier 19 91 91.5 92.3 OK
Amy Conald 19 81 81.5 82.3 CA
Kevin Crowley 20 82 82.5 83.3 NE
Jeff Curry 20 78 78.5 79.3 NM
Jennifer Curtis 20 86 86.5 87.3 UT
Julia Davis 20 92 92.5 93.3 UT
Rixie Davis 21 76 76.5 77.3 NV
Brett Dickson 21 93 93.5 94.3 UT
Bradley Ditchen 21 82 82.5 83.3 OK
Chris Dixon 20 88 88.5 89.3 ND
James Elm 21 74 74.5 75.3 NV
Julie Falk 22 88 88.5 89.3 NV
Kristopher Fannin 22 93 93.5 94.3 NV
Kimberly Farkas 22 82 82.5 83.3 TX
Joshua Fecht 22 91 91.5 92.3 TX
John Feely 22 90 90.5 91.3 ND
Richard File 22 76 76.5 77.3 OR
Todd Foldesi 21 91 91.5 92.3 TX
Gina Garcia 21 87 87.5 88.3 NM
Ridonna Gaston 21 76 76.5 77.3 MT
Sarah Gill 21 79 79.5 80.3 KS
Eric Graff 21 86 86.5 87.3 MT
Jeff Graham 21 78 78.5 79.3 ND
Jennifer Griffin 21 87 87.5 88.3 NV
Chris Grotbo 19 88 88.5 89.3 SD
Kristen Guilliams 19 83 83.5 84.3 UT
Steven Halcomb 19 89 89.5 90.3 CA
Jennifer Hall 19 87 87.5 88.3 WA
Virginia Hardy 19 76 76.5 77.3 UT
Gregory Harris 19 87 87.5 88.3 ND
Jennifer Hemphill 19 88 88.5 89.3 OR
Bill Herman 19 82 82.5 83.3 KS
Michael Horn 19 85 85.5 86.3 OR
Rob Hougas 20 76 76.5 77.3 CA
J Lane Hunnicutt 20 74 74.5 75.3 UT
Lee Isaacson 20 78 78.5 79.3 NV
Jennifer Jeans 21 88 88.5 89.3 MT
Brooke Johnson 21 93 93.5 94.3 WA
Kimberle Jolliff 21 82 82.5 83.3 OK
Dawn Kelley 21 84 84.5 85.3 OK
Heather Kiel 22 73 73.5 74.3 NE
Jason King 22 76 76.5 77.3 MT
Elizabeth Klopfens 22 85 85.5 86.3 OR
Stephanie Kneipp 22 87 87.5 88.3 OR
Michael Lamonica 22 85 85.5 86.3 MT
Paul Langellier 22 73 73.5 74.3 OK
Dung Le 21 85 85.5 86.3 NV
Mark Lipic 21 84 84.5 85.3 KS
David Lorber 19 84 84.5 85.3 KS
Lisa Mack 19 88 88.5 89.3 NM
John Malito 19 90 90.5 91.3 SD
Erin Malone 19 86 86.5 87.3 NV
Jason Marshall 20 76 76.5 77.3 NV
Ryan Maschhoff 20 78 78.5 79.3 SD
Lara McClusk 20 93 93.5 94.3 OR
Jane McCorma 21 76 76.5 77.3 WA
Bridgette McDowall 21 83 83.5 84.3 NV
Sean McPeak 21 86 86.5 87.3 NV
Molly Meyer 21 87 87.5 88.3 ND
Marc Mickiew 21 88 88.5 89.3 SD
Ted Molis 19 90 90.5 91.3 NE
Dawn Morgan 19 84 84.5 85.3 TX
Kevin Naylor 19 82 82.5 83.3 KS
Scott Nierstheim 19 86 86.5 87.3 UT
Jason Nourie 20 77 77.5 78.3 CA
Amy O'Connor 20 81 81.5 82.3 AZ
Son Ong 20 87 87.5 88.3 WA
Erin O'Rourke 20 86 86.5 87.3 CA
Jeffrey Otto 21 78 78.5 79.3 SD
Meredith Otto 21 85 85.5 86.3 WA
Sayuri Ozeki 21 79 79.5 80.3 TX
Brenden Penn 20 83 83.5 84.3 TX
Andrew Peterson 21 82 82.5 83.3 SD
Rich Peterson 22 74 74.5 75.3 WA
Sue Phelan 22 89 89.5 90.3 NM
Rob Pritts 22 77 77.5 78.3 AZ
Marty Prochnow 22 84 84.5 85.3 TX
Tony Ragusa 22 92 92.5 93.3 NV
Thomas Reed III 22 91 91.5 92.3 OK
Kari Robb 21 81 81.5 82.3 NM
Gretchen Roetzer 21 87 87.5 88.3 SD
Sara Romance 21 78 78.5 79.3 NE
Kara Romine 21 80 80.5 81.3 NV
Aaron Ruby 21 80 80.5 81.3 WA
Amy Ryan 21 81 81.5 82.3 NM
Michael Sams 21 86 86.5 87.3 NV
Susie Scherb 19 90 90.5 91.3 SD
Jeffrey Schryver 19 79 79.5 80.3 NE
Sarah Scott 19 79 79.5 80.3 OK
Charles Sieg 19 78 78.5 79.3 NV
Bryan Simkins 19 78 78.5 79.3 OR
Lana Smith 19 83 83.5 84.3 WA
Terence Smith 19 90 90.5 91.3 KS
Adrien Smith III 19 80 80.5 81.3 MT
Moria Smoski 19 87 87.5 88.3 SD
Suresh Soundar 20 89 89.5 90.3 ND
Felicia Spakowski 20 87 87.5 88.3 AZ
Steve Stabley 20 88 88.5 89.3 NV
Jason Steele 21 77 77.5 78.3 AZ
Rebecca Steiner 21 74 74.5 75.3 NV
Jennifer Studeba 21 89 89.5 90.3 NV
Jerry Sullivan 21 89 89.5 90.3 CA
Heather Tatum 22 73 73.5 74.3 KS
Adam Trudo 22 80 80.5 81.3 OR
Karen Truttmann 22 81 81.5 82.3 AZ
Heather Van Kirk 22 73 73.5 74.3 OK
Philip Vancil 22 73 73.5 74.3 TX
Michael Voelker 22 86 86.5 87.3 CA
Luke Vybiral 21 88 88.5 89.3 ND
Linette Wagner 21 88 88.5 89.3 AZ
Kari Watkins 19 81 81.5 82.3 ND
Marshall Watson 19 84 84.5 85.3 OK
Andrea Weber 19 82 82.5 83.3 ND
Steven Werby 19 89 89.5 90.3 AZ
Douglas Wilken 20 85 85.5 86.3 UT
Katie Wills 20 82 82.5 83.3 SD
Justin Woith 20 80 80.5 81.3 OR
Jonathan Woodruff 21 91 91.5 92.3 KS
Holly Woodson 21 74 74.5 75.3 TX
Miriam Wyman 21 87 87.5 88.3 NM
Ruth Wyman 21 78 78.5 79.3 ND
Chris Yeadon 21 88 88.5 89.3 CA
Eleanor Yeh 19 85 85.5 86.3 WA
Matt Young 19 84 84.5 85.3 UT

 

[Allen Browne]

The table you have is structured like a spreadsheet rather than a database.
If you build it that way, you lose most of the power and reliability that a
relational database can give you, and you make it difficult to query the
results.

For a suggestion on how to build relational tables to handle school grades,
see this example:
Relationships between Tables
at:
http://allenbrowne.com/casu-06.html

If you have never built or seen a relational design before this may sound
strange, but it is the absolute core understanding to grasp of how
relational databases like Access work.

 

[klim1167 via AccessMonster.com]

Thanks a lot, Allen.
Can you give me an example how to implement a parameter query in a form
without a pop up window?

The table you have is structured like a spreadsheet rather than a database.
If you build it that way, you lose most of the power and reliability that a
relational database can give you, and you make it difficult to query the
results.

For a suggestion on how to build relational tables to handle school grades,
see this example:
Relationships between Tables
at:
http://allenbrowne.com/casu-06.html

If you have never built or seen a relational design before this may sound
strange, but it is the absolute core understanding to grasp of how
relational databases like Access work.

Dear Friends,
I have a table named "class_grades" and with the fields of "First Name",

[quoted text clipped - 173 lines]

Eleanor Yeh 19 85 85.5 86.3 WA
Matt Young 19 84 84.5 85.3 UT

 

[Allen Browne]

Best to ask a new question as a separate thread.

If you don't want the parameter dialog popping up, then I presume you want
it to pick up the parameter values from an open form. You can do that by
specifying parameters like this:
[Forms].[Form1].[Text0]

For information on a better way to do it, see:
http://allenbrowne.com/ser-62.html
The last section of that article illustrates how to do it with a parameter
query reading values from a form, if you really want to do it that way
instead of building a filter string.