I am trying to make a database and form to enter data we currently have daily printouts for going back 5 years.
Ideally I want to have the form designed to look like the spreadsheet printout so that it is easy for someone to enter the historical data.
I've set up tables for:
Locations
Autonumber
Location name
Items
Autonumber
ItemType
PriceTable
Autonumber
PriceDate
PriceValue
Item FK integer
CompetitorID FK integer
I can create a form that will allow me to input one location's two price items for a given date - a process that would have to be repeated 50 times for each locations' two prices for each day, which I don't want to have to do. I'm struggling to figure out a form entry system where the date is at the top & all 25 locations' price data gets updated with one button click (50 prices to be added to the table).
A very rough layout of my vision is attached - all these are currently unbounded. Just a quick throw together to show what layout I'm looking for.