Results 1 to 7 of 7
  1. #1
    armyofdux is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Posts
    55

    Update Query for manual entry

    Is there a way to have an update query update values in a table based on the user's input?


    For example, I have categories of tests and educational assignments indigenous to a course I manage. I would like Access to give me a popup or window to ask me a question?

    So it would go something like this? "How many students failed the Science Exam?" The user would then put the manual calculation in since it varies from semester to semester.

    In access it looks like this.

    Tbl 1530ScienceClick image for larger version. 

Name:	Untitled.jpg 
Views:	19 
Size:	135.6 KB 
ID:	18809
    Column ID: Tracking Questions


    Column ID: Value

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    At the simplest:

    UPDATE TableName
    SET FieldName = [How many students failed the Science Exam?]
    WHERE...(optional)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    armyofdux is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Posts
    55
    Ok I can get the popup to function but i only want it to update 1 row/record it wants to update 40... i am sure I am typing something wrong/

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You need the WHERE clause with the appropriate criteria. Typically that would come from a form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    armyofdux is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Posts
    55

    Database attached

    SMS Buildout.ZIP Here is what I am doing.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Those 6 Buildout tables should really be one table with another field for category (B, D, F, G, H, N).

    Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

    Need the SMS ID value as criteria to identify specific record to UPDATE.

    UPDATE [15B30 Buildout] SET [15B30 Buildout].[Number] = [Number of Students enrolled at beginning of course] WHERE [SMS ID] = [enter SMS ID];
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    theyad75 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Posts
    13
    ur database not organize

    1-all tables have same kind of data u can put them all in one table

    as June7 say u have to put the data u have in one table u can do that by exporting each table to excel then copy and past to one table sheet.

    this are ur distinct categories and 30mos u have make table for each of them
    create like below
    after that do what ever u want but first u need right tables see tables in picture

    Categories
    Category_id Category_name
    1 General Academic
    2 Admin
    3 APFT Data
    4 APFT/HT WT
    5 Counseling
    6 Fill Rates/ATRRS
    7 Folder Header ID
    8 Grad Rate
    9 MOS Spec Academic
    10 Perf Eval General

    30MOS
    30MOS_ID 30MOS
    1 15B30
    2 15D30
    3 15F30
    4 15G30
    5 15H30
    6 15N30

















    Click image for larger version. 

Name:	MASTER.jpg 
Views:	14 
Size:	30.2 KB 
ID:	18838

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Entry Update Form Problem with the update
    By cuddles in forum Forms
    Replies: 1
    Last Post: 05-30-2014, 02:38 PM
  2. Manual Primary Key Entry
    By harpreett.singhh@gmail.co in forum Access
    Replies: 1
    Last Post: 03-03-2014, 03:09 PM
  3. Replies: 2
    Last Post: 05-20-2013, 05:23 PM
  4. Replies: 6
    Last Post: 12-01-2011, 10:54 AM
  5. Replies: 3
    Last Post: 05-19-2010, 10:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums