Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: Changing record configuration

    Evening,

    I've got a database of examination entries with the fields CandidateNr, UnitNr, OptionCode.

    The exam board needs this exported in the form CandidateNr, UnitNr1, OptionCode1, UnitNr2, OptionCode2, UnitNr3, OptionCode3, UnitNr4, OptionCode4, UnitNr5, OptionCode5, UnitNr6, OptionCode6, UnitNr7, OptionCode7,....

    That is: each candidate has a list of the units that they take with the option code for each listed in the fields after their name. Can anyone think of a way to do this?

    Thanks in advance!

  2. #2
    Join Date
    May 2010
    Posts
    601
    This should help get you started:

    Return a concatenated list of sub-record values
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    Thanks for that.

    As I understand it, this will generate a field with a list of all the Units for which a Candidate is entered. Is there any way of then splitting these into separate fields?

  4. #4
    Join Date
    May 2010
    Posts
    601
    Without knowing the format for the file the you will be exporting into and other details about what you are doing, I can only give you examples that show the techniques that should apply.

    The example shows how to loop through child records. This technique, not the exact code, should be able to be applied to your task.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I've got a database of examination entries with the fields CandidateNr, UnitNr, OptionCode.

    1. Using code you will need to sort the data by Candidate number and unit number.

    2. Create an two dimensional array to store the Candidate number in each row and the unit number and option codes in the columns. In this format
    CandidateNr, UnitNr1, OptionCode1, UnitNr2, OptionCode2, UnitNr3, OptionCode3, UnitNr4, OptionCode4, UnitNr5, OptionCode5, UnitNr6, OptionCode6, UnitNr7, OptionCode7,....

    3. Then write the array to a table.

    4. If you require assistance please post some sample data.

  6. #6
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Poppa Smurf View Post
    I've got a database of examination entries with the fields CandidateNr, UnitNr, OptionCode.

    1. Using code you will need to sort the data by Candidate number and unit number.

    2. Create an two dimensional array to store the Candidate number in each row and the unit number and option codes in the columns. In this format
    CandidateNr, UnitNr1, OptionCode1, UnitNr2, OptionCode2, UnitNr3, OptionCode3, UnitNr4, OptionCode4, UnitNr5, OptionCode5, UnitNr6, OptionCode6, UnitNr7, OptionCode7,....

    3. Then write the array to a table.

    4. If you require assistance please post some sample data.
    Curious, Why the extra step of reading the records into and array and sorting with code then reading through the array to write the records? Is this not handling the same data an extra time?

    Could you not:
    1) use a query to sort the records in a recordset
    2) loop through the records and writ to a table
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Curious, Why the extra step of reading the records into and array and sorting with code then reading through the array to write the records? Is this not handling the same data an extra time?

    Could you not:
    1) use a query to sort the records in a recordset
    2) loop through the records and writ to a table
    1. The data would be sorted using SQL or a query before you loop through the records.
    2. Using an array to store the results then writing the results to a table is quicker than looping and writing each record to the table.

    I found step 2 was useful when transposing data from one format to another e.g. the rows of table 1 become the columns of table 2 and the columns of table 1 becomes the rows of table 2. It also prevents bloating of the database.

Posting Permissions

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