Time Based Data - Simple Approach (Access/SQL)

have you ever thought about time-dependent data something that is a right now and be tomorrow I will show how you can handle this with queries and access I am Philip from cabinet comm and today I would like to talk about time-dependent data so usually when we store data in a database we just store the current state of something in the database that is a record we enter it save it and that is the current state if there checked state changes and we need to update the database we will just edit the record save it again and then we got the new current state of whatever is stored in that record that is usually fine because we want the current data but what if we need to know what changed what was the same record yesterday or what was the same thing in real life yesterday and I'm not talking about some sort of audit trail that looks the changes that user apply to the database but I talked about data that changes over time but when we need to have all the previous days or at least some previous States available one example is prices in a storm if you've got a customer that orders today of course the customer needs to know the prices you display but the prices to him and he puts in an order and then you change the prices tomorrow if you would just update the record while the order is not completely processed and he would later print the invoice with the new price but the customer order for the old price so he would be rightfully really annoyed you so you need to stop old price and the new price and even if there are mechanisms to to like store the price in the order or stuff that you don't need to have the history of prices but apply them to individual orders it still would be quite annoying if you need to keep in mind when to change prices if your database is connected to the Internet or you have many employees who are not aware of price changes to a certain date and and just look into the database to see the current price and you need exact timing like midnight today you need to update the prices for them to be new prices tomorrow so that is a bit of a pain and I'm going to show you how to implement time based data like the state yesterday and the state tomorrow in a table and how to query the data let's go for our current contexts there are mainly three tables right now that are relevant of table with orders linking an order to a customer and very interesting to us the order date now the order position is just the link between the order and the actual ordered item so nothing special in here and finally the table with the items and the most important thing for now is the net price in that table and let's just think about potatoes this year was a very hot and dry year and that is bad for potato it ruined the potato harvest so the prices for potatoes have gone up and we need to raise our prices for potatoes so these two records concern potatoes if we would just change the price here we would have a problem because that would affect all orders and we cannot raise the prices for both people who ordered already so we need a different approach and for that we are going to create a new table this is the new table it will have price ID which is an autonumber field and the primary key of that table will have an item ID which is the foreign key referencing our item of course it needs to have a net price which is currency and now the important thing it has a valid from date which is date time and valid to date as well and once again it's a date time so we save this table table price and we close it now to make things easy I'm not going to check type the prices by hand but I'm creating a query to append the prices so we need the item ID from the items table and the net price and this is going to be an append query and we are going to append the prices to the new price table and I need to say this ID column from from items table should end up in the item ID field of the price table now let's just run this query and we don't need it anymore so I'm going to discard it just fetch the price table and add it to our group up here and now you see I've got already all the prices in here but the valid from and well a two dates are empty that is not a bad thing because for now all the prices have been wear constant all the time now let's look at our potatoes I'll just hide the price adikam because that is not really important and might be confusing so 14 and 15 are just my potatoes so let's say these are the old prices and I want to increase the prices beginning with 2019 so I say the old price is well it until the end of the December 2018 this is my German date format it's actually day month year don't get distracted by that so now I am going to enter a new price for the 5 kilos 5 kilo bag potatoes that is this record and I say yeah they're at 290 now beginning with the first of January 2019 and the same for the loose potatoes they will be at 1 euro nine cents beginning with the first of January 2019 I always like to allow null values to mean this price was valid from the beginning of time and the same here this price is well it from the first day of 2019 and it's valid to indefinitely okay now let's look at our queries for this I've got an owner items query here and for now our changes have no effect yet because this query is only using the net price from the items table and well to avoid confusions I'm going to remove the original net price field and going to add the table price here and this is of course linked by the ID from the items table to the item ID in the price table and now I add the net price and and quickly run this table and now you see there is an issue here because now just filter for this order there are two records with the different prices because I have not yet filtered the data by date so let's switch to sequel view because I think it is much easier to write it there and that's going to be another problem and we are going to look at that later so this is our drawing on the price table and yes we need to join via the item ID here and we need to introduce an additional join and that is why the order date because we need that as our date reference the order date needs to be greater or equal the valid from date from our table price and for those cases where we did not enter any valid from we need to add something else here and all clause or the valid from date is null now I just copied this part and change it around them because we need to check if the order date is less or equal that well it to date or the valid to date is null so and that should already do the trick I save the query and run it and I have seen I just filter for this order there are no duplicate values anymore and if we select all orders let's just focus on the potato stuff here and sort this oldest to newest you see there is this price in December 2018 and now there is automatically a new price here in January 2019 so unfortunately there is a problem with this query well not in the way that it does not work properly but in another way and I'm going to copy this to text editor for future reference just put that out of the picture because now I switch to design view and this is the problem this is one of the situations where the query works perfectly the database engine can process this query without any problem it is delivering the correct results and everything is fine the query is adhering to sequel standards there is nothing wrong with it at all except the stupid access query designer cannot handle the query wait a minute I just show you what I copied from the sequel view this is something this join clause with multiple conditions in the drawing is something that the XS graphical query designer cannot handle properly so if I click OK he complains about every part of that expression now if I click okay and switch back to sequel view it is still there but I cannot edit this query here because the query designer cannot display this expression now if I go back to the sequel view and change a little bit around then it still should be the correct result I just move the criteria to the where condition and now the query should still produce the same results you see it's still the correct result here this is the lower price and starting with a new year here we've get the newer price so the query still works and I can even switch to design view this looks a little bit messy right here because this is all entered here these expressions and they are combined with the null values so even though the query editor can display that it's not very pleasant to look at and it's not very easy to understand what's going on here so even now I would prefer to write this in the sequel view so let's just save this query and just to show you how you would go about entering new prices i just opened the price table once again and filter down to the tomatoes and let's say we need to right raise the prices once again at the 16th of January so I would just enter the 15th in here and then I need a new price beginning with the 16th of January that will be a 350 and starting with 16th of January and the loose potatoes will rise to 129 the beginning with the 16th of January and I do not need to change anything in code I'll just run the order items query and now you see here with the 16th automatically the prices were increased while on the 15th of January there is still the older lower price that was the code sample there's one thing to keep in mind in the sandal I showed only date dependent data without time this whole concept works all the same if you have data that does not change at a daily basis like that changes over at midnight but it will work all the same if it changes to a certain time but if you are dealing with such kind of data you need to store the time together with the date in the vendor to and valid from columns in my example if I wouldn't just store the order date as the day but I would store the exact order time then I would have entered like one minute to midnight like 11 o'clock and 59 minutes or if the the time even includes seconds I had to I would have had to include the seconds in the valid shoe and well it from dates well with weather from if it's at midnight you can leave that out but if you have data that changes during the day to certain times you need to keep in mind that date and time are stored together and if it can change anytime you need to store the exact time then it would work all the same okay that was it for today it's pretty easy if you see it the query is a little bit difficult and that is one of the reasons why I frequently write my sequel in a text editor not in the access query editor and rarely use the graphical query designer actually made a video about that but that aside you've got a solid solution that you actually can use in practice and I have used this solution in I don't know a number of databases in production and it works very well because the display of the data with the well it from well the two dates is something that can be displayed very easily in a data feed or in continuous form or whatever and it is easy for the user to understand the principle but there are slight issues with this approach regarding normalization but that is a topic for another video I guess it will be released next week so if you're interested don't forget to subscribe and I will explain the problem man but as I said the solution I show today is a solid solution you can use in production thank you for watching bye bye [Music]

Channel: codekabinett.com/en

Views: 1,210

Rating: 4.891892 out of 5

Keywords: Access, Time dependent data, Time based data, time range, time based validity, price changes, date range, Query, tutorial, Query Tutorial, Microsoft Access, JET-Engine, Query Designer, SQL

Id: lfUhxfbifCo

Length: 19min 29sec (1169 seconds)

Published: Tue Jan 29 2019