Filling Table - Create Records for Gaps in Database

[MCuthill]

Greetings All,

I am trying to develop a means of "filling" in missing data from a table, and would like some guidance as to the best approach to take. I have thought of a few differing approaches, and do not know how to implement or which method is most effective for the data structured as follows:

Data Looks like (2 fields)
DATE // AMOUNT
06/01/2009 // 1.25
06/02/2009 // 1.23
06/03/2009 // 1.17
06/06/2009 // 1.05
etc

What I need to develop is a means of creating records for the "missing" dates with the value from the last date on file (create records for June 4th & June 5th with a value of 1.17 in this example).

I was thinking that if I had a table with all dates linked via an Outer Join to my data I can get null/empty Amount values for the "missing" dates and was considering something structured as follows (pseudo-code):

Do Until EoF
Variable varAmount as Double
If table.Amount <> "" then
   varAmount=table.Amount
ELSE
   Do Until table.Amount <> ""
      ~fill varAmount into Amount field~
      Next Record
   Loop
Next record
Loop

I have not worked much with loops, nor have I had to "create" data which is not in the Database and would appreciate any insight as to how to proceed.

Thanks in advance,

Mike
___________________________
There Are 10 Types of People,
Those Who Can Read Binary...
And Those Who Can't.

 

[MichaelRed]

yes, but it is just a BAD idea to begin with. DATA is data, not just pie in the sky. If you need a value for dates where there is no enttry, you can create a pseudo vlookup function which returns the value of the 'amount' from either the date (if the record exists) or the value from the previous record (sorted by Date ASC). Then you are not creating pseudo information masquerading as data.

Also has the advantage of not bloatiing the recordset with essientially redundant entries.



MichaelRed

 

[MCuthill]

Thanks for the Reply MichaelRed! =)

I agree 100% with not wanting to create redundant entries, but my rationalization is that the data would be accurate as the "DATE" is essentially a *start of* date range (the AMOUNT holds true until it changes).

Your suggestion intrigues me. I gave Google a shot and found an example using DLOOKUP, which is great for if the value exists - would you then loop this on "de-incrementing" date until an AMOUNT is discovered and populate the found AMOUNT?
Example found at this link.

The overall project is to compare actual new account data and ensure employees are using proper AMOUNTS based on Corporate standards (the data with gaps) for any given day (hence the AMOUNTS being valid until changed). My solution was to create a reference table with these three fields (plus the AMOUNT) and link on all 3 fields to the new account listing and side-by-side the AMOUNT fields.

Thanks again for your help, it is much appreciated! =)

Mike
___________________________
There Are 10 Types of People,
Those Who Can Read Binary...
And Those Who Can't.

 

[GKChesterton]

Some edits below based on idea that missing value will be found as Null, not an empty string ("").

Dim varAmount [s]as Double[/s] 'will trip on Null
    varAmount=[s]table.Amount[/s] table!Amount
Do Until table.EoF

  If not IsNull(table.Amount) then
   varAmount=table.Amount
  ELSE
   [s]Do Until table.Amount <> ""[/s]
      ~fill varAmount into Amount field~
      [s]Next Record
    Loop[/s]
  End if
Next record
Loop

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[SkipVought]

I have a PartID and a Qty in an inventory DB.

I only show a row, when it was received.

Heaven help if EVERY part/qty were repeated EVERY day that it is in inventory. Holy MACRO!!!!

Is that not what you are doing?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MCuthill]

@GKChesterton:
Thanks for the correction! =) I was only jotting the theory in pseudo-code, hadn't looked into proper syntax just yet.

@SkipVought:
Yes, that is more-or-less what I am doing. Luckily, there are not that many categories for a given day (and will get significantly smaller next week) so the dataset won't be *too* bloated.

Specifically, the database outlined above is used to dsplay current (deposit & loan) rate offerings on the corporate intranet site; I am linking this to the banking system DB to audit rates granted on new accounts and note any which vary to the posted offerings. Since an account can be opened on any day of the month, I thought the simplest solution was to have all possible rates show for every day of the month and be able to match account type and date opened.

Thanks for your help all, much appreciated!


Mike
___________________________
There Are 10 Types of People,
Those Who Can Read Binary...
And Those Who Can't.

 

[GKChesterton]

I knew it was p-code, but I was concerned about the Do-Loop!

 

[MCuthill]

GKChesteron:
Ah! Now I see! Not sure how I managed, but totally missed that the first time I read your post. Good Catch! =D I would have not thought of that as an issue.

I think I was looking at handling successive "empty" days, not realizing that they would be handled on the outer loop - and that I don't need to check for the opposite of my first conditional.

TGIF! Have a great weekend all!

Mike
___________________________
There Are 10 Types of People,
Those Who Can Read Binary...
And Those Who Can't.

 

[MichaelRed]

is 'SQL' just "Lookup" the value of the Amount and Date where the date (input arg) is less than of equal to the search date?



MichaelRed

 

[MCuthill]

Hello Everyone,

Please see below for the resulting code. Possibly not the most efficient way to acheive the end result, but it works.

Thanks to everyone who helped out with this one, it is much appreciated!

