Filters and query records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a problem and maybe someone here could solve my problem. I have a
table in Access and I want to make a query depending of the last entry (now
function) of the table. If the last entry is an apple I want to make a query
of all the apples that is more than maybe $50.

No DateEntry Product Price
1 10:30 Apple 55
2 10:33 Pear 20
3 10:10 Apple 25
4 10:50 Apple 50
5 10:40 Apple 52


The criteria should be No 4 because of the time it’s entered last.

This would generate in the query:

No DateEntry Product Price
1 10:30 Apple 55
5 10:40 Apple 52

Many thanks in advance.

Stefan
 
Stefan said:
I have a problem and maybe someone here could solve my problem. I have a
table in Access and I want to make a query depending of the last entry (now
function) of the table. If the last entry is an apple I want to make a query
of all the apples that is more than maybe $50.

No DateEntry Product Price
1 10:30 Apple 55
2 10:33 Pear 20
3 10:10 Apple 25
4 10:50 Apple 50
5 10:40 Apple 52


The criteria should be No 4 because of the time it’s entered last.

This would generate in the query:

No DateEntry Product Price
1 10:30 Apple 55
5 10:40 Apple 52
Click to expand...


I think you want something like this:

SELECT T.No,
T.Product,
T.DateEntry,
T.Price
FROM table As T
INNER JOIN (SELECT TOP 1 X.Product,

X.Price
FROM table As X
ORDER BY X.DateEntry DESC) As M
ON T.Product = M.Product
WHERE T.Price > M.Price
 
Hi Marshall and thanks a lot for your answer. But I get an error message on
your code. I have renamed the table to T. Correct?

I don’t believe it, but could it be something wrong with your code?

Thanks

Stefan
 
What error message did you get?

What do you mean "renamed the table". I expected the query
to run without you having to do anything beyond Copy/Paste
my query into a new query's SQL view and replace "table"
with your real table's name. The T is just an alias for the
table name in the main query to make the query easier to
write and read. The X and M are required to distinguish the
subquery's table from the mainquery's table.

Sure, there could be something wrong with my query. I have
used the field names in your post and have no idea if you
posted the actual field names. But the way I read your
question, the query worked on my test case.
 
Hi Marsh
For the last 24 hours I have studied SQL-queries and I finally understood
your code and it is brilliant. I am a newbie at SQL-queries and never
realized the potential. You brought Access to a new level in my eyes. The
code works PERFECT in the example. My code didn’t work because of a
misspelling.

My records in the table will always be updated and new records will be
added; therefore the DateEntry will always change. Is it possible to always
get the query to look at the last changed DateEntry and pick the same fruit
with a price above the price of the last DateEntry?

Many thanks to you, Marsh, form your time and effort.

Stefan


Marshall Barton said:
What error message did you get?

What do you mean "renamed the table". I expected the query
to run without you having to do anything beyond Copy/Paste
my query into a new query's SQL view and replace "table"
with your real table's name. The T is just an alias for the
table name in the main query to make the query easier to
write and read. The X and M are required to distinguish the
subquery's table from the mainquery's table.

Sure, there could be something wrong with my query. I have
used the field names in your post and have no idea if you
posted the actual field names. But the way I read your
question, the query worked on my test case.
--
Marsh
MVP [MS Access]


Hi Marshall and thanks a lot for your answer. But I get an error message on
your code. I have renamed the table to T. Correct?

I don’t believe it, but could it be something wrong with your code?
Click to expand...
Click to expand...
 
Stefan said:
For the last 24 hours I have studied SQL-queries and I finally understood
your code and it is brilliant. I am a newbie at SQL-queries and never
realized the potential. You brought Access to a new level in my eyes. The
code works PERFECT in the example. My code didn’t work because of a
misspelling.

My records in the table will always be updated and new records will be
added; therefore the DateEntry will always change. Is it possible to always
get the query to look at the last changed DateEntry and pick the same fruit
with a price above the price of the last DateEntry?
Click to expand...


You can not find the last edited record unles you have a
field that indiactes it is the one that was most recently
changed. A DateTime field with the date the record was
changed would take care of it. That's what the current
query does using the DateEntry field. Maybe I don't
understand your new question and using your example records
would help me get a grip on what you want now.

Something about all this that's been bothering me is what do
you do if the "latest" DateEntry has the highest price?
I.e. there are no records with a higher price.
 
Back
Top