A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to use dynamic query in Access 2007 Form?



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 02:13 AM posted to microsoft.public.access.forms
klim1167
external usenet poster
 
Posts: 1
Default How to use dynamic query in Access 2007 Form?

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

  #2  
Old June 1st, 2010, 04:02 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default How to use dynamic query in Access 2007 Form?

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"klim1167" u60492@uwe wrote in message news:a8da1e93430ae@uwe...
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

  #3  
Old June 2nd, 2010, 10:23 AM posted to microsoft.public.access.forms
klim1167 via AccessMonster.com
external usenet poster
 
Posts: 2
Default How to use dynamic query in Access 2007 Form?

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


Allen Browne wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201006/1

  #4  
Old June 2nd, 2010, 02:10 PM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default How to use dynamic query in Access 2007 Form?

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"klim1167 via AccessMonster.com" u60492@uwe wrote in message
news:a8eaf7585359e@uwe...
Thanks a lot, Allen.
Can you give me an example how to implement a parameter query in a form
without a pop up window?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.