Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    making a field name a field (A2k2, SP-2, DAO)

    Hi All,
    This *should* be easy, or at least straightforward, but I'm really close to brain-dead at the moment and I need to get this done.
    I have a table which has, say, 16 fields. 1 of those fields has a repeating value (8 individual values) which I would like to have as a field heading with the values of 3 other fields selectively chosen (based on other criteria) to be its field values for use in a report.

    Now:
    <pre> Fields 1-12 Field 13 Field 14 Field 15 Field 16
    </pre>


    Need:
    <pre> Fields 1-12 Field 13
    Field 14
    Field 16
    Field 14
    Field 15
    </pre>



    Would someone please give me a nudge in the proper direction?
    Thank you

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    I don't understand what you want.

    Would you be more explicit, say, give an example.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    Hi Patt and Thanks,
    Yes, I know. What a terrible example as it was left. It wasn't completed before the post and I must have mi######. Told you I was near brain-dead. <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15>
    How about this, a shortened version but the idea is the same:

    Now:
    <pre> Fields 1-12 Field13 Field14 Field15 Field16
    Values 1-3 Values 1-3 Values 1-3 Values 1-3
    </pre>



    Need:

    <pre>Fields 1-12 Field13-1 Field13-2 Field13-3
    Field14-1.Value Field14-2.Value Field14-3.Value
    Field16-1.Value Field16-2.Value Field16-3.Value
    Field15-1.Value Field15-2.Value Field15-3.Value
    </pre>


    As I tried to say in my initial post, the values placed into these "new" fields are based on other criteria and may be chosen from Field 14, 15 or 16.
    I hope this makes a bit more sense and I do apologize for the utter suckiness <img src=/S/barf.gif border=0 alt=barf width=64 height=23> of the initial post.
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    I still don't understand what you are getting at. I'm obviously brain dead as well.

    Maybe someone else will work out what you are trying to do. If I knew what you wanted I may be able to help, but the understanding side of my brain is obviously not working. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

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

    Re: making a field name a field (A2k2, SP-2, DAO)

    As Patt wrote, this is still not very clear. Perhaps you can create a small demo in an Excel spreadsheet containing a table with dummy data, and a table that shows how you want it to come out.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    Hi Patt, Hans:
    Sorry.
    Hope this helps.

    gdr
    Attached Files Attached Files
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

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

    Re: making a field name a field (A2k2, SP-2, DAO)

    Hi Gary,
    I think waht you want to do is step through your original recordset and build a record for each set of records in the new table. Because you are building one new record from multiple existing records, you may have to repeat that process several times based on the algorithm you describe on your worksheet. Hope I've understood the problem correctly.
    Wendell

  8. #8
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    Hi Wendell,
    Thank you.
    Are you suggesting that I use a Do...Loop with nested If...Then's or Select Case on the PersonDesignator (M, CH* or TM*) and the ProbeLocus? Or as usual, do I understand? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    I've been wondering if that is what I'd have to end up doing. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Thanks again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    First I presume that you are trying to build a new table?
    If so, you would need to create part of the new table up to the Designators. Next have a do loop to read thru the distinct values of fields up to ProbeLocas and create the fields in the new table.
    Next a do loop to update the values using the distinct query from the previous and min and max values.

    I'm not sure if you could use a crosstab query to create the new table, I would have to experiment somewhat.

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

    Re: making a field name a field (A2k2, SP-2, DAO)

    Hi Gary,

    The attached procedure seems to do more or less what you want. Since your demo spreadsheet only included data for PersonDesignator "M", I can't be sure that it is correct for the others. You'll have to substitute the actual names of the tables/queries.
    Attached Files Attached Files

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    Is this a one-off or are you going to be doing this multiple times?

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    I imported your sheet into an Access table and concocted the following SQL query:
    TRANSFORM IIf([PersonDesignator]="M",
    Min([tblFinalPassThrough].[MotherBandsLow]) & (", " + Max([tblFinalPassThrough].[MotherBandsHigh])),
    iif(PersonDesignator="C",
    Min([tblFinalPassThrough].[ChildBandsLow]) & (", " + Max([tblFinalPassThrough].[ChildBandsHigh])),
    iif(PersonDesignator="T",
    Min([tblFinalPassThrough].[TestedMaleBandsLow]) & (", " + Max([tblFinalPassThrough].[TestedMaleBandsHigh]))))) AS MinOfMotherBandsLow
    SELECT tblFinalPassThrough.DataDetailsPKID, tblFinalPassThrough.tblCardShuffle_IGICaseNumberDa taDetailsPKID, tblFinalPassThrough.qryAlleleCheckFiltered_IGICase NumberDataDetailsPKID, tblFinalPassThrough.IGICaseSubNumber, tblFinalPassThrough.PersonDesignator, tblFinalPassThrough.Designators
    FROM tblFinalPassThrough
    GROUP BY tblFinalPassThrough.DataDetailsPKID, tblFinalPassThrough.tblCardShuffle_IGICaseNumberDa taDetailsPKID, tblFinalPassThrough.qryAlleleCheckFiltered_IGICase NumberDataDetailsPKID, tblFinalPassThrough.IGICaseSubNumber, tblFinalPassThrough.PersonDesignator, tblFinalPassThrough.Designators
    PIVOT tblFinalPassThrough.ProbeLocus;

    I have also included the DB I imported the data into. The query is called tblFinalPassThrough_Crosstab_ChildLow.
    HTH
    Attached Files Attached Files

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

    Re: making a field name a field (A2k2, SP-2, DAO)

    Sorry - I got sidetracked yesterday - but it looks like Hans has whipped up some code similar to what I was thinking of. Let us know if that doesn't do the trick.
    Wendell

  14. #14
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    Hi Patt,
    Sorry for the delay.
    Thanks a bunch. I had been trying to do just what you did with a pivot, but was unable to as I mucked up the 'Value' portion consistantly.
    The weekend past wasn't a complete wash after all. I 'learnt' something.
    Thanks again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  15. #15
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: making a field name a field (A2k2, SP-2, DAO)

    Hi Hans,
    Sorry for the delay.
    Not more or less, it does do it.
    I thoroughly double checked and I couldn't find anything that this coding missed and I am at least a bit happy for myself in that I had started down that path and didn't have to change too much of what I had (to match yours).
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Page 1 of 2 12 LastLast

Posting Permissions

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