Results 1 to 15 of 15
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: need to combine record fields

    I've searched but have not yet found an answer to this particular problem.
    I would very much appreciate some help with it.
    Problem:
    I have a table with multiple client records with differing data in the same field per record.
    I need to gather up all the records for say clientxx and then store them in a single clientxx record with a number of fields containing the data- say edata 1, edata2, edata3 up to 10. The fields are already defined in the destination table from 1 - 10.
    diagram:
    (record).(field) becomes (record).(field1).field2).(field3)
    clientxx.edata|
    clientxx.edata|> move to second table = clientxx.edata1.edata2.edata3
    clientxx.edata|

    Is there a way to do this??

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one technique good be to write a series of queries which take a specific element and put it into another table column

    it may be worthwhile look at pivot table, there is something else that Access in Access that is similar, forget its name.

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Quote Originally Posted by WilliamS
    I've searched but have not yet found an answer to this particular problem.
    I would very much appreciate some help with it.
    Problem:
    I have a table with multiple client records with differing data in the same field per record.
    I need to gather up all the records for say clientxx and then store them in a single clientxx record with a number of fields containing the data- say edata 1, edata2, edata3 up to 10. The fields are already defined in the destination table from 1 - 10.
    diagram:
    (record).(field) becomes (record).(field1).field2).(field3)
    clientxx.edata|
    clientxx.edata|> move to second table = clientxx.edata1.edata2.edata3
    clientxx.edata|

    Is there a way to do this??
    my first inclination would be to use two recordsets. get the clientxx info in the first. loop through, and each time gather in recordset2 the records for that client. then insert those into the new table, using whatever method you want to keep up with numbering.
    something like :
    Code:
    Set rs1 = CurrentDb.OpenRecordset("SELECT fldClientID FROM table")
    
    Do While rs1.EOF=False
    
     Set rs2=CurrentDb.OpenRecordset("SELECT * FROM table " _
      & "WHERE fldClientID=" & rs1.Fields(0))
    
     Do While rs2.EOF=False
      
      'insert the info from rs2 fields into the new table here
      
      rs2.MoveNext
     Loop
    
     rs1.MoveNext
    Loop
    
    Set rs1=Nothing
    Set rs2=Nothing
    this would be using DAO. just one example. i'm sure someone else will post a better way before long. good lluck

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    think healdem may be talking about a crosstab query in Access

  5. #5
    Join Date
    May 2004
    Posts
    159
    a crosstab query just shows me the data from the old record but does not put it in the appropiate new fields.
    I am working along the lines of a series of queries that check if the previous field has been filled or not but it seems a bit of a cludge and is going to be a lot of queries when done.
    I like using code in a loop or an array but am not strong in that area and will need a simple example to modify and use.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So I think you need to either write a VBA procedure / fucntion OR (a better option in my view) a series of queries, one inserts and n-1 update queries. These can be assembled into a macro

    the only wrinkle is going to be to make sure you don't duplicate records (so you need to design some form of mechanism to identify those records which should be added (that could be based on a date value, or checking to see if the record already exists, or set a flag int eh original table which indicates if that value has been imported).


    an alterntivae approach may be to have a series of queries which are all joined on a pk as subselects or standalone queries

    each individual query does something similar to
    select primarykeycolumn as key1, myvalue as value1 where vlautetype =1
    select primarykeycolumn as key2, myvalue as value2 where vlautetype =2
    ...and so on

    and then do super query which links on the primary column
    HTH

  7. #7
    Join Date
    May 2004
    Posts
    159
    I'm having some issues getting the update queries to acommodate all the values. what do you mean by valuetype = 1 or 2? I tried using a count but I couldn't reliy on the appropiate values popping up. EG I can set the query to choose those records with a count of 2 but I can't determine if it is the 1st or 2nd value counted.
    I feel what would be the most elegant way to do this is in an array but I don't know how to define it.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not knowing your data
    but I'd imagine that you would want the result of one type of test to be in one column

    say its a testing system and you have your current data in the form
    testid | testtype | value
    eg
    134 | MHT | 100.34
    134 | ZBS | 23.4

    and so on

    and you want yur new row
    testID | MHT | AAC | ZBS | xxx.....
    134 | 100.3 | | 23.4

    so for consistency you would need to know that colu,n 3 in the new record is always test type "ZBS"

    so you would need to test what type of result it is to assign the value to the appropriate column.

    incidentally where are you using this information.. is it in multiple consumers (forms & reports, or is it just one report).

    if its one report there is a cunning workaround that can be performed without using queries, you just have to be prepared to get your hands dirty with some VBA behind the report.

  9. #9
    Join Date
    May 2004
    Posts
    159
    This is used for some medical reporting we have to do.
    We have a list of medical record numbers with codes for the proceedures done, each proceedure is listed with a MRN and there are more than one instance of each MRN.. Now we need to submit one medical record number with all the proceedues for that MRN listed under the fieldnames of DXE2, DXE3. DXE4 etc up to 10.
    It doesnt really matter what order they are in as long as all the proceedures are listed in the record which is part of a file we upload.

    I've got the query version set up but it isn't pretty. I needed two queries for each field- a standard query to gather the data and check if its a unique value and an update query to put it in the right field. try as I might I couldn't get an update query to handle it all so for 10 fields I have twenty queries. Definitly a kludge!
    I would prefer to do this with an array and will see if I can come up with one.
    Last edited by WilliamS; 02-21-08 at 16:34.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you will find that using SQL will be dramatically faster (and more efficient) than resorting to code.

    if you feel upto it (eg secuirty issues) I'd be tempted to post your DB here and see if there are other ways of doing what you want. sometimes its a lot easier for contributors to understand what you want by seeing the data, than by description alone.

  11. #11
    Join Date
    May 2004
    Posts
    159
    But would SQL be faster? I've heard arguments that Access compiles the queries to be as fasr as SQL.
    Anyway here is the SQL for the two queries needed. If they could be combined into one SQL that would be great.
    first I run this
    Code:
     SELECT tblPrimaryRecord.PCONTROL
    FROM tblEcodes INNER JOIN tblPrimaryRecord ON tblEcodes.VISIT_NO = tblPrimaryRecord.PCONTROL
    WHERE (((tblEcodes.DXE_ID) Not Like [tblprimaryrecord].[DXE1]));
    then I run this from the above

    Code:
     UPDATE tblPrimaryRecord INNER JOIN queryforEcode2 ON tblPrimaryRecord.PCONTROL = queryforEcode2.PCONTROL SET tblPrimaryRecord.DXE2 = [DXE_ID];

    I run the updates sequentially with each one adding the previous fields to check and filter out dups. If there were a way to get a single SQL statement
    for each field that might be faster.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    SQL will be faster than using VBA
    SQL operates on sets of data, VBA operates item by item

    how many groups/items do you have
    is the order of those items important
    how are you sending that information back to the customer

  13. #13
    Join Date
    May 2004
    Posts
    159
    Quote Originally Posted by healdem
    SQL will be faster than using VBA
    SQL operates on sets of data, VBA operates item by item

    how many groups/items do you have
    is the order of those items important
    how are you sending that information back to the customer
    about 100 records a month and each record has one unique med_rec number with 10 DXE fields to store Edata and not all the DXE fields are used but they must be unique. This is all stored in an export table.
    Data is sent in comma deliminated form for each field but if we can just get the export table filled I have the rest covered.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you could define your DXE fields in a table with an indexID which indicates which column the data should be put in.

    pickup that INDEXID and assign the value for that item as the column No
    then merge all those individual queries into a top level query.

    The attached zip file seems to do what you what

    HTH
    Attached Files Attached Files

  15. #15
    Join Date
    May 2004
    Posts
    159
    Hmm but a couple of flys in the ointment. The actuall dxe codes number in the hundreds just the dxe fields in the table number 10 Usually only 1 or 2 per record are used.

    well next week I'll look at this again. Thanks for trying to help!
    Last edited by WilliamS; 02-22-08 at 13:19.

Posting Permissions

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