asked
Newbie Help!
Hi Guys, hope you can help. A colleague is storing a huge amount of data in excel which I’ve suggested would be better stored in sql tables. So far I’ve had no problem building the tables containing all the relevant data. However, my problems are three fold; 1. This data is updated monthly and needs to be imported (by layman) into sql without creating duplicates. 2. Each master item (product) has a life of 10 years and in any (or none or multiple) of these months an adjustment may he added and must be recorded. I can capture this fine but when it comes to query or export this information I end up with hundreds of columns on each product - mostly blank but some containing these amendments. I am stumped at any way around this as it seems I need a column for each month ‘in case’ there is an amendment figure. 3. I’m currebtky importing each new month of amendments as a new table but then need to update my query to reflect the new table - am I doing this wrong? There are thousands of products so I can’t oitput each individually but if I have each product on a row I encounter the issue above. Am I going mental? There must be a way around this....! Many many thanks for any help at all Neil
I'm guessing here, because don't have your full schema info, but sounds like you need a Product table and an Adjustment table, with effective and termdates on the adjustments, and a productID to link back to. Not sure what exactly changes monthly, but you might also need a Price table or something (for whatever is changing) where you can put the timeframe where that particular price for that product is effective. Then your query will just look up the product by joining the three tables to find the appropriate price for that product, applying any adjustment effective within the timeframe of the order. Does that make sense?
18 People are following this question.
