table to calculate past information with current?

[wichitawx]

Hi


I'm looking to create a column that will do some calculation, but it will need to compare it with some past data is this possible?
specifically I need to choose which is the bigger value. here is the formula that I would use in excel (see attached photo of what I'm looking for)

=MAX(value2 - value3, value2-(prior day Value4), (prior day Value4) - value 3)


(sorry in the photo it should NOT be value 1 - value 2 .... but instead value 2 - value 3)

Is this possible to do in Access?

[Micron]

I'm not sure I follow. For the first record, there is no prior day so any portion that involves a "prior day" before 1/12/01 would result in Null. Therefore 46-43 would be the only calculation. Looking at the second record, the calculation would be Max of 50-45, 50-44, 44-45; the answer being 6. What you probably need to look at is subqueries, though this one would be somewhat complex, methinks.

Almost always a code solution is also possible. If you wanted only the short answer, I should have just said "yes".

[ArviLaanemets]

On fly:

=MAX(value2 - value3, (value2-DLookup("Value4","YourTable","Date = #" & DMax("Date","YourTable", "Date < #" & [Date] & "#" ) & "#")), (DLookup("Value4","YourTable","Date = #" & DMax("Date","YourTable", "Date < #" & [Date] & "#" ) & "#") - value 3))

But probably this returns error for 1st row too as Micron mentioned. Without Access available at weekend, I haven't tools to check the formula and possible workarounds.

And probably the best solution will be an UDF designed specially for this case - there you can have solution for 1st row exactly in a manner you want.

[CJ_London]

you also need to define the relationship with past data - the previous day? what if there is a gap between yesterday and today? what if there are two records today and/or yesterday?

[wichitawx]

Thanks everyone for your quick response
@micron the first data I would skip doing the max calculation. And start on the 2nd data.

@arvilLaanemets, thanks I'll try that out. I'm relatively a newbie with access, but will research and look into UDF.

@ajax, I'm only entering data once a day, there will be days that there is no data (like holidays) and I would skip entering data for those days and only continue on days there is data.

[CJ_London]

In that case a sub query is the way to go rather than a domain function. The subquery Would be something like

(Select top 1 myfield from mytable T where mydate<mytable.mydate order by mydate desc) as prevvalue

Note the T alias

Sent from iPhone so please excuse typos

[Dehn0045]

You will find that things that are super easy in Excel become quite difficult in Access. Remember that in Access the location of the record in the table is usually not meaningful. Assume that the records in your table are in a totally random order and then write your code accordingly. Avoiding bugs can be difficult. For example, what if there are multiple records with the same date, what if somebody puts in a future date, or enters the wrong year on a record...

[Micron]

Assume that the records in your table are in a totally random order

This can usually be overcome by basing something (form, report, recordset, query, etc.) on an ordered query instead of a table. A query can impose a logical order as long as the fields providing the Order By are suitable.