Show only Last record

[ZmrAbdulla]

Hi,

I have a store Issue ledger database.
Fields in the table are below
IssueID (AutoNumber)
Date
Name of the Person
Item Name
Quantity

Now I need to create a query that will show only the last record of the issue of a particular person and particular item and Quantity. Then I can calculate the time difference between last issue and current issue.
Is this possible?

Thanks

Zameer

 

[KenReay]

Hi

Not sure how you plan to access this data, but you could simply have a query which selected only issues for given person, and ORDER BY Date DESC, then read only the first row of the returned recordset

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ZmrAbdulla]

Hi Ken,

It is easy for me to return a query like what you said.
My requirement is the query should only return the last record of the given person and the given item. No matter how I'm accessing the data. I can run the query from any of the forms.Only thing When I give the parameters to the query
it should return me only the last record. I can give parameters for person and item. I dont know the parameter for the date field to return the last issue date.The reason of this is to print the data on the issue request.
So it will print only Person Name, Item Name, Last issue date, Current Issue date and time elapsed (difference between two dates)

Sorry if this is taking your valuable time.

Thanks
Zameer

 

[KenReay]

Hi

Sounds like you need to make an agregate query, and use the LAST setting for Date, or possibly for IssueId, sincce IssueId is an autonumber, moust recently created record should have highest value

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ZmrAbdulla]

Hi Ken,
I got the answer from your clue. It is in the help file

"Domain Aggregate Functions"
"DFirst, DLast Functions"
Then top value = 1
Thanks
Zameer

 

[CyberPerk]

For those MS-SQL guys who searched on Google using the phrase "show last record query" or something like that AND you found this thread on tek-tips.com, then here is your answer:

SELECT *
FROM mytable
WHERE my_id=(SELECT MAX(my_id) FROM mytable)

This query will show the last record.

Enjoy