Hello Community!
I hope you can lend some advice. I am COMPLETELY new to working in access, so please forgive me if the answers should be obvious (they aren't to me). Although I am familiar with principles from reading through design basics, I don't have enough experience to know if I am on the right track in my design or not. I hope you can help me work through what to do next.
I have a report to generate monthly (on the first of each month) where I work. The data is for the stock value on the first of the month for each brand, plus the monthly turnover and average margin for the month just passed for each brand. (eg - stock value on 1 Jan 2018, monthly turnover and average margin for December 2017). Also, I would like to be able to import all of this data in one go with an Excel spreadsheet.
I am getting hung up on a few points:
Should I store the data by the entry date (i.e. first of the month) given how I want to import the data, or is this bad design in the long run? My current design draft (obvs incomplete at this point) -assumes this is ok. If indeed it is okay, then how do you then sort/display/query data for the previous month if they are all in the same table? I have a separate date table which I had hope would be able to distinguish between 1st of month and previous month. However I am not confident in designing it this way, ie how to utilize the turnover/avg margin date column from it when it comes to the main table.
Would it be better to store data in separate tables by the date of the data (rather than by entry date), and if so, how would you append the imported data (by columns from a single excel worksheet) into separate tables in access.
The end game here is to be able to pull historical data by brand (by month/year etc) and include charts with overlapping line graphs. The existing report is in excel at the moment and needs an upgrade/streamlining due to sheer amount of data involved.
A few notes relating to my design pdf:
*brand id used to ensure no duplication - there are just shy of 1000 brands, some with similar names
*turnover and margin date col =is manually entered date in chart, formatted as mmmm yyyy, aso opposed to being a formatted version of data entry date
*Value type = value or not value (to designate no data from lack of data, such as when a brand is discontinued)
All suggestions (and patience with my newness) appreciated.
Thanks