Viewing most up to date information in a one to many relationship

[CDaly]

Is there a way to view the most up to date informtion from a table when linking tables that have one record per customer on one table and more than one record on the other table?

For the table that has many records I just want to see the most recent record.

Thanks,
Claire

 

[markphsd]

Most recent record updated, or created.

If you use the auto number, you can sort the results by the autonumber of the many sided table.

If you use a field to indicated the last time the record is update, you can sort by that field.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[PHV]

You may consider using the TOP 1 predicate.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[zoroaster]

For the many table create a separate query based on the primary key, with the Max of the date field, then link this query on all equal fields to the table it's based on, and then link that to the customer table. It's one way I've learn't (which although less efficient) is a way round having to write subqueries, which is the other obvious way of doing it, but since I'm no genius at SQL, someone else can probably provide a better answer along thos lines.

Laters, Z

"42??? We're going to get lynched!

 

[CDaly]

I have now managed to see the most recent information and the customer information but can not update information on the table with the customer on it only once in the view.

Is it possible to do this?

Thanks,
Claire

 

[zoroaster]

What method did you end up using Claire? If you've used the one I've just elaborated, no, I don't think it will be an updateable query...

Laters, Z

"42??? We're going to get lynched!

 

[zoroaster]

Just thinking... I'd suggest you repost this question in the Access Queries/Jet SQL forum, and specify that you want the query to be updateable. I'm honestly not sure if it is possible, but there WILL be someone there who will know.

Best of luck...

Laters, Z

"42??? We're going to get lynched!

 

[zoroaster]

Another thought... have a look into creating one form based on your Customers table, and a subform based on a query that comprises of just the Max dates of Many-table linked to the Many-table fields you want on the form.

I apologise if I've not made myself very clear!

Laters, Z

"42??? We're going to get lynched!