get last value from query (1 Viewer)

cpampas

Registered User.
Local time
Yesterday, 22:48
Joined
Jul 23, 2012
Messages
218
Hello ,
I wonder if it is posible in case the value in a query is null to assume by default the previous value. this is how the output of my query looks like :

idjogoPlapladataScrapPinnacleWmarathonW1xbetW
7594Genk - Club Bruges27/02/2020 02:30:504.033.943.90
7989Genk - Club Bruges27/02/2020 03:30:494.023.933.90
8403Genk - Club Bruges27/02/2020 04:30:504.033.943.88
8687Genk - Club Bruges27/02/2020 04:39:093.92
8847Genk - Club Bruges27/02/2020 05:30:504.043.903.90
9131Genk - Club Bruges27/02/2020 05:39:123.954.00
9293Genk - Club Bruges27/02/2020 06:30:514.033.943.90

so, the 2 blanks in record number 8687, would be filled with 4.03 and 3.88
and in record 9131 I would get the value 4.04

Code:
SELECT tblTempGraf.id, tblTempGraf.jogoPla, tblTempGraf.pladataScrap, tblTempGraf.PinnacleW, tblTempGraf.marathonW, tblTempGraf.[1xbetW]
FROM tblTempGraf
WHERE (((tblTempGraf.jogoPla)="Genk - Club Bruges"))
ORDER BY tblTempGraf.pladataScrap;


I ve been trying with dlookup, but perhaps that is not the right way
Any toughts ?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:48
Joined
Oct 29, 2018
Messages
21,358
Hi. That should be possible using a subquery. Can you post a sample db for testing?
 

ebs17

Well-known member
Local time
Today, 06:48
Joined
Feb 7, 2020
Messages
1,882
How does it look?
SQL:
SELECT 
   T..id, 
   T.jogoPla, 
   T.pladataScrap, 
   IIF(T.PinnacleW IS NULL, 
   (
      SELECT TOP 1 
         X.PinnacleW 
      FROM 
         tblTempGraf AS X 
      WHERE 
         X.jogoPla = "Genk - Club Bruges" 
            AND 
         X.pladataScrap < T.pladataScrap 
      ORDER BY 
         X.pladataScrap DESC 
   )
   , T.PinnacleW) AS PinnacleW, 
   T.marathonW, 
   IIF(T.[1xbetW] IS NULL, 
   (
      SELECT TOP 1 
         X.[1xbetW] 
      FROM 
         tblTempGraf AS X 
      WHERE 
         X.jogoPla = "Genk - Club Bruges" 
            AND 
         X.pladataScrap < T.pladataScrap 
      ORDER BY 
         X.pladataScrap DESC 
   )
   , T.[1xbetW]) AS [1xbetW] 
FROM 
   tblTempGraf AS T 
WHERE 
   T.jogoPla = "Genk - Club Bruges" 
ORDER BY 
   T.pladataScrap

Eberhard
 
Last edited:

cpampas

Registered User.
Local time
Yesterday, 22:48
Joined
Jul 23, 2012
Messages
218
your query works great , except for the query field "marathonw"
 

Attachments

  • fillBlanks.zip
    384.1 KB · Views: 75

plog

Banishment Pending
Local time
Today, 00:48
Joined
May 11, 2011
Messages
11,613
Your table is not properly structured and you should focus on fixing that prior to tackling this. A correct table structure either avoids this or presents this issue in a different manner requiring a different solution.

You have groups of fields in that table:

1xbetW, skybetW, betfairW, dafaBetW...
1xbetD, skybetD, betfairD, dafaBetD...
1xbetL, skybetL, betfairL, dafaBetL...
...

When you have fields like that it requires at least one new table. Google "database normalization", read up on it, and practice few tutorials.

ebs's solution fails because of consecutive blanks in your data. Only the field [marathonw] exhibits this in the data set you have, the other fields would fail as well under this condition. I believe an additional criteria in the subqueries' WHERE fields could accomodate this.
 

cpampas

Registered User.
Local time
Yesterday, 22:48
Joined
Jul 23, 2012
Messages
218
Hi Plog,
I understand that my table is not correctly designed, actually I changed its structure in order to split my data into columns so that I could define the source data for a line chart in excel. Originaly my table was structured like this :

idjogoPlapladataScrapwindrawlosebookieIDwBookie
7594Augsburg - Werder Bremen27/02/2020 02:302.753.252.5537Pinnacle
7989Augsburg - Werder Bremen27/02/2020 03:302.753.402.6038marathon

so, from the "normalized" table I created another one just to feed the line chart in excel, because the series I believe should be in columns
It s unbelivable the amount of time I ve spent just to create a chart with 4 series of data , either in excel ( from access), or within access , and until now with no result.

As I am abandoning the idea of passing data from access to excel to generate a line chart ( 4 lines), do you know of any examples that can relate to this case
Thanks for your help
 

Users who are viewing this thread

Top Bottom