Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Apr 2009
    Posts
    90
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Combining records

    I have a table structure in Access 2007 as shown below

    Category Percent
    ABC 1.1
    ABC .5
    DEF .75
    DEF 1.2

    I would like to combine the like Categories into a new field as shown:

    ABCCategory DEFCategory
    1.1 .75
    .5 1.2

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    MOSTATE,

    I would caution against doing this as you will move away from the normalization rules used in relational databases such as Access. Of course you can still do this but it would not be considered good database design. Could you enlighten us as to why you want to make the change? There may be other ways more in keeping with good design to accomplish your goals. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    Star Lounger
    Join Date
    Apr 2009
    Posts
    90
    Thanks
    9
    Thanked 0 Times in 0 Posts
    The original source table will stay intact as I would like the new object, table or query, to be based on the query results so that each like Category can be summarized with just the CategoryName and each unique Percent amount. There are hundreds of records that contain like category names each with a percent amount. The summarization would only put the category name and the percent combination one time.This will be used for end of year reporting. If you need further clarification please let me know.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    MOSTATE,

    Ok, maybe I'm not quite understanding what you want to do. It would be helpful if you could provide a sample of the desired year end output based on the sample records above. As I read your previous post it sounds to me like you want to summarize (I read average) percentages? If this is the case it is a bad idea, as I see it, since this will lead to a very unreliable number given my limited understanding of statistics. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    Star Lounger
    Join Date
    Apr 2009
    Posts
    90
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Based on the original data there will be several records that have ABC as the Category with a percent, some may have the same percent. I would like a field created based on the Category data i.e., ABCCategory, along with each percent for the instances of Category ABC. The report will then show all the ABCCategory and their percents. I do not want any functions performed on the percent field I just want the percent data to display. (Sorry I can't get the data to align properly but ABCCategoryPercents and DEFCategoryPercents would be 2 separate fields along with all the different percentages listed for each.)

    ABCCategoryPercents
    1.1
    .5

    DEFCategoryPercents
    .75
    1.2

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    MOSTATE,

    Do you mean something like this?
    percents.JPG
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    Star Lounger
    Join Date
    Apr 2009
    Posts
    90
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Yes, but ABC and DEF are separate fields with the percents being the records.
    ABC DEF
    0.5 1.2
    1.1 0.75

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    MOSTATE,

    So you're saying that:

    Code:
    RecordID  CategoryABC  CategoryDEF
    1                 0.5         1.2
    2                 1.1         0.75
    If the above is the case I don't see how you get there from the table structure given in post #1.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Maybe a crosstab query might be what you want? But you would have to massage the tables first.
    I would like to see a database of this.
    Last edited by patt; 2014-07-30 at 06:27.

  11. #10
    Star Lounger
    Join Date
    Apr 2009
    Posts
    90
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I am attaching a workbook that shows a sample of what is actually entered in the database on the first worksheet and how they want the report to look on the second worksheet. I don't have access to the database as this is an end user that is asking for assistance and I don't have a copy of the actual database. Hopefully the workbook with help illustrate what they want. They would like Access to take the like data values in the RPTGCat fields and create a new fieldname that shows each individual Percent. Please let me know if you have any questions after reviewing the workbook. Thanks so much to all for trying to assist!
    Attached Files Attached Files

  12. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Will there be only 4 Categories and their associated %'s in the Excel sheet?

    If so, then it is very straight forward to split these into another table and do the report using another query.
    Last edited by patt; 2014-07-30 at 20:24.

  13. #12
    Star Lounger
    Join Date
    Apr 2009
    Posts
    90
    Thanks
    9
    Thanked 0 Times in 0 Posts
    No, there are about 10 categories and 10 percent fields along with other fields. I would like for the query to create a field based on the criteria in the RPTGCat fields and group all the % for those like categories as shown on the 2nd worksheet in the sample workbook.

  14. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Does that mean there are always 10 pairs of Categories and percent fields per Excel sheet line or can it vary from 1 to 10 pairs.
    You say "with other fields", what other fields and what is to be done wit these other fields?
    A larger sample would help.

  15. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    This may be of help, let me know.
    Attached Files Attached Files

Posting Permissions

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