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

  • Thread starter Thread starter tcek
  • Start date Start date
T

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
 
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;
 
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.

KARL DEWEY said:
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


tcek said:
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
Click to expand...
Click to expand...
 
[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
Click to expand...

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


tcek said:
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.

KARL DEWEY said:
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


tcek said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

KARL DEWEY said:
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


tcek said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top