Automatically displaying a certain record of a query

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

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?
 
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
 
Back
Top