Results 1 to 2 of 2
  1. #1
    bak is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Australia
    Posts
    1

    Question Access SQL Assistance

    Hello,

    I have a couple of questions about Access SQL in Access 2016. I am working on creating a database in Access that will produce a random list of results based on a randomly assigned value. I am very new to SQL and am still learning the basics.

    Here are the steps in my process:

    1. qry1 - Selects all data in tbl1 and appends a random value to each record as Random in a new column.
    2. qry2 - SQL query with the following syntax:
    SELECT TOP 4 [qry1].A, [qry1].B, [qry1].C
    FROM [qry1]
    ORDER BY [qry1].Random;

    [qry1].A = short text


    [qry1].B = short text
    [qry1].C = number (only 1-5)

    I am trying to include a second variable in the selection process ,[qry1].C, where a user can select a level of intensity (HIGH, MED, or LOW) from a combo box on a form with the following results:

    HIGH will return a list of 4 random records, where SUM([qry1].C >15)
    MED will return a list of 4 random records, where SUM( 15 >= [qry1].C > 12)
    LOW will return a list of 4 random records, where SUM([qry1].C <= 12)

    I am having a lot of trouble and would appreciate anyone's help with the SQL syntax, or any suggestions on other ways to set up my queries. I am also planning to incorporate other options on the form in the future that I will need to include in my macro.

    If you need anymore information please let me know.

    Thanks!

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Function fcnRan5(arg As Long) As Integer
        Randomize
        fcnRan5 = (4 * Rnd(arg)) + 1
    End Function
    The SQL for below query is
    Code:
     UPDATE tblAnalysis SET tblAnalysis.TVRandom = fcnRan5([ID]);

    Attachment 25308
    An update query will create random numbers in the table as above. First, modify tbl1 to have a new field named random.
    Copy the function to a module.
    Create and run the above query, call it qAddRandom or such.
    The code assumes that the table, your tbl1 has an autonumber field named ID. (the example table name is tblAnalysis, and field name is TVRandom)
    You can't use this query as qry1 as it is an update query and returns no data as a select query does.
    So, you can either dispense with qry1 and just have qry2 select its data from tbl1 instead of qry1.
    OR, have qry1 just select all data in tbl1 which will now include the random field.
    Last edited by davegri; 07-29-2016 at 09:03 AM. Reason: added SQL view

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

Similar Threads

  1. Replies: 9
    Last Post: 08-17-2015, 09:39 AM
  2. Access Assistance
    By otis in forum Access
    Replies: 1
    Last Post: 03-05-2015, 01:03 PM
  3. Urgent Access Database Assistance
    By ETCallHome in forum Access
    Replies: 4
    Last Post: 04-04-2014, 08:35 AM
  4. need assistance on image size in access
    By paulkimball in forum Reports
    Replies: 0
    Last Post: 05-16-2012, 10:01 PM
  5. New to Access - Looking for some basic assistance
    By Dave_EntryIT in forum Access
    Replies: 2
    Last Post: 03-27-2011, 11:25 PM

Tags for this Thread

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