Need grouping by last four characters

[dan@goditmaderanch.com]

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?

[JoeM]

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.

[dan@goditmaderanch.com]

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;

[JoeM]

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:

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.

[dan@goditmaderanch.com]

Ok, here is the data

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

[JoeM]

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.

[dan@goditmaderanch.com]

Sorry, thats Filename

[JoeM]

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.