I currently have data set up in an excel spreadsheet like the data in my attachment--basically I have various bits of information pertaining to number of hours worked for different Job Order Numbers (JO). I currently have over 100 job order numbers, each with between 1 and 8 rows of information, depending on how many Fiscal Years (FY) and departments (Code) are represented for each one.
My boss likes the way the initial spreadsheet is set up, but would like a summary spreadsheet that lists the JO, the Job Description, and the total number of labor hours by year. (If you look at the attachment, you'll see what the original spreadsheet looks like, and what the summary spreadsheet should look like--ignore the awarded/accepted columns for now.) The summary spreadsheet should pull all information from the original spreadsheet and both should be easily updatable. I originally posted my problem here: Excel forum thread .
I think this data is better suited to Access. I have an understanding of what Access does, but need to teach myself how to make it do what I want (including writing macros/VBA code). My question for now is, should I make one table that lists a record for each row from my spreadsheet? Or should I make a separate table for each JO? Also, if I create separate tables for each JO, is it possible to write a macro(?) that will let me enter new information that will then be added/updated to the corresponding table, or create a new table if one doesn't already exist?