MS Access SQL - No Current Record Error

[manny213]

Hi

I am getting this error when I run a query below query:

SELECT *,
(SELECT STDEV(LOG_PRICE_CHANGE)
FROM QRY_LOG_PRICE_CHANGES AS T1
WHERE T1.UNDERLYING_SYMBOL = QRY_LOG_PRICE_CHANGES.UNDERLYING_SYMBOL AND
QRY_LOG_PRICE_CHANGES.ROW_NUMBER-T1.ROW_NUMBER BETWEEN 0 AND 20) AS STD_DEV
FROM QRY_LOG_PRICE_CHANGES

Here are the steps I have taken so far in troubleshooting this issue:

1. I ran QRY_LOG_PRICE_CHANGES and it ran with no issues. This means this query is correct. The query is slow though. Could that be an issue? Does Access have a timeout feature that I am not aware of? It probably takes 2 minutes max to run.

2. If I change QRY_LOG_PRICE_CHANGES to TBL_LOG_PRICE_CHANGES then noted query runs without any issues. This means that the noted query will process the data that is found in QRY_LOG_PRICE_CHANGES so there are no data issues or incompatible header issues.

3. The test database is about 1.6 GB. Access has a 2 GB limit (i think). I created a new temp DB and had a linked table created that displayed the raw data from the test database in the temp database I created. I then copied the two queries (QRY_LOG_PRICE_CHANGES and above noted query) into the temp DB. I still get the same error message.

4. I have checked online for this error and it makes references to SYSDATA folder. I checked my computer and I don't even have this folder. Here is the Microsoft literature:

https://support.microsoft.com/en-us/topic/run-time-error-3021-no-current-record-1265be01-5850-c5e3-5...

Not sure if this is related or not. Obviously, Microsoft says it is so it probably is. I just don't understand why only this query is giving me a hard time. I have multiple queries that are running off the same main table etc and no issues with those. It is just this one query that is giving me a problem.

5. Not sure if this is related or not. I always use alias of the form TX where X = 1,2,3, etc. I was not able to create the second alias so it is left as is.

6. Running a simple query works:

SELECT *
FROM qry_Log_Price_Changes;

I am really stumped here. Not sure what else to do.

Does anyone know what is going on here?

[#2]

@manny213 

Hi

Is anyone able to help me with this?

Thank you

[#3]

Does the subquery work as a standalone query?

You can find various timeout settings in Access options.. Client Settings....Advanced

[#4]

Thanks for the reply.

Yes the base query QRY_LOG_PRICE_CHANGES runs fine on its own.

I took a look at the Client Settings...Advanced options. There are 5 settings. Which one would you suggest I look at?

[#5]

@manny213 

That wasn't my question. This is the subquery:

SELECT STDEV(LOG_PRICE_CHANGE)
FROM QRY_LOG_PRICE_CHANGES AS T1
WHERE T1.UNDERLYING_SYMBOL = QRY_LOG_PRICE_CHANGES.UNDERLYING_SYMBOL AND
QRY_LOG_PRICE_CHANGES.ROW_NUMBER-T1.ROW_NUMBER BETWEEN 0 AND 20

Does that run as a standalone query?

These are my settings:

How do yours differ from those?

[#6]

Sorry about that confusion.
No, that subquery will not run. It is saying "QRY_LOG_PRICE_CHANGES.UNDERLYING_SYMBOL" is a parameter and wants a value.

My settings are the same as yours.

[#7]

OK. You need to ensure that works as a standalone query before you can use it as a subquery.
Try inserting a value that you know exists and then see if it works as standalone / subquery.
If so, you need to find a way of adding the parameter to the query

[#8]

a few things...
1. i re-ran the subquery and put in random values for the two parameters. it ran without any errors but the value it produced was NULL.

2. secondly, that subquery you mentioned definitely does work. one of the things i tried when I was troubleshooting this was try the below query:

SELECT T2.UNDERLYING_SYMBOL, T2.QUOTE_DATE, (SELECT STDEV(LOG_PRICE_CHANGE)
FROM TBL_LOG_PRICE_CHANGES AS T1
WHERE T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND
T2.ROW_NUMBER-T1.ROW_NUMBER BETWEEN 0 AND 20) AS STD_DEV_21
FROM TBL_LOG_PRICE_CHANGES AS T2;

noticed that TBL_LOG_PRICE_CHANGES is used and not QRY_LOG_PRICE_CHANGES

QRY_LOG_PRICE_CHANGES runs fine on its own

for some unknown reason, the query doesn't like it when qry is used in the FROM clause

[#9]

I ran the query again and now I am getting a different error. I am getting "Invalid value to function"

SELECT T2.UNDERLYING_SYMBOL, T2.QUOTE_DATE, (SELECT STDEV(LOG_PRICE_CHANGE)
FROM TBL_LOG_PRICE_CHANGES AS T1
WHERE T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND
T2.ROW_NUMBER-T1.ROW_NUMBER BETWEEN 0 AND 20) AS STD_DEV_21
FROM QRY_LOG_PRICE_CHANGES AS T2;

If I run the below query then no issues:

SELECT T2.UNDERLYING_SYMBOL, T2.QUOTE_DATE, (SELECT STDEV(LOG_PRICE_CHANGE)
FROM TBL_LOG_PRICE_CHANGES AS T1
WHERE T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND
T2.ROW_NUMBER-T1.ROW_NUMBER BETWEEN 0 AND 20) AS STD_DEV_21
FROM TBL_LOG_PRICE_CHANGES AS T2;

Only difference is the FROM part. The first is referencing a query and the second is referencing a table

Any idea on what is going on here? So strange...

[#10]

Its difficult to know based on the info you've supplied.
Can you upload a cut down version of your database with all relevant items?
Remove or change any confidential data

[#11]

@isladogs 

good idea.  thank you for suggesting this.

i have attached a sample db. 

========

qry_v1_alias

========

gives "The specified field 'T2.Store' could refer to more than one table listed in the FROM clause of your SQL statement" error
not sure why it is giving this error.

this is a problem b/c I don't know why it doesn't like alias.  To be honest this is a minor problem.  if I can't use alias then no big deal.

==========

qry_v1_no_alias

==========

gives "No Current Record" error

this is the main problem I am having

=======

qry_v2_tbl

=======

works fine
uses tbl as source and not qry

[#12]

.

[#13]

@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

[#14]

Thanks for the reply

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?

[#15]

@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

[#16]

is anyone able to help me with this?