Creating YTD Statistics

[bradical987]

I apologize right of the bat for my lack of knowledge about Access. I used to be alright with it, but after about a 10 year hiatus, I don't remember a thing. Currently, I have a pretty substantially sized Excel workbook containing weekly performance data for a program being tested. Basically, every week I get a CSV with about 20 columns of performance data that I copy and paste onto a new sheet in my Excel workbook. One of my tabs is for YTD stats. The format of the sheet is column A being the name of the tester, and then the next 20 columns are different aspects of performance data that is gathered based on how the tester used the program. Unfortunately, after two months of testing, calculating YTD stats has caused my Excel file to slow to a crawl when I try to add new data, sort data, etc., because all of the YTD data is created through formulas that sum the weekly data for each tester. I also am calculating averages, max, min, etc.



What I currently have (in my attempt to try to remember how to use Access) is a table for each week containing all the weekly data and a table of tester names. I've created a relationship between the "TesterName" field in tbl_TesterInfo and "TesterName" of each table containing the weekly data. I have been trying to figure out how to make a query that takes all of my weekly performance data for each tester and generates a YTD table. The format would be the exact same as the weekly data tables, but just summing together the data. Ultimately, I would like something that will auto update when I add a new table, rather than going in and manually modifying a bunch of query statements to include the new table. (Hopefully I am explaining this well enough)

After struggling for a couple days and not coming across a solution in my many hours of searching, I started to wonder if maybe my database design is going to really limit what queries I can do or if it is just that I have no idea what I am doing, so it seems more complex than it really is. If anyone can point me in the right direction, it would be greatly appreciated.

[ranman256]

every week you would import the csv file into an access table. This would have the weekEnding date.
your query would sum/avg the date range between jan -Current month and produce YTD totals.

[CJ_London]

use a group by query - in the query window, click on the epsilon totals button on the ribbon - group by testers names and as Ranman says, sum or avg as required the weekly values