MS Access SQL - No Current Record Error

manny213

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

manny213 

I don't normally download files from external sites such as OneDrive but I made an exception in your case. You couldn't upload your file because it was HUGE...approx 43MB because one of the tables contained over 1.1 million records - mostly duplicates. I've removed all the duplicates and only 67 records were left! After compacting, it reduced to about 500kB - approx 100 times smaller.

Looks very much like you are used to working in Excel and haven't grasped the basic concepts of Access

You are trying to do some complex subquery work but have ignored proper database design

1, Neither table has a primary key field. I've added an autonumber ID field to each table as a PK field

2. You are duplicating data unnecessarily: Store & QuoteDate

3. I've added a table tbl_Store with 2 fields StoreID (autonumber OK) & Store (text) then replaced Store with StoreID in the other 2 tables.

4. The Row_Number field in tbl_PriceChanges is superfluous as its the same as the PriceChangeID PK field

You only have 1 entry for each date in tbl_PriceChanges which means that table isn't needed.

5. I've added the PriceChange field to tblData and populated it.

So you now only need two tables tblStore & tbl_Data.

I've created a relationship between them & added referential integrity

The old tables can now be deleted as can your 5 queries & my update query. All items ready for deletion are prefixed with a #.

Next I've created a much simpler query qryStoreData based on the 2 remaining tables.

SELECT tbl_Store.Store, tbl_Data.quote_date, tbl_Data.store_price, tbl_Data.Price_Change
FROM tbl_Store INNER JOIN tbl_Data ON tbl_Store.StoreID = tbl_Data.StoreID;

Finally based on that I've created qryPriceChangeStDev 

SELECT qryStoreData.Store, StDev(qryStoreData.Price_Change) AS StDev
FROM qryStoreData
GROUP BY qryStoreData.Store;

This has one record with the StDev for price changes for the only store in your data.

Is that what you want? See attached screenshot (Capture.PNG)

If not, please use qryStoreData as a starting point to get the end result you want

Hope that helps you move forward. Suggest you study a book on the basics of database design