Non-editable query

Question

I created a query called qry_MostRecentDates on a table named Prices that extracts the most recent dates (as well as the key field) from a table (I used the GROUP BY on the field Description and LAST for the date field and the record number).

I used the output of this query in another query to display the data of the other fields in the same table. I would like to edit the data of these records, but the second query does not allow me to edit the information in that query.

I would like to be able to edit the record with the most recent date from the table Prices. How can I solve this? I already tried to set the query and the form to Dynaset (inconsistent update), but this had no effect.

Ron

Answer 1

As soon as you use aggregate functions such as Last, or use GROUP BY, the query is not updatable.

Answer 2

Both queries below:

Thanks for your help in advance, Ron

Query 1:

SELECT DISTINCT tbl_Prijsbeheer.pbh_Product, Last(tbl_Prijsbeheer.pbh_DatumInvoer) AS Laatste_pbh_DatumInvoer, Last(tbl_Prijsbeheer.pbh_ID) AS Recordnr

FROM tbl_Prijsbeheer

GROUP BY tbl_Prijsbeheer.pbh_Product

ORDER BY tbl_Prijsbeheer.pbh_Product;

Query 2:

SELECT tbl_Prijsbeheer.pbh_ID, ot_Product.pdt_Productomschrijving, tbl_Prijsbeheer.[pbh_Productgroep ID], tbl_Prijsbeheer.pbh_Productgroep, tbl_Prijsbeheer.pbh_Leveranciernummer, tbl_Prijsbeheer.pbh_Leveranciernaam, tbl_Prijsbeheer.pbh_Inkoop, tbl_Prijsbeheer.pbh_Marge1, tbl_Prijsbeheer.pbh_Verkoop1, tbl_Prijsbeheer.pbh_Marge2, tbl_Prijsbeheer.pbh_Verkoop2, tbl_Prijsbeheer.pbh_Opmerking, tbl_Prijsbeheer.pbh_Voorraad, tbl_Prijsbeheer.pbh_Eenheid, tbl_Prijsbeheer.pbh_DatumInvoer

FROM ofrmqry_LaatstePrijsInformatie INNER JOIN (ot_Product INNER JOIN tbl_Prijsbeheer ON ot_Product.pdt_ID = tbl_Prijsbeheer.pbh_Product) ON (ofrmqry_LaatstePrijsInformatie.Recordnr = tbl_Prijsbeheer.pbh_ID) AND (ofrmqry_LaatstePrijsInformatie.Laatste_pbh_DatumInvoer = tbl_Prijsbeheer.pbh_DatumInvoer);

Hello John,

Could you please have a look at both queries?

Thanks, Ron

Answer 3

Both queries below:

Thanks for your help in advance, Ron

Query 1:

SELECT DISTINCT tbl_Prijsbeheer.pbh_Product, Last(tbl_Prijsbeheer.pbh_DatumInvoer) AS Laatste_pbh_DatumInvoer, Last(tbl_Prijsbeheer.pbh_ID) AS Recordnr

FROM tbl_Prijsbeheer

GROUP BY tbl_Prijsbeheer.pbh_Product

ORDER BY tbl_Prijsbeheer.pbh_Product;

Query 2:

SELECT tbl_Prijsbeheer.pbh_ID, ot_Product.pdt_Productomschrijving, tbl_Prijsbeheer.[pbh_Productgroep ID], tbl_Prijsbeheer.pbh_Productgroep, tbl_Prijsbeheer.pbh_Leveranciernummer, tbl_Prijsbeheer.pbh_Leveranciernaam, tbl_Prijsbeheer.pbh_Inkoop, tbl_Prijsbeheer.pbh_Marge1, tbl_Prijsbeheer.pbh_Verkoop1, tbl_Prijsbeheer.pbh_Marge2, tbl_Prijsbeheer.pbh_Verkoop2, tbl_Prijsbeheer.pbh_Opmerking, tbl_Prijsbeheer.pbh_Voorraad, tbl_Prijsbeheer.pbh_Eenheid, tbl_Prijsbeheer.pbh_DatumInvoer

FROM ofrmqry_LaatstePrijsInformatie INNER JOIN (ot_Product INNER JOIN tbl_Prijsbeheer ON ot_Product.pdt_ID = tbl_Prijsbeheer.pbh_Product) ON (ofrmqry_LaatstePrijsInformatie.Recordnr = tbl_Prijsbeheer.pbh_ID) AND (ofrmqry_LaatstePrijsInformatie.Laatste_pbh_DatumInvoer = tbl_Prijsbeheer.pbh_DatumInvoer);

Answer 4

Can you post the SQL of the query?  It might be possible to use the qry_MostRecentDates as a sub-query in the where clause to identify the records to be update.

SELECT *

FROM SomeTable

WHERE SomeTable.PrimaryKey in (SELECT PrimaryKey FROM qry_MostRecentDates). 

I am a bit worried that you used LAST for the date field in the qry_MostRecentDates.  Last is not a reliable method to get the most recent date.  Last returns the last record found by the query in each group of records, this could be the last record by date or the last record stored or the last record found on the disk or ...

Your best bet might be to use a correlated sub-query in the where clause.

SELECT * FROM SomeTable

WHERE SomeTable.SomeDate =

   (SELECT Max(SomeDate) FROM SomeTable as Temp 

   WHERE Temp.Description = SomeTable.Description)

You may need other fields in the sub-query to ensure a correct match.