473,320 Members | 2,097 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Repeat the last record under certain conditions in a table or a query

Hello,

I’d like to repeat the last not = 0 record under certain conditions, in a table or in a query.

What I have:
Calendar_All Dates
Calendar date MyValue
7/6/2014 0.00
7/7/2014 108.94
7/8/2014 107.71
7/9/2014 107.07
7/10/2014 105.89
7/11/2014 105.30
7/12/2014 0.00
7/13/2014 0.00
7/14/2014 104.69
7/15/2014 0.00
7/16/2014 0.00
7/17/2014 0.00
7/18/2014 0.00
7/19/2014 0.00
7/20/2014 0.00
7/21/2014 0.00
7/22/2014 0.00
7/23/2014 0.00

What I want:
Calendar_All Dates
Calendar date MyValue
7/6/2014 0.00
7/7/2014 108.94
7/8/2014 107.71
7/9/2014 107.07
7/10/2014 105.89
7/11/2014 105.30
7/12/2014 0.00
7/13/2014 0.00
7/14/2014 104.69
7/15/2014 104.69
7/16/2014 104.69
7/17/2014 104.69
7/18/2014 104.69
7/19/2014 0.00
7/20/2014 0.00
7/21/2014 104.69
7/22/2014 104.69
7/23/2014 104.69

Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calendar date<>1 and <>7), AND Calendar Date not in (Holidays table)
Then repeat the last not = 0 value of MyValue


The idea is clear but I can’t find the right way to write it.. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.
Thanks a lot for your help!

Ben
Jul 15 '14 #1
8 1109
twinnyfo
3,653 Expert Mod 2GB
Ben,

I htink you have much of the basics to do what you want. How fluent are you in VBA and using recordsets? This would allow you to examine multiple aspects of each record.

There may be a way to do this with just a SQL SELECT Statement, but it would probably make my head hurt more than I care to guess.
Jul 15 '14 #2
Hi,

I'm starting to understand how to use them. I think I would be better to do this using VBA as well.
What are you thinking about?

Ben
Jul 15 '14 #3
twinnyfo
3,653 Expert Mod 2GB
Without writing your code for you, I would begin by opening a recordset based upon your table, sorted by date. Have a variable that slooks at the value of MyValue for the first record. Then cycle through records 2 - x. If the MyValue for that record is not 0, then you update the variable to reflect the new value. Then, if the date of the current record meets all your criteria, and is also 0, then update that record to reflect the value in your variable.

Relatively straightforward, and we will be glad to help you through any hitches you come across. We'd like to see what you come up with first.
Jul 15 '14 #4
I'd do something like:

Expand|Select|Wrap|Line Numbers
  1. Public Sub GetInterestingValue()
  2.  
  3. Dim x As Double
  4. Dim recordSetDate As DAO.Recordset
  5. Set recordSetDate = CurrentDb.OpenRecordset("Calendar_All Dates")
  6.  
  7. 'I need to like "scan" all the records, which I don't know how to do, for recordSetDate in [Calendar_All Dates], while maybe?
  8.  
  9. If recordSetDate("MyValue").Value <> 0 
  10.  
  11. '{This will be for later: AND Weekday(recordSetDate("Calendar Date").Value) Between 2 and 6 AND recordSetDate("Calendar Date") is in [UK_Holidays]}
  12.  
  13. then 
  14. x=recordSetDate("MyValue").Value
  15.  
  16. 'Go scan Next one
  17.  
  18. End Sub
  19.  
  20.  
  21. So it should return the last value with the goo syntax right?
  22.  
  23. After I see something like 
  24.  
  25. Public Sub FillTheTable()
  26.  
  27. Dim recordSetDate As DAO.Recordset
  28. Set recordSetDate = CurrentDb.OpenRecordset("Calendar_All Dates")
  29.  
  30. 'I need again to like "scan" all the records from the last corresponding to the x I got earlier to the last of the table, which I don't know how to do, for recordSetDate in [Calendar_All Dates], while maybe?
  31.  
  32. For recordSetDate("Calendar Date") > {Calendar Date corresponding to the x calculated earlier} 
  33.  
  34. If Weekday(recordSetDate("Calendar Date").Value) Between 2 and 6 AND recordSetDate("Calendar Date") is not in [UK_Holidays]
  35.  
  36. then 
  37. recordSetDate("MyValue").Value=x
  38.  
  39. 'Go scan Next one
  40.  
  41. End Sub
Jul 15 '14 #5
x is not global I guess, I should call GetInterestingValue() and store it as x at the beginning of the Second sub
Jul 15 '14 #6
twinnyfo
3,653 Expert Mod 2GB
Good first trial for a rookie(?). Let's use what you have so far and work on it:

P.S. Please use Code Tags when posting code.

Expand|Select|Wrap|Line Numbers
  1. Public Sub GetInterestingValue()
  2.     Dim dblCurrentValue As Double
  3.     Dim strSQL As String
  4.     Dim recordSetDate As DAO.Recordset
  5.  
  6.     'We want to make sure we sort by the date:
  7.     strSQL = "SELECT * FROM [Calendar_All Dates] " & _
  8.         "ORDER BY [Calendar Date];"
  9.     'You may notice the challenge of having spaces in your
  10.     'field names here--try to avoid in the future
  11.  
  12.     Set recordSetDate = CurrentDb.OpenRecordset(strSQL)
  13.  
  14.     'This should return all records in the Table
  15.     'But if something goes wrong, we want to make sure
  16.     'there are records before we start:
  17.     If Not recordSetDate.RecordCount = 0 Then
  18.         'First find out what the MyValue is
  19.         recordSetDate.MoveFirst
  20.         dblCurrentValue = recordSetDate!MyValue
  21.         recordSetDate.MoveNext
  22.         Do While Not recordSetDate.EOF
  23.             'Perform your data validations here
  24.             'for weekdays and holidays
  25.  
  26.  
  27.             'Update your Current Value here (when needed):
  28.             dblCurrentValue = recordSetDate!MyValue
  29.  
  30.             'If you need to assign the value of the MyValue
  31.             'This will probably be nested in an If...Then
  32.             With recordSetDate
  33.                 .Edit
  34.                 !MyValue = dblCurrentValue
  35.                 .Update
  36.             End With
  37.         Loop
  38.     End If
  39. End Sub
You were not far off, but I htink it's a lack of experience. We will be glad to help you through the rest of the process as you trudge along--we've all been there at some point in our lives.
Jul 15 '14 #7
This is just beautiful, thanks a lot for your help.
I will try to write in properly tomorrow and I will tell you about.

Many thanks again
Jul 15 '14 #8
Rabbit
12,516 Expert Mod 8TB
There is a SQL only solution but it's convoluted to say the least.

Please note this solution was created and tested in SQL Server, some minor syntax changes would be needed to make it work in Access:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     y.dt,
  3.     z.amt
  4.  
  5. FROM 
  6.     (
  7.         SELECT 
  8.             t1.dt,
  9.             MAX(COALESCE(t2.dt, t1.dt)) AS mdt
  10.  
  11.         FROM 
  12.             #t AS t1
  13.  
  14.             LEFT JOIN #t AS t2
  15.             ON    t1.dt > t2.dt AND
  16.                 DATEPART(WEEKDAY, t1.dt) BETWEEN 2 AND 6 AND
  17.                 DATEPART(WEEKDAY, t2.dt) BETWEEN 2 AND 6 AND
  18.                 t1.amt = 0 AND
  19.                 t2.amt <> 0
  20.  
  21.         GROUP BY
  22.             t1.dt
  23.     ) x
  24.  
  25.     INNER JOIN #t AS y
  26.     ON    x.dt = y.dt
  27.  
  28.     INNER JOIN #t AS z
  29.     ON    x.mdt = z.dt
  30.  
  31. ORDER BY
  32.     y.dt
Basically you have to reference the table 4 times to make it work.
Jul 15 '14 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Daniel Ng | last post by:
Thanks for the 2 guys how hep to to solve to count the number of recount in forum. I'm done wif that. Got 1 more question.. Example Record 1: ID 2 VALUE 2 Record 2: ID 3 VALUE 3 Record 3: ID 2...
4
by: Lucius | last post by:
Hello everyone, I have a query problem. I'll put it like this. There is a 'publishers' table, and there is a 'titles' table. Publishers publish titles (of course). Now I want to make a query (in...
1
by: travismorien | last post by:
I have four tables of different "entities". One table contains information for "people", one for "trusts", one for "companies" and one for "self managed super funds". Each type of entity has an...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
2
by: pranjalraje | last post by:
can you please help me with sql querry for 'finding last record in a table'
1
by: chris | last post by:
I have a Switchboard I created that has the program version at the bottom. I have a table with version history in it that I maintain everytime I make changes and release it to the users. ...
2
by: sheenaa | last post by:
Hi, I want to display the last recorded record in the database of SQL SERVER 2005. How can i display it with the select query in the grid view... The database contains the...
1
by: JohnG1234 | last post by:
I am new to SQL and just started making reports in reporting services, need help on how to write a query in SSRS 2005 dataset Here is an example Table A (3 columns Date , Limit, Product) ...
1
by: psyvanz | last post by:
here is the code im working with... hope you can understand? cause this thing searches the first one, not the last one record... i wanted ONLY to find the last record in a table... Please...
8
by: PAuser | last post by:
Greetings, I`m having a curious situation in MS Access 2003 while editing records in a cross table query. Tables are: Table1 (field1_1, field1_2, field1_3) Table2 (field2_1, field2_2,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.