Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2016
    Posts
    7

    Cool Need grouping by last four characters

    I have a table, it is setup with the following columns
    ID | ComputerName | User | FileName | |FileCount | Size

    First I would like to figure out, how to combine all the duplicate computernames and give me a total of size associated with the computername, Second I would like to know if it is possble to tell Access that I want to group all the filename, by the last 4 characters that are alike and add the size by those also by computername.

    SO for instance I would like 2 sheets,
    First Sheet

    COmputerName | User | Total Size

    Second Sheet



    ComputerName | User | File Type | Size

    Is it Possible?

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    To group by the last 4 characters, just use the RIGHT function to return them, i.e.
    Right([FieldName],4)
    and Group on this calculated string.

  3. #3
    Join Date
    Dec 2016
    Posts
    7
    Did not work
    SELECT DISTINCTROW UserProfile12_5.Computername, UserProfile12_5.User, UserProfile12_5.Path, UserProfile12_5.FileName, UserProfile12_5.FileCount, Sum(UserProfile12_5.Size) AS [Sum Of Size]
    FROM UserProfile12_5
    GROUP BY UserProfile12_5.Computername, UserProfile12_5.User, UserProfile12_5.Path, Right([UserProfile12_5.FieldName],4), UserProfile12_5.FileCount;

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    When Grouping data, the only fields in your query should be the fields you are Grouping By and the fields you are calculating (SUM).
    Adding any other fields will cause problems, because if they are not duplicates, they will not be grouped.

    So your Aggregate Query should probably need to look something like:
    Code:
    SELECT Right([UserProfile12_5.FieldName],4) Sum(UserProfile12_5.Size) AS [Sum Of Size]
    FROM UserProfile12_5
    GROUP BY Right([UserProfile12_5.FieldName],4);
    If you want to add other fields into it, you need to do so after the fact, by linking (or nesting) this query to your original data.

    If you are still having trouble with this, please post data samples of:
    - What your data currently looks like
    - What you want your results to look like

    That would give us a clearer picture of what the data you are working with looks like, and what exactly you wanted your expected result to look like.

  5. #5
    Join Date
    Dec 2016
    Posts
    7
    Ok, here is the data
    Click image for larger version. 

Name:	1.JPG 
Views:	10 
Size:	298.9 KB 
ID:	26639
    I want the 2 tables

    When computernames are the same I want it to combine the name and give me the Total size of data, so one line per computername with a total size

    The Second table I want to be FileType last 4 characters if multiple types combine them and then sum of size for each group of files by Computername

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Well, you showed data, but not any examples of your expected output, so we can only assume exactly how you want it to look.

    When computernames are the same I want it to combine the name and give me the Total size of data, so one line per computername with a total size
    Just a simple Aggregate Query:
    - Create a new query
    - Add the Computername and Size field
    - Click on the Totals icon
    - Change the word "Group By" on the new Totals new under the Size field to Sum.

    The Second table I want to be FileType last 4 characters if multiple types combine them and then sum of size for each group of files by Computername
    Not sure I understand. I do not see any "FileType" field.

  7. #7
    Join Date
    Dec 2016
    Posts
    7
    Sorry, thats Filename

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Sorry, thats Filename
    So, based on your examples, taking the last 4 characters of FileName would look like:
    heet
    heet
    xlsx
    heet
    ntos
    xlsx
    lsen
    3729
    2015
    3188

    and you want to combine of this value (so in looking at the first 10, all the "heet" and all the "xlsx" records would be combined, whereas the others do not have any matching values.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-02-2016, 12:41 PM
  2. text with odd characters
    By vicsaccess in forum Programming
    Replies: 3
    Last Post: 01-16-2016, 02:29 PM
  3. Looking for characters that cause problems
    By Ruegen in forum Programming
    Replies: 11
    Last Post: 01-30-2014, 11:28 PM
  4. split characters
    By msasan1367 in forum Access
    Replies: 3
    Last Post: 08-01-2013, 12:50 AM
  5. Replies: 9
    Last Post: 02-11-2013, 03:09 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums