UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Causes Access To Abort Without Messages, Access 2013    
 
   
OldMike
post Jan 15 2020, 03:48 PM
Post#1



Posts: 79
Joined: 21-July 15



Hi, this isn’t stopping me doing anything, but I’m puzzled by this behaviour. I’m running Access 2013 on a Windows 10 machine. Simple database with two tables tbl_Reading and tb_Rates as shown below:
tbl_Reading
Readings_ID Reading_Date Reading_Value Rates_ID
1 01/02/17 5 1
2 05/05/17 23 1
3 12/10/17 43 1
4 31/12/17 55 2
5 03/02/18 70 2
6 04/04/18 82 3
7 06/07/18 99 3
The Reading_Value is a meter reading.
tbl_Rates
Rates_ID Rate_Value
1 0.25
2 0.255
3 0.35

The Rates_ID field in tbl_Reading links relates to the Rates_ID field in tbl_Rates In order to calculate Payments (note this is pure test data), I need to find the difference between consecutive readings and multiply by the rate relating to the second reading. The Readings_ID value is an autonumber so I can’t depend on this being a consistent consecutive number, so I use qry_Create_RowNumber to give me consistent consecutive number. The query is:
SELECT (select count(*) from tbl_Reading where Reading_Date <= t1.Reading_Date) AS Row_Number, t1.Reading_Date, t1.Reading_Value, t1.Rates_ID FROM tbl_Reading AS t1;

This gives what I need – see below:
qry_Create_RowNumber
Row_Number Reading_Date Reading_Value Rates_ID
1 01/02/17 5 1
2 05/05/17 23 1
3 12/10/17 43 1
4 31/12/17 55 2
5 03/02/18 70 2
6 04/04/18 82 3
7 06/07/18 99 3

I then use the Row Numbers that I’ve created in query_Readings_Joined to match the consecutive rows. The query is:
SELECT t1.Reading_Value AS Reading_Start, t2.Reading_Value AS Reading_End, t2.Reading_Date AS Reading_Date, t2.Rates_ID AS Rates_ID, t2.Row_Number AS Row_Number FROM qry_Create_RowNumber AS t1 INNER JOIN qry_Create_RowNumber AS t2 ON t1.Row_Number + 1 = t2.Row_Number;
qry_Readings_Joined
Reading_Start Reading_End Reading_Date Rates_ID Row_Number
5 23 05/05/17 1 2
23 43 12/10/17 1 3
43 55 31/12/17 2 4
55 70 03/02/18 2 5
70 82 04/04/18 3 6
82 99 06/07/18 3 7

I then use further query qry_Readings_Joined_WithRates to get the related Rate Value. This query is:
SELECT Reading_Start, Reading_End, Reading_Date, Rate_Value, t1.Rates_ID FROM qry_Readings_Joined AS t1 INNER JOIN tbl_Rates AS t2 ON t1.Rates_ID = t2.Rates_ID ORDER BY Reading_Date;
The result of this is:
qry_Readings_Joined_withRates
Reading_Start Reading_End Reading_Date Rate_Value Rates_ID
5 23 05/05/17 0.25 1
23 43 12/10/17 0.25 1
43 55 31/12/17 0.255 2
55 70 03/02/18 0.255 2
70 82 04/04/18 0.35 3
82 99 06/07/18 0.35 3

With this result set I can then calculate payment (Reading_End – Reading_Start) * Rate_Value, and this all works.
However if I try to show the related Row_Number in the final query Access simply thinks about it for a few minutes (spinning cursor) aborts and restarts – no messages.
The query that causes this is qry _Readings_Joined_withRates_withRown the detail of the query is:
SELECT Reading_Start, Reading_End, Reading_Date, Rate_Value, t1.Rates_ID, t1.Row_Number FROM qry_Readings_Joined AS t1 INNER JOIN tbl_Rates AS t2 ON t1.Rates_ID = t2.Rates_ID ORDER BY Reading_Date;
As mentioned earlier, this isn’t actually stopping me doing anything at the moment, however I’m wondering if there’s a problem in my installation of Access – even if there’s something that Access can’t deal with I would have expected a message of some kind.
Go to the top of the page
 
GroverParkGeorge
post Jan 15 2020, 04:58 PM
Post#2


UA Admin
Posts: 36,515
Joined: 20-June 02
From: Newcastle, WA


My first suspicion is corruption.

Try decompiling/recompiling the accdb and doing a compact & repair.

Sometimes queries can get corrupt, so try creating a duplicate from scratch, as well.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
OldMike
post Jan 15 2020, 05:29 PM
Post#3



Posts: 79
Joined: 21-July 15



Hi George the dB doesn't contain any modules it's only tables and queries. I created it very simply because I was getting the behaviour on another dB and I wanted to test the very simplest case. Does the decompile/recompile approach still apply
Go to the top of the page
 
GroverParkGeorge
post Jan 15 2020, 05:34 PM
Post#4


UA Admin
Posts: 36,515
Joined: 20-June 02
From: Newcastle, WA


Probably not.

However, I have seen queries go corrupt. It wouldn't hurt to rebuild each of the ones involved in this stack.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
OldMike
post Jan 16 2020, 02:35 PM
Post#5



Posts: 79
Joined: 21-July 15



Hi all, I created another Test DB from Scratch recreating all the components. I'm still getting the same behaviour as described in my first post. However I was then able to test on a different machine Windows 10/Office 2010. On this machine when I try the offending query, the machine things about it for a few seconds then responds with a Microsoft Access message 'Query is too complex', the Database doesn't close.
I'm not enough of an expert (I'm no expert at all) on SQL to comment on the complexity I'd welcome any feedback from anyone on this.
The other question that I've got is why my Office 2013 version doesn't cope with the situation in the way that my 2010 version does. I've attached my most recent test DB and if anyone is able to run it against 2013 to see what happens and feed back on anything I'd be grateful

RegardsAttached File  New_Test_DB.zip ( 20.55K )Number of downloads: 1
Go to the top of the page
 
June7
post Jan 16 2020, 07:38 PM
Post#6



Posts: 1,153
Joined: 25-January 16
From: The Great Land


Using RIGHT or LEFT instead of INNER allows query to open.

There are 7 records in tbl_Reading. Queries return 6 records. What output do you expect?

This post has been edited by June7: Jan 16 2020, 07:40 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
OldMike
post Jan 17 2020, 05:56 AM
Post#7



Posts: 79
Joined: 21-July 15



Hi June, thanks for that. Interesting, but I'm still a bit puzzled.
As mentioned in my previous post, my knowledge of SQL is very basic. Like a lot of people using Access, learnt from using the Query Designer then looking at the SQL afterward, then lots of web searches for some more detailed stuff. So I lack the holistic knowledge of eg. Selects within selects etc. within a single query. So anything requiring multiple selects I do by creating a series of queries.
So in this case qry_Readings_Joined_WithRates_withRown uses qry_Readings_Joined which uses qry_Create_RowNumber. In the test db tbl_Readings contains 7 entries, qry_Readings_Joined structures this to create 6 entries to match against tbl_Rates. With my lunderstanding of SQL I'm kind of assuming that the hard work of generating a row number to the readings and using this to match them against each other has been done. So matching against the rates and outputting the row number should be easy. I'm suppose that it's the fact that row number has been generated that causes the problem for the inner join. It would just be nice to know why, especially as per your test it works with Left/Right joins.


Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th January 2020 - 10:52 PM