Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Sequencing records in queries (97)

    I am trying to write a query in Access 97 to take questions out of a database and put them into a format that is ready to import in Blackboard. Here is the query in SQL format:

    SELECT "1. " AS Sequence, Problems.Problem,
    IIf([Problems]![MCCOUNTSA]=100,"T","F") AS Answer
    FROM Problems
    WHERE (((Problems.Type)="True/False") AND
    ((Problems.Alt_Chapter)=1));

    Blackboard needs a sequence number so I then go in and change the
    Ronny Richardson

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequencing records in queries (97)

    Should the sequence number always be Alt_Chapter followed by a period and a space? If so, you could use

    SELECT Problems.Alt_Chapter & ". " AS Sequence, Problems.Problem, ...

    Or should the sequence number be 1.1, 1.2, 1.3 etc., where the number before the period is the same as Alt_Chapter?

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Sequencing records in queries (97)

    Hans,

    In all cases, it should be

    1.
    2.
    3.

    and so on. It just so happens that the example I posted had Alt_Chapter equal to 1. That represents the textbook chapter number for which I am generating a quiz to go into Blackboard.

    Ronny
    Ronny Richardson

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequencing records in queries (97)

    How is the order of the questions within a chapter determined? Is there a field (or a combination of fields) for this?

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Sequencing records in queries (97)

    These are test questions for quizzes in an academic environment called Blackboard. I wrote the questions in the order I thought of them and put them in an Access database. Their order in the database has no meaning. There order they are going into Blackboard has no meaning either. It is just that the Blackboard quiz generation tool uses the sequence number to figure out the end of one question and the beginning of the next and so needs each question to start with a sequecial number.

    Ronny
    Ronny Richardson

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequencing records in queries (97)

    Does your table have a primary key then?

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Sequencing records in queries (97)

    No primary key. I've been using the database for a long time now but I've never needed to put anything in any specific order. I started out importing the questions into MicroTest, a PC application that would randomly select questions that fit specific criteria, e.g. Chapter 2 and multiple choice. I then moved on to WebCT (online competitor to Blackboard) which can import the questions from an ASCII file and also randomly select questions.

    Now, with Blackboard, it can also import questions and randomly select them for a quiz but it has a convoluted import format so I have to use an online tool to convert the ASCII files to Blackboard format. It is that online tool that needs the sequence number.

    There is no number in the database that can be used. I had hoped to be able to use the record number from the query but cannot figure out if that is possible. Also tried to have the query just generate a new sequence number for each record but could not get that to work either.
    Ronny Richardson

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequencing records in queries (97)

    If you don't have a primary key, you can use an increment function. Copy the following code into a standard module:

    Public lngRecordNumber As Long

    Function Increment(Anyval) As Long
    lngRecordNumber = lngRecordNumber + 1
    Increment = lngRecordNumber
    End Function

    Use it like this in your query:

    SELECT Increment([Problem]) & ". " AS Sequence, ...

    The problem is that lngRecordCount will keep its value as long as the database is open (and no errors occur), so if you run the query twice, numbering will not start at 1, but at the highest value from the previous run, plus 1. To reset the number, you must either type

    lngRecordNumber = 0

    in the Immediate window (and press Enter), or you must execute this statement from a button in your database.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sequencing records in queries (97)

    Do the sequence numbers have to be strictly sequential, or do they simply have to be unique. If it's the latter, you should be able to simply add a field to the database table where the questions are stored, and make the field an autonumber field. Then all you need to do is include that in the query and you will have a unique number as the first field of your query result.
    Wendell

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Sequencing records in queries (97)

    Turns out, this works fairly well. I did not know it until I experimented but the utility I use REQUIRES that the first record be numbered "1." but after that it just wants a unique number so taking the ASCII file this query generates and editing the first record number makes it work just fine. Thanks.

    Ronny
    Ronny Richardson

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sequencing records in queries (97)

    As noted above, if you type lngRecordNumber = 0 in the Immediate window and press Enter, then run the query, numbering will start at 1, so you won't need to edit the ASCII file.

Posting Permissions

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