Using Totals w/ an Updateable Recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jamie McD - 17 Jul 2006 17:06 GMT

I have two tables with a one to many relationship, well call them A and B.

I have a continuous recordset form based upon this relationship.

The issue I'm having is I only want the Last value of B to show up - meaning
I do not wan't multiple lines from A for each B, just one.

I can do this using the Totals option of the Query, and choosing last for my
B value, it works - I only get one record from A, and the latest record from
B.

The problem I have, is that I need to update a field from A, using this
query on the form. The new recordset that shows the info I want, comes up as
un-updateable.

I can use Dlookup at the form to find my value, but my users don't really
like how it works with the continuous recordset.

Any thoughts?

Reply to this Message

Allen Browne - 17 Jul 2006 17:33 GMT

You should be able to use a subquery in the WHERE clause and still have an
updatable query.

Subform recordsource would be something like this:

SELECT B.*
FROM B
WHERE B.ID =
(SELECT Max(ID)
FROM B AS Dupe
WHERE Dupe.FK = B.FK);

The example uses ID is the autonumber primary key, and FK as the foreign key
field name.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have two tables with a one to many relationship, well call them A and B.
>
[quoted text clipped - 19 lines]
>
> Any thoughts?

Reply to this Message

Jamie McD - 17 Jul 2006 18:29 GMT

Wonderful, I'm new to Access 2000/2003, so this should be able to help in
many other places I've had to throw work arounds in.

Thanks!

> You should be able to use a subquery in the WHERE clause and still have an
> updatable query.
[quoted text clipped - 39 lines]
> >
> > Any thoughts?

Reply to this Message

John Vinson - 17 Jul 2006 18:31 GMT

>I have two tables with a one to many relationship, well call them A and B.
>
[quoted text clipped - 6 lines]
>B value, it works - I only get one record from A, and the latest record from
>B.

Ummm... you're being tricked. The "Last" operator is all but useless.
It does *NOT* mean "the latest record" - it means "the last record in
disk-storage order". Sometimes it will actually work for you but you
have NO guarantee; Access will store records wherever it pleases, not
necessarily in data entry order.

>The problem I have, is that I need to update a field from A, using this
>query on the form. The new recordset that shows the info I want, comes up as
>un-updateable.

No Totals query, nor any query containing a Totals query, is ever
updateable (even if logically it should be).

>I can use Dlookup at the form to find my value, but my users don't really
>like how it works with the continuous recordset.

That's unfortunate because it's your only workable solution.

John W. Vinson[MVP]

Reply to this Message