Looking for most recent value other than today's. Question

[tcek]

I have numerous values (a,b,......) that are associated with a specific date
and time. I would like to compare the value obtained today against the most
recent date other than today. i am looking for a 50% changed in values
between the most recent (today) and the previous value. thanks in advance

 

[KARL DEWEY]

Try this substituting you table and field names --
SELECT TOP 1 [Change Requests_1].[Date open], [Change Requests_1].x
FROM [Change Requests], [Change Requests] AS [Change Requests_1]
WHERE (((CDate([Change Requests].[Date open]))=Date()) AND (([Change
Requests_1].x)>=[Change Requests].[x]*1.5 Or ([Change Requests_1].x)<=[Change
Requests].[x]*0.5))
ORDER BY [Change Requests_1].[Date open] DESC;

 

[tcek]

I assume [Change Requests] and [Change Request_1] are two seperate tables. If
so can this be run with just one table [Change Requests]? I assume [Date
open] is another table? What if the date is in you original and only table.

All my data is in one table.

Try this substituting you table and field names --
SELECT TOP 1 [Change Requests_1].[Date open], [Change Requests_1].x
FROM [Change Requests], [Change Requests] AS [Change Requests_1]
WHERE (((CDate([Change Requests].[Date open]))=Date()) AND (([Change
Requests_1].x)>=[Change Requests].[x]*1.5 Or ([Change Requests_1].x)<=[Change
Requests].[x]*0.5))
ORDER BY [Change Requests_1].[Date open] DESC;

--
KARL DEWEY
Build a little - Test a little

I have numerous values (a,b,......) that are associated with a specific date
and time. I would like to compare the value obtained today against the most
recent date other than today. i am looking for a 50% changed in values
between the most recent (today) and the previous value. thanks in advance

 

[KARL DEWEY]

[Change Requests_1] is the second instance of table [Change Requests] as
Access adds a sufix to the table name when you place a table in the design
view window.

[Date open] is in the one table.

Create a new query in design view, placing your table in the window twice.
Drag YourDateField and ValueField to the FIELD: row from YourTable_1.
In the criteria row under the ValueField enter --

=[YourTable].[ValueField]*1.5 Or <=[YourTable].[ValueField]*0.5

Next edit a calculated field using the YourDateField of YourTable to read
like this --
CDate([YourTable].[YourDateField])
this remove the time component from the datetime data.
In the criteria row enter Date()

--
KARL DEWEY
Build a little - Test a little

I assume [Change Requests] and [Change Request_1] are two seperate tables. If
so can this be run with just one table [Change Requests]? I assume [Date
open] is another table? What if the date is in you original and only table.

All my data is in one table.

Try this substituting you table and field names --
SELECT TOP 1 [Change Requests_1].[Date open], [Change Requests_1].x
FROM [Change Requests], [Change Requests] AS [Change Requests_1]
WHERE (((CDate([Change Requests].[Date open]))=Date()) AND (([Change
Requests_1].x)>=[Change Requests].[x]*1.5 Or ([Change Requests_1].x)<=[Change
Requests].[x]*0.5))
ORDER BY [Change Requests_1].[Date open] DESC;

--
KARL DEWEY
Build a little - Test a little

I have numerous values (a,b,......) that are associated with a specific date
and time. I would like to compare the value obtained today against the most
recent date other than today. i am looking for a 50% changed in values
between the most recent (today) and the previous value. thanks in advance

 

[John W. Vinson]

Karl's suggesting that you use the same table TWICE in the query: the FROM
clause

FROM [Change Requests], [Change Requests] AS [Change Requests_1]

opens the table twice, and assigns the alias ChangeRequests_1 to the second
instance of the table.

I assume [Change Requests] and [Change Request_1] are two seperate tables. If
so can this be run with just one table [Change Requests]? I assume [Date
open] is another table? What if the date is in you original and only table.

All my data is in one table.

Try this substituting you table and field names --
SELECT TOP 1 [Change Requests_1].[Date open], [Change Requests_1].x
FROM [Change Requests], [Change Requests] AS [Change Requests_1]
WHERE (((CDate([Change Requests].[Date open]))=Date()) AND (([Change
Requests_1].x)>=[Change Requests].[x]*1.5 Or ([Change Requests_1].x)<=[Change
Requests].[x]*0.5))
ORDER BY [Change Requests_1].[Date open] DESC;

--
KARL DEWEY
Build a little - Test a little

I have numerous values (a,b,......) that are associated with a specific date
and time. I would like to compare the value obtained today against the most
recent date other than today. i am looking for a 50% changed in values
between the most recent (today) and the previous value. thanks in advance