Thanks, Bill for your detailed critique. Indeed, although I have a degree in computer science, there’s no wonder I’ve never been paid to write a line of code!
The table I am using is primarily for managing all things related to each piece of art I create, whether it is a studio piece or something commissioned by a client. Each piece of art is a record with about 30 fields of non-date related fields. So there are things like the medium, material, size, price, total hours etc.
I also have a habit tracker that is formatted as you mentioned by having days as records, but I couldn’t get it to work for artworks because I need to link and summarize hourly data for each artwork, as I track my productivity by weeks and months since I started painting three years ago. I also have a time budget based on price for each commissioned piece. I need to monitor my use of that budgeted time as time goes by.
I switched over from using Excel spreadsheets for everything, and so it is obvious that the database has not been “designed”. Plus I have invested too much time, and don’t have enough time in the foreseeable future to start from scratch.
The quick fix workaround I decided on was to condense all the historical day fields into weeks by manually summing them up and deleting fields. After five years (or ~250 fields) I can store the summarized info on another table and then condense further into year fields, keeping the most recent data in weeks and the forward planning info as days, in order to stay within the 500 column limit.
If there was a way that I could enter the daily hours I plan on painting and the hours I actually spend painting on each piece and have it link into my primary artworks table that would be brilliant, but this is the first database I have used in the 20+ years since university, and I couldn’t figure out a way to get it done!
You can see more of my work at http://joyacousin.com/. I have only been at this professionally for a year, but thanks for asking!