Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2013
    Posts
    11

    Unanswered: difficult to create following query

    my question is in ms word 2010 format.you have to have ms word to view it.plz click link below.
    <link removed>
    or click below
    <link removed>
    Last edited by gvee; 06-30-13 at 15:34. Reason: Links removed.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your links have been removed as I don't believe the website to be safe or sanitary i.e. I wouldn't personally download a file from there.

    If you want help then I suggest you go to the effort of copying the text out of your file in to this thread so we can all see without the need to download something extra.

    P.S. we don't do homework on this site
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2013
    Posts
    11
    i wanted a friendly discussion.not homework.
    PS:its not homework.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Okay, so if you share the contents of the word document/the problem and questions here we might be able to have a debate
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2013
    Posts
    11
    if i pasted in this forum then you wouldnt understand it.it would be simply a mess.with names and numbers.in ms word file i have made tables to illustrate what i wanna say.btw the link is a very popular filesharing site.it doesnt contain any viruses.

  6. #6
    Join Date
    Jun 2013
    Posts
    11
    in fitness database created in MS access 2010 have following 2 tables: Member and Program.
    Member(MemberID,ProgramID,First,Last,Street,City,S tate,Zip,Phone,DateJoined,Expiration,Status.)
    Program( ProgramID, ProgramType, MonthlyFee, Physical Required.)
    it is required that i have to create the following query:
    but i dont know how.plz help me.
    show only records for customers from Richmond whose membership or on hold.

    MemberID First Name Last Name Phone Date Joined
    1156 Kurt Eisler 804-236-8961 4/17/2013
    1142 Kye Nguyen 804-236-0325 2/20/2013
    1120 Carmen Sanchez 804-674-5991 10/18/2013
    1166 Abigail Turner 804-674-6807 9/1/2013
    It has to be created by pressing create then create design query tab.its not to be created by access vba programming.
    I don’t know how to use query design method to achieve above query.
    Member table data sheet is:

    Member
    Member ID Program ID First Name Last Name Street City State Zip Phone Date Joined Expiration Date Status
    1103 201 Joseph Eckler 29 Prospect Street Ashland VA 23005 804-550-3050 1/16/2013 1/16/2014 Active
    1105 204 Barry Hassan 9 Harrington Avenue Richmond VA 23220 804-236-6717 3/2/2013 3/2/2015 Active
    1106 202 Debbie Ward 18 Barton Street Richmond VA 23219 804-674-8462 4/22/2013 10/22/2013 Active
    1110 201 Ashish Mittal 103 Hubbard Way Glen Allen VA 23058 804-553-4737 4/3/2013 4/3/2014 On Hold
    1111 203 Liz Sorrento 134 Lincoln Road Chester VA 23831 804-751-1270 2/10/2013 8/20/2013 Active
    1115 203 Michelle Kim 290 Brook Mill Road Bon Air VA 23235 804-323-0291 5/17/2013 5/17/2015 Active
    1116 207 Robert DeCosta 35 Washington Street Richmond VA 23220 804-236-4121 8/4/2013 8/4/2014 Inactive
    1117 203 Paul Novick 302 Great Road Ashland VA 23005 804-550-1164 9/20/2013 9/20/2015 Active
    1120 202 Carmen Sanchez 1294 Monument Avenue Richmond VA 23219 804-674-5991 10/18/2013 9/25/2014 On Hold
    1121 209 Kelly Smith 7 Lakeview Road Chester VA 23831 804-751-0466 7/18/2013 7/18/2014 Active
    1125 203 Jessica Picard 17 Briarwood Lane Mechanicsville VA 23111 804-559-0909 6/22/2013 12/22/2013 Active
    1126 204 Sam Bethel 258 Elm Street Mechanicsville VA 23111 804-559-1750 5/19/2013 5/19/2014 Active
    1131 207 Olivia Alexander 29 Newtown Circle Richmond VA 23220 804-236-0642 3/11/2013 3/11/2015 Active
    1135 201 Tina Sun 682 Hickory Way Chester VA 23831 804-751-9111 6/3/2013 6/3/2014 Active
    1136 212 Mary Grant 913 Main Street Chester VA 23831 804-751-8772 5/1/2013 5/1/2015 Active
    1137 210 Alan Fraser 41 Carver Hill Road Mechanicsville VA 23111 804-559-5885 3/20/2013 3/20/2014 Active
    1139 210 Peter Grigas 3 Pleasant Street Glen Allen VA 23058 804-553-1642 6/9/2013 6/9/2015 Inactive
    1140 206 Ronald Cunningham 52 Putnam Road Ashland VA 23005 804-550-7732 2/4/2013 8/4/2013 Active
    1142 207 Kye Nguyen 456 Harringont Avenue Richmond VA 23220 804-236-0325 2/20/2013 2/20/2015 On Hold
    1143 206 Barry Feinberg 73 Endicott Avenue Chester VA 23831 804-751-1119 10/5/2013 10/5/2014 Active
    1144 208 Colleen Murphy 47 Winter Street Mechanicsville VA 23111 804-559-1651 6/9/2013 6/9/2014 Active
    1146 209 Marlene Halpin 75 Elm Boulevard Bon Air VA 23235 804-323-0346 7/17/2013 7/17/2015 On Hold
    1147 210 Elijah Slomich 108 Roosevelt Avenue Mechanicsville VA 23111 804-559-5060 8/2/2013 8/2/2015 On Hold
    1148 212 Todd Wolfe 18 Parker Avenue Richmond VA 23219 804-674-2140 9/20/2013 9/20/2014 Active
    1150 203 Zach Adgate 92 Apple Blossom Lane Ashland VA 23005 804-550-6119 10/15/2013 10/15/2014 Active
    1151 202 Isabella Cimino 429 South Street Ashland VA 23005 804-550-9870 8/2/2013 8/2/2014 Active
    1153 212 Vinnie Tumbiolo 358 East Street Richmond VA 23219 804-674-1434 5/19/2013 5/29/2015 Inactive
    1154 204 Jamal Asmal 9872 Turnpike Road Richmond VA 23220 804-236-2194 6/7/2013 6/7/2014 Active
    1155 210 Juliette Beaumont 35 Walnut Street Glen Allen VA 23058 804-553-1275 8/1/2013 8/1/2014 Active
    1156 208 Kurt Eisler 5 Bradford Circle Richmond VA 23220 804-236-8961 4/17/2013 4/17/2015 On Hold
    1158 201 Pedro Fuente 89 Mockingbird Lane Chester VA 23831 804-751-1147 9/5/2013 9/5/2014 Active
    1159 211 Maggie Hadley 6 Rose Court Ashland VA 23005 804-550-6103 3/19/2013 3/19/2014 Active
    1160 212 Mark Reynolds 11 Pond Lane Mechanicsville VA 23111 804-559-4642 6/20/2013 6/20/2015 Active
    1165 207 Shea McKiernan 780 Flint Street Ashland VA 23005 804-550-0107 7/13/2013 7/13/2014 Active
    1166 203 Abigail Turner 99 Maple Road Richmond VA 23219 804-674-6807 9/1/2013 9/1/2015 On Hold
    1167 204 George Krukonis 14 Collins Drive Richmond VA 23219 804-674-4333 5/5/2013 5/5/2014 Active
    1169 211 Maria Gonzalez 100 Tower Drive Richmond VA 23220 804-236-6337 5/19/2013 5/19/2014 Active
    1170 203 Student First Student Last 40 Green Boulevard Bon Air VA 23235 804-323-6824 6/3/2013 12/3/2013 Active
    1172 211 Ed Curran 25 Fairway Drive Richmond VA 23220 804-674-0227 10/18/2013 11/16/2014 Active
    Program table datasheet is
    Program
    Program ID Program Type Monthly Fee Physical Required
    201 Junior Full(Ages 13-17) 40.00 Yes
    202 Junior Limited(Ages 13-17) 30.00 Yes
    203 Young Adult Full(18-25) 50.00 No
    204 Young Adult Limited(ages 18-25) 35.00 No
    206 Adult1 Limited (ages 26-35) 45.00 No
    207 Adult2 Full (ages 36-50) 60.00 No
    208 Adult2 Limited (ages 36-50) 45.00 No
    209 Senior1 Full (ages 51-65) 50.00 Yes
    210 Senior1 Limited (ages 51-65) 35.00 Yes
    211 Senior2 Full (ages 66 and over) 40.00 Yes
    212 Senior2 Limited (ages 66 and over) 30.00 Yes
    Thanks.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select MemberID from Member
    where city = "Richmond" and Status = "On Hold"

    the program table is irrelevant to this query

    storing status as an alphabetic string is asking for trouble unless you constrain the values somehow. you could do that constraint in the form however that still means that someone could enter data into the tables incorrectly. so an idealised means of storage would be to store status in a table and use the PK of that table to identify a members status as a FK in Members

    if you wanted to refine that further
    select MemberID from Member
    where state = "VA" AND city = "Richmond" and Status = "On Hold"
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2013
    Posts
    11
    teach me and other less techies by choosing some options in ribbons.dont teach by using code.i would understand it if i know vba programming.by the way thanks for your reply buddy.
    <a href=http://www.filedropper.com/msaccess2010databaseissuemodified><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >share files free</a></div>

  9. #9
    Join Date
    Jun 2013
    Posts
    11

  10. #10
    Join Date
    Jun 2013
    Posts
    11
    there is no on hold field.so above solution is not working.

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    "On Hold" is not a field. It is a value within the field [Status].
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jumhg View Post
    teach me and other less techies by choosing some options in ribbons.dont teach by using code.i would understand it if i know vba programming.by the way thanks for your reply buddy.
    <a href=http://www.filedropper.com/msaccess2010databaseissuemodified><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >share files free</a></div>
    well if you aren't prepared to look at the answer then I doubt I can help you. I don't use the touchy feely oohh that looks cute interface. Ive been using PC's since 1984 and hate the way the world is going, masking the actual function with form (presentation)

    if you are unwilling to dip your toes into the world of SQL then your developm,ent career will either be short or very very unfullfilling and undemanding.
    likewise with VBA

    However the code provided was SQL NOT VBA

    you can get the similar effect if you create a query in the query designer

    create a new query
    plonk the the member table in the query designer
    drag whatever columns you need into the boxes below

    make certain that you include the STATE, CITY and STATUS columns
    in the criteria row for
    STATE add = "VA"
    CITY add = "Richmond"
    STATUS add "On Hold"
    execute the query

    ..and No Im not going to look at files dumped on another site. if there is a need to look at your db IU'll usually request thart you attach it to a post here as a zip file, havign cleaned out any sensitive data and "compact and repaired" the DB
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •