update field in first record of query

[FrankM]

I have a Form which Users update fields with comments. This form is based off
a Query which has criteria in it to specify a date range, filter out certain
comments and limited the records to a particular Site (the Site piece is
based off another Form).

The issue is there are multiple records which have all the same information
accept for the Primary Key (which is an Auto Number) and the Account field. I
want to filter out the duplicate records and have just the first Account
displayed but every time I try to do that the Query can not be used to update
the comment field.

I hope that makes sense.

Any thoughts?

 

[Lord Kelvan]

if you use a query to filter anything it makes the recordset non
updateable

 

[John Spencer]

Possible to do depending on what your structure is.

For instance if you just wanted the Maximum Primary Key for each
Account, the query would look like the following.

SELECT *
FROM SomeTable
WHERE PK IN (
SELECT Max(PK)
FROM SomeTable as Tmp
Group BY Tmp.Account)

Your first requirement would be to build a query that returns the
Primary Key of the records you want to edit. I don't have enough
information to identify how you would do that.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[FrankM]

That was my fear.

 

[FrankM]

Unfortunately that didn't seem to work. I modified the code to fit my field
names ...

SELECT *
FROM qrydatacommentsv3d
WHERE ID IN (
SELECT Max(ID)
FROM qrydatacommentsv3d as Tmp
Group BY Tmp.ACCOUNT_NUMBER);

I used "qrydatacommentsv3d" because that returns just the certain Site
needed. I used "ID" because that is the Primary Key and obviously
"ACCOUNT_NUMBER" is the Account.

The only two records that differ at all are the ID and ACCOUNT_NUMBER
fields; all the other fields are the same. The great thing about your code is
that the records ARE updateable and I thought any Query with a Group By
statement would not be updateable. You've given me hope to keep trying!

 

[John Spencer]

IS qryDataCommentsv3d updateable? It must be if you want to update the data
in it.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

if you use a query to filter anything it makes the recordset non
updateable

That's ridiculous and completely untrue. Most queries are updateable.

 

[FrankM]

Yes, qrydatacommentsv3d is updateable. I use your code and modified it
slightly and everything worked perfectly. I checked with my Users and it
turns out they don't really need the ACCOUNT_NUMBER field for this part of
the process. So I removed that from the qrydatacommentsv3d Query and then
changed the code provided as what follows. This worked perfectly and made
everything excellent. Many, many thanks! I didn't think a Query with the
Group By statement was updateable but this worked perfectly.


SELECT *
FROM qrydatacommentsv3d
WHERE (((qrydatacommentsv3d.ID) In (SELECT First(ID)
FROM qrydatacommentsv3d as Tmp
Group BY Tmp.CUSTOMER_ID)));


Since I removed the ACCOUNT_NUMBER field I changed part of the code to read
CUSTOMER_ID in its place, regardless, this code does exactly what I needed
and I am very, very grateful.

 

[John Spencer]

You can use a group by query in a subquery in a where clause and have the main
query updateable. You cannot use a group by elsewhere and have an updateable
query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County