Forum Discussion
MS Access SQL - No Current Record Error
The database I sent is a small subset of the full database. The repeating values are there by design. There are other fields in the table that uniquely identify each record. The values I care about for the purpose of this query are going to be repeating. That is why I have a DISTINCT keyword in my base query for QRY_LOG_PRICE_CHANGES.
I intentionally did not want a table added. The tables add to the size of the DB so I have decided to rely on queries to provide data for other queries. Not sure if this is in line with proper DB practices but that is what I did. I am not opposed to them if they are truly needed.
The STDEV calculation is a rolling calculation. For example, on Nov 3 2021 go back 10 days and use those 10 days to calculate the STDEV (Oct 25 to Nov 3). On Nov 2 2021 go back 10 days and use those 10 days to calculate the STDEV (Oct 24 to Nov 2) etc.
I have the calculation working as I have envisioned it but the problem I am having is that it will not accept QRY_LOG_PRICE_CHANGES as a base. It will only work if TBL_LOG_PRICE_CHANGES is used instead. This is my problem that I am trying to resolve. Do you know why that is the case?
The repeating values may be there by design but its a big mistake.
Sorry if it appears rude but, as stated, the design is very badly flawed in several ways. No primary key fields. No indexes. Duplicated data. If you want those types of data table, use Excel NOT Access.
Adding tables can reduce database size where they prevent the need for repeated data. Using the ID value in each record takes up far less space & makes it less likely that there will be data entry errors. To remind you , the changes I made meant the database size was reduced from 43MB to 500kB approx.
You can easily modify the StDev query I provided to filter it for selected date ranges.
For example:
SELECT qryStoreData.Store, StDev(qryStoreData.Price_Change) AS StDev
FROM qryStoreData
WHERE (((qryStoreData.quote_date) Between #3/9/2020# And #3/19/2020#))
GROUP BY qryStoreData.Store;
or using parameters:
SELECT qryStoreData.Store, StDev(qryStoreData.Price_Change) AS StDev
FROM qryStoreData
WHERE (((qryStoreData.quote_date) Between [Enter Start Date] And [Enter End Date]))
GROUP BY qryStoreData.Store;
Having spent well over an hour going through this and making it workable, I'm really not willing to spend any more time analysing the original database.
You need to change the table structure - the sooner the better. It will take a little time to do so but will save you a huge amount of time in the long run. I assure you it will be worthwhile.
Good luck with your project