Automatically displaying a certain record of a query

[Guest]

I have a form (A) that is bound to a table (A) that for example only keeps a
record of name and SSN. The SSN in table A is the primary key. Form B is
bound to table B and table B only keeps a record of date, amount , and SSN.
The key field in table B is an autonumber. I created a query that shows the
results of name, date, and amount in descending order by date.

I created a third Form (C) that is supposed to show the results of the query
for only one record. I want the fields on Form C to reflect the record of
choice. For example, if John Doe has three records, each having a different
date, I want Form C to only reflect the most recent record out of the three
records for John Doe.

Now, I know how to make one form go to a record that is the same as a record
on another form but how do you make Form C go to the most recent record in
the query based on the SSN shown on Form A?


Quick Note: I have a parameter query set up Identical to the query attached
to Form C that will allow you to see all the records for lets say John Doe,
but again, I'm trying to do this based on the SSN shown on Form A to get a
single, most recent record in the query on Form C.


Is this possible? If not, any suggestion on how to restructure the database
to get this result?

 

[Tony D'Ambra]

Edit the RecordSource SQL for FormC to return the Top 1 sorted DESC...

For example:

SELECT DISTINCTROW TOP 1 Orders.OrderDate, Orders.EmployeeID,
Orders.OrderID, Customers.CompanyName
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.EmployeeID)=[Employee]))
ORDER BY Orders.OrderDate DESC;


Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net