Back-filling data in a query?

[TomRenish]

Hi there,

I'm trying to generate a file that helps me figure out how many widgets were in the field as a function of days since deployment. Here's an example:

[Days since shipment] [Running Total]

1 / 234
2 / 230
3 / 220
4
5
6 / 210

Nothing shipped 4 days ago or 5 days ago, for example. But the number of widgets in the field for days 4 and 5 will be identical to 6, so I need a way to push "210" into those particular records. There are hundreds of records like this and the units shipped "x" days ago will will increment with each passing day.

I mucked around with DFirst, DMin, etc., but I couldn't ever get it to work correctly.

 

[Joe4]

Access is not like Excel, so the "order" of records within a table/query really doesn't have much meaning. So that makes tasks like these a bit more difficult (where it would be easy in Excel).

Are you records appearing like you have posted in the underlying table (blanks for certain days)? If so, I would go about this a bit differently. I would fill in the missing days with VBA. I would create a query that sorts the recordset by Days, then loop through that recordset in VBA (DAO or ADO Recordset), filling in the missing values so that they are stored on the underlying table. Within your loop, you can compare the current value to the previous one.

 

[xenou]

I think I'd also script this. I found this correlated query works but not if the running total is missing for two or more days - though running it several times in a row should catch everything up. BTW I suspect it would to fill in a missing day 1 - which is just how it goes (there being no day zero to compare it to).

[COLOR="Navy"]UPDATE [/COLOR]Table1 t1 
[COLOR="Navy"]INNER JOIN [/COLOR]
Table1 t2 
[COLOR="Navy"]ON [/COLOR](t1.[Days Since Shipment]) = (t2.[Days Since Shipment] -  1) 
[COLOR="Navy"]SET [/COLOR]t1.[Running Total] = t2.[Running Total]
[COLOR="Navy"]WHERE [/COLOR]t1.[Running Total] [COLOR="Navy"]Is Null[/COLOR];

 

[Joe4]

That's a pretty clever way of doing that!

 

[TomRenish]

Thanks for the replies, everyone.

Since I don't know the first thing about modifying record sets in Access VBA, I may just do it in Python.

 

[Joe4]

Did you try xenou's suggestion?
It doesn't use VBA or recordsets, just an Update Query.

 

[TomRenish]

I thought about that, but I have a few records that go several weeks between subsequent shipments. I didn't want to run a query several dozen times to fill in data.

After reading about record set edits, I used "teh google!" to find examples of this practice. It looks pretty straightforward, so that will be my solution.

Thanks again!

 

[Joe4]

Yep, you can find just about anything with Google.

Editing Recordsets using VBA actually is not bad at all, especially if you have any programming experience (in VBA or otherwise).

 

[TomRenish]

I got mad and decided that "DFirst" must work the way I thought it should, so I tried again...

this query is called "qry_Running_Totals"


[Day#] [Temp Data] [Running Total]

1 / 234 / 234
2 / 230 / 230
3 / 220 / 220
4 / (blank) / 210
5 / (blank) / 210
6 / 210 / 210

[Day#] is read from a table
[Temp Data] is also read from a table

The secret sauce is [Running Total]

Running Total: IIf(IsNull([Temp Data]),DFirst("[Temp Data]","[qry_Running_Totals]","[Day#] > " & [Day#] & " And Not IsNull([Temp Data])"),[Temp Data])

The criteria portion of DFirst (and all other domain functions in Access) is really weird to work with. You have to be very careful about what gets wrapped in quotes and what doesn't get wrapped. This is what was causing my query to continually fail.

 

[xenou]

I can honestly say I've never used DFirst in my life - that's an interesting example! Good to see it.