Query in Access/Excel that shows count of instance in records

[TechnoPup]

Greetings,
I am very new to working with databases, and I am not sure how to go about structuring the query I need. What I have is an Access database with approx. 400,000 records in 5 fields. The fields are owner, filename, size, path, modified date. What I would like to do is take the field "filename", and come up with a count of the different file types listed in the records (XLS, PPT, DOC, etc...) as well as a sum of the bytes that those counts entail. The filenames are in long filename format with the typical .XXX extensions. The ultimate goal is to get this information into Excel where I can put together a summary page with a distribution breakdown of all the file types, their byte count and their overall impact on the server storage space.
The queries I have attempted will get me the list of, say XLS files, but will not show me the other types available. I realize this is because I have hard-set the criteria to "XLS" in the query.

Having rambled on enough above, my questions are:
1. How can I structure the query to filter on all extensions available without having to hard set the criteria for each individual one?

2. How can I import that query into Excel where I can use a pivot table or other means to make actionable the results of the query?

Any help you good folks can provide will be most appreciated. Thanks in advance for your time and expertise.

Ron

[NS3687]

Ron,

I think you need to do a few things there.
1. To add some functions to create a seperate column for file extension.
Assuming that .XXX is consistent in all your records and always at the end of the long filename.

MID([Filename], Len(Trim([Filename]))-4, 4)

2. 400,000 records won't work in Excel. Max you have 65,536. I suggest you do a few "views" to structure your data, i.e.,

View01 - you create query using design view to add Extension column from the original table you have.

View02 - you use from View01 as input. You can do a count by EXT to get how many files per each type, then do a SUM of file size.

As for making it some ability to simulate Pivot table - I have no idea. I'm looking to find ways for that as well

Hope this help -
NS3687