Simple Expression

[andrew]

I am trying to display records that have been created within the last
24 hours of the last transaction/record in my database.

For example...how many records have been created in the last 24 hours
etc.


Now, my timestamp is in milliseconds, so I guess I want to get the Max
value, and find out how many whatevers have been created between that
value and that value minus 86400000 milliseconds (1 day).

Trouble is, I am horrible at Access and don't know how to do this.

So, if I wanted the average value of the number in a column that have
been created in the last 24 hours since the most recent entry...how
would I do this?

=Avg (.................?



I know this is simple...but I'm alllllllllllmost at the point where I
can do this myself!


Help!

 

[Ken Sheridan]

If your TimeStamp column is a date/time data type you just need to subtract 1
from it as date/time values are implemented as a floating point number in
which the integer part represents days and the fractional part the times of
day:

SELECT AVG(SomeColumn)
FROM SomeTable
WHERE TimeStamp >
(SELECT MAX(TimeStamp)
FROM SomeTable) – 1;

 

[andrew]

Thanks! I should be able to figure it out from here!

The timestamp column isn't actually recognized or formatted in access
as a timestamp, it's actually being read off of our Oracle database.
And to the best of my knowledge....Access doesn't deal at all with time
as milliseconds (since 1970?) ? That's what I've been told...

So I guess because I'm dealing with a range I need to stick an AND in
there?


Thanks so much!!

 

[andrew]

Thanks! I should be able to figure it out from here!

The timestamp column isn't actually recognized or formatted in access
as a timestamp, it's actually being read off of our Oracle database.
And to the best of my knowledge....Access doesn't deal at all with time
as milliseconds (since 1970?) ? That's what I've been told...

So I guess because I'm dealing with a range I need to stick an AND in
there?


Thanks so much!!

 

[andrew]

Thanks! I should be able to figure it out from here!

The timestamp column isn't actually recognized or formatted in access
as a timestamp, it's actually being read off of our Oracle database.
And to the best of my knowledge....Access doesn't deal at all with time
as milliseconds (since 1970?) ? That's what I've been told...

So I guess because I'm dealing with a range I need to stick an AND in
there?


Thanks so much!!

 

[andrew]

Actually, I suck... here is my super-lame attempt at creating an
expression in Access to do what I needed.... but the syntax is wrong
and I'm still in over my head....

= Avg ( [Transaction1info]![GROSSAMOUNT] )
WHERE ( [Transaction1info]![TIMESTAMP] )
< ( Max ( [Transaction1info]![TIMESTAMP] ) )
AND

 

[Douglas J Steele]

The Date/Time data type in Access is an 8 byte floating point number where
the integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day.

Today (29 Nov, 2005) is 38685. 6:00 AM this morning was 38685.25, Noon today
was 38685.5 and 6:00 PM will be 38685.75:

?Format(38685, "dd mmm, yyyy")
29 Nov, 2005
?Format(38685.25, "dd mmm, yyyy hh:nn:ss")
29 Nov, 2005 06:00:00
?Format(38685.5, "dd mmm, yyyy hh:nn:ss")
29 Nov, 2005 12:00:00
?Format(38685.75, "dd mmm, yyyy hh:nn:ss")
29 Nov, 2005 18:00:00

 

[andrew]

The timestamp I am getting is coming from our Oracle database, so the
timestamp is in this form: 1133232905757
So far I haven't been able to really easily convert this into a
day/date that easily (simple in Java though).

So the workaround I'm trying to do is to just say "calculate the
average of SOMECOLUMN when looking at the range between the Max
TIMESTAMP and the TIMESTAMP - 86400000 milliseconds (24 hours)"

But I'm not having luck creating the expression to do this...my most
recent feeble attempt is as follows:


=Avg([GROSSAMOUNT])
WHERE
([TIMESTAMP]) < (Max ([TIMESTAMP]))
AND > (Max ([TIMESTAMP] - 86400000))

 

[John Vinson]

The timestamp I am getting is coming from our Oracle database, so the
timestamp is in this form: 1133232905757

ok... you're in luck for a few years, that's still within the range of
a Long Integer.

So far I haven't been able to really easily convert this into a
day/date that easily (simple in Java though).

DateAdd("s", [Timestamp] \ 1000, #1/1/1970#)

So the workaround I'm trying to do is to just say "calculate the
average of SOMECOLUMN when looking at the range between the Max
TIMESTAMP and the TIMESTAMP - 86400000 milliseconds (24 hours)"

SELECT Avg([GROSSAMOUNT]) AS ThisIsGrossOnAverage FROM tablename
WHERE [Timestamp] > (SELECT Max(X.[timestamp])-86400000 FROM tablename
AS X)

in the SQL window.


John W. Vinson[MVP]

 

[Ken Sheridan]

Regardless of how Oracle implements date/time data you'll have to use a
subquery in the WHERE clause of an outer query. You don't need to specify a
range as the top of the range is the row with the latest TIMESTAMP column
value so all you need to look for are rows after this less one day.
Apparently an Oracle TIMESTAMP data type is a variable data type, from 7 to
11 bytes, with the last 4 bytes representing fractions of a second down to a
maximum precision of one nanosecond. As far as I can see from my limited
reading on Oracle DATE and TIMESTAMP data types the interval between two
values is, when simple arithmetic is used, (as in Access) returned in days,
from which I'd imagine that when querying Oracle data in Access you can
simply subtract 1 to give a value one day earlier, so the query I gave you
should work once you've substituted the correct column name for the TIMESTAMP
column. To reiterate it would go like this in Access:

SELECT AVG(GROSSAMOUNT)
FROM Transaction1info
WHERE YourTimeStampColumn >
(SELECT MAX(YourTimeStampColumn)
FROM Transaction1info) – 1;

Oracle provides functions for doing date arithmetic, so in Oracle itself the
above query could I think go something like this:

SELECT AVG(GROSSAMOUNT)
FROM Transaction1info
WHERE YourTimeStampColumn >
(SELECT MAX(YourTimeStampColumn)
FROM Transaction1info) – NUMTODSINTERVAL(1, 'DAY');

A VBA equivalent of this using domain aggregate functions would be:

=DAvg("GROSSAMOUNT", "Transaction1info", "YourTimeStampColumn > " &
DMax("YourTimeStampColumn", "Transaction1info") -1)

Actually, I suck... here is my super-lame attempt at creating an
expression in Access to do what I needed.... but the syntax is wrong
and I'm still in over my head....

= Avg ( [Transaction1info]![GROSSAMOUNT] )
WHERE ( [Transaction1info]![TIMESTAMP] )
< ( Max ( [Transaction1info]![TIMESTAMP] ) )
AND

(( [Transaction1info]![OBOOWN_TRANSACTIONEVENT1.ID] ) - (86400000));