Using Totals w/ an Updateable Recordset

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

Guest

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?
 
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
 
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!
 
On Mon, 17 Jul 2006 09:06:01 -0700, Jamie McD <Jamie
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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

John W. Vinson[MVP]
 
Back
Top