[b]Public Function Process_Rates()[/b]
Dim cSQL As String
Dim recset As Recordset
Dim currRateSchedule As String [COLOR=green]'current HEADING being processed[/color]
Dim currRateDate As Date [COLOR=green]'current DATE being processed[/color]
Dim currRate As Double [COLOR=green]'current RATE being processed[/color]
Dim minRateDate As Date [COLOR=green]'the beginning of the month prior to data.[/color]
Dim maxRateDate As Date [COLOR=green]'the Monthend date (CUVAR.TJD)[/color]
Dim recsetHEADINGS As Recordset [COLOR=green]'used to house the unique listing of HEADINGS in RATEDB[/color]
Dim recsetTOPROCESS As Recordset [COLOR=green]'used to house Rates for specific Rate Schedule, including Missing Dates[/color]

[COLOR=green]'Identify current monthend date and store to variable[/color]
cSQL = "SELECT CUVAR.INST_NAME, CUVAR.TJD, CUVAR.ZZA FROM CUVAR;"
Set recset = CurrentDb.OpenRecordset(cSQL)
recset.MoveFirst
maxRateDate = recset("TJD")

[COLOR=green]'Identify 1st of Month Prior to Data.[/color]
minRateDate = DateAdd("m", -2, maxRateDate) + 1

[COLOR=green]'Create listing of rates, starting with 1st of month prior to data cut[/color]
cSQL = "SELECT dbo_RATETBL.HEADING, dbo_RATETBL.ID, dbo_RATETBL.RATEDATE, dbo_RATETBL.RATE " & _
        "FROM dbo_RATETBL " & _
        "WHERE (((dbo_RATETBL.HEADING)<>'') AND ((dbo_RATETBL.RATEDATE)>=#" & minRateDate & "#)) " & _
        "ORDER BY dbo_RATETBL.HEADING, dbo_RATETBL.RATEDATE;"
CurrentDb.QueryDefs("qry_ALLRATES_Heading-RateDate-Ascend").SQL = cSQL

[COLOR=green]'Capture listing of unique HEADINGS from dbo_RATES[/color]
cSQL = "SELECT [qry_ALLRATES_Heading-RateDate-Ascend].HEADING " & _
        "FROM [qry_ALLRATES_Heading-RateDate-Ascend] " & _
        "GROUP BY [qry_ALLRATES_Heading-RateDate-Ascend].HEADING " & _
        "ORDER BY [qry_ALLRATES_Heading-RateDate-Ascend].HEADING;"
Set recsetHEADINGS = CurrentDb.OpenRecordset(cSQL)
recsetHEADINGS.MoveFirst

[COLOR=green]'For each HEADING (Rate Schedule), process rates to fill non-saved dates[/color]
Do Until recsetHEADINGS.EOF
    currRateSchedule = recsetHEADINGS("HEADING")
    
    [COLOR=green]'Move Entries for Current Rate Schedule to Temporary Table[/color]
    cSQL = "INSERT INTO TEMPORARY_RATES ( HEADING, ID, RATEDATE, RATE ) " & _
            "SELECT [qry_ALLRATES_Heading-RateDate-Ascend].HEADING, [qry_ALLRATES_Heading-RateDate-Ascend].ID, [qry_ALLRATES_Heading-RateDate-Ascend].RATEDATE, [qry_ALLRATES_Heading-RateDate-Ascend].RATE " & _
            "FROM [qry_ALLRATES_Heading-RateDate-Ascend] " & _
            "WHERE ((([qry_ALLRATES_Heading-RateDate-Ascend].HEADING)='" & currRateSchedule & "'))"
    CurrentDb.QueryDefs("qry_Move-RATES-to-TemporaryTable").SQL = cSQL
    CurrentDb.QueryDefs("qry_Move-RATES-to-TemporaryTable").Execute
        
    [COLOR=green]'Create RecordSet including Empty Rows for Missing Dates[/color]
    cSQL = "SELECT '" & currRateSchedule & "' AS HEADING, Calendar_2009to2018.Date AS RATEDATE, TEMPORARY_RATES.RATE " & _
            "FROM Calendar_2009to2018 LEFT JOIN TEMPORARY_RATES ON Calendar_2009to2018.Date = TEMPORARY_RATES.RATEDATE " & _
            "WHERE (((Calendar_2009to2018.Date)>=#" & minRateDate & "#)AND((Calendar_2009to2018.Date)<=#" & maxRateDate & "#)) " & _
            "ORDER BY '" & currRateSchedule & "', Calendar_2009to2018.Date"
    Set recsetTOPROCESS = CurrentDb.OpenRecordset(cSQL)
    recsetTOPROCESS.MoveFirst
    
    [COLOR=green]'Catch - if first rate is empty, move to next date[/color]
    If Trim(recsetTOPROCESS("RATE") & "") = "" Then
        recsetTOPROCESS.MoveNext
    End If
    
    Do Until recsetTOPROCESS.EOF
        If Trim(recsetTOPROCESS("RATE") & "") <> "" Then
            currRate = recsetTOPROCESS("RATE")
        End If
        
        currRateDate = recsetTOPROCESS("RATEDATE")
        cSQL = "INSERT INTO RATES ( HEADING, RATEDATE, RATE ) " & _
            "SELECT '" & currRateSchedule & "' AS HEADING, #" & currRateDate & "# AS RATEDATE, " & currRate & " AS RATE"
        CurrentDb.Execute (cSQL)
        
        recsetTOPROCESS.MoveNext
    Loop
       
    [COLOR=green]'Empty Temporary Table before processing next Rate Schedule[/color]
    cSQL = "DELETE * FROM TEMPORARY_RATES"
    CurrentDb.Execute (cSQL)
    
    recsetHEADINGS.MoveNext
Loop
[b]End Function[/b]

Mike
___________________________
There Are 10 Types of People,
Those Who Can Read Binary...
And Those Who Can't.