Results 1 to 6 of 6
  1. #1
    valglad is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    5

    Missing dates

    Hi,



    If anyone could help - that'd be really great.

    Here is the situation:

    I have two tables:

    Table1:

    AccNum--------------AccCurr----------------------AMT--------------BDate
    112344----------------USD-------------------------150---------------2/28/2016
    112344----------------USD-------------------------160---------------3/1/2016
    112344----------------USD-------------------------170---------------12/1/2016
    112344----------------USD-------------------------175---------------12/26/2016

    Table2.
    6/1/2016
    6/2/2016
    6/3/2016
    6/4/2016
    6/5/2016
    6/6/2016
    6/7/2016
    6/8/2016
    6/9/2016
    6/10/2016
    ....till today

    Basically, I need to create a query which would have existing records from Table1 and new records where dates in Table1 are missing so they would have the value from a day before. The desired result would look like



    AccNum--------------AccCurr----------------------AMT--------------BDate
    112344----------------USD-------------------------150---------------2/28/2016
    112344----------------USD-------------------------160---------------3/1/2016
    112344----------------USD-------------------------160---------------3/2/2016
    112344----------------USD-------------------------160---------------3/3/2016
    .................................................. ................................................
    112344----------------USD-------------------------160---------------11/30/2016
    112344----------------USD-------------------------170---------------12/1/2016
    112344----------------USD-------------------------170---------------12/2/2016
    112344----------------USD-------------------------170---------------12/3/2016
    .................................................. .......................................
    112344----------------USD-------------------------170---------------12/25/2016
    112344----------------USD-------------------------175---------------12/26/2016

    Thank you

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to use a subquery something like

    Code:
    SELECT *
    FROM Table2, Table1
    WHERE Table1.BDate=(SELECT Max(BDate) FROM Table1 T WHERE Accnum=Table1.Accnum AND BDate<=Table2.DateField)
    If you have a lot of data, ensure BDate and DateField are indexed.

  3. #3
    valglad is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    5
    Thank you, it works! I did index the tables, but it still takes too long. Is there any way, I can improve the performance?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can try an alternative subquery

    instead of

    (SELECT Max(BDate) FROM Table1 T WHERE Accnum=Table1.Accnum AND BDate<=Table2.DateField)

    try

    (SELECT TOP 1 BDate FROM Table1 T WHERE Accnum=Table1.Accnum AND BDate<=Table2.DateField ORDER BY BDate Desc)


    Otherwise only by reducing the number of records to be returned - date range, account number.

    This is what is called a correlated query - each row has to run the subquery because the criteria is based on a value in the main query. The alternative would be to use a domain function but they are even slower.

    But also take a look at this link https://www.experts-exchange.com/art...Functions.html

    Find the bit about DSeek - no idea if it would be quicker - you'll need to experiment. Sometimes using a UDF is better than a subquery

    Will be interested to know whether the alternative sub query is faster or slower and how you get on with DSeek

  5. #5
    valglad is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    5
    Hi,

    I timed both sub-queries and the results are kind of mixed. When I run the first one vs the latest one in Access - the Select TOP 1 wins by a mile.

    But the way I do it - I assemble a Union query SQL in Excel, where the date range parameter is entered and write it Access using ADO every time either parameters change or the new data has been added to the database. A pivot table in Excel that is based on the union query is refreshed automatically.

    So, when I timed this whole process - writing SQL statement into Access and refreshing the pivot - the Select MAX subquery took 94 seconds and the Select TOP 1 took 162 sec. The union has four queries and the one you've been helping me with is the last one, so the actual time for it is much shorter.

    I looked into DSeek function but didn't understand how to use it for my purposes.

    Thank you Ajax for your help. I do appreciate it.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Union queries often imply a poorly designed db - typically not normalised, but sometimes they are required. You mention Excel, Excel is a good example of laying our data in an unnormalised way - but it is designed to work that way. Access data needs to be normalised to work well, so if you are treating it as a larger excel, that might be a reason for poor performance. It does suggest you look at your table design in light of what you are doing, you may be able to make improvements.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Missing Dates - Running Balance
    By valglad in forum Access
    Replies: 2
    Last Post: 03-10-2017, 12:20 PM
  2. Need to query for missing dates
    By Jaron in forum Queries
    Replies: 5
    Last Post: 09-11-2013, 11:58 AM
  3. Combing two tables with missing dates
    By Jerseynjphillypa in forum Queries
    Replies: 1
    Last Post: 07-19-2012, 10:51 AM
  4. Appending Missing Dates
    By Jerseynjphillypa in forum Queries
    Replies: 3
    Last Post: 05-25-2012, 09:44 PM
  5. Report missing ship dates and other info...
    By adam1986 in forum Access
    Replies: 8
    Last Post: 08-25-2011, 01:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums