Recordset not updatable...is there another way?

[Saberan]

First some information about the dbase....
Main Table contains all of the base information on our customers, there is only one line per customer and all customer names are unique
Second Table contains the customer name, date of construction, and all applicable information pertaining to that construction. (a customer may have several entries in this table)

I'm trying to find a way to create a query that will pull the latest construction for each customer from the second table, and join it with all the information from the first table while allowing me to update information from both tables.

I've tried to do a totals query on the second table and use that to filter records but that makes the query read only, I then tried the same query as a make a table query but to filter out the records I need to join this new table by both date of construction and customer name, which makes the new query read only.....is there a way to do this or am I missing something?

 

[xenou]

I'm trying to find a way to create a query that will pull the latest construction for each customer from the second table, and join it with all the information from the first table while allowing me to update information from both tables.

Hi,
You can't update information in two tables at the same time.

 

[Saberan]

Hi,
You can't update information in two tables at the same time.

Thank you, and I was to understand you could update information based on two tables as long as they had a one to one relationship (neither here nor there), but I'm not even getting to update information based on one table at the moment....for now, if its possible, i'd like to run a query that will return the latest construction for each customer and still have the information updateable. (all based on the second table)

 

[xenou]

If the query uses grouping it is not updateable. It doesn't make sense to get the latest information in the second table and use it to update data in the first. The first table has only data that is related one to many in the second table. You probably need to be more specific about what you are trying to do exactly (what to update).

 

[Saberan]

If the query uses grouping it is not updateable. It doesn't make sense to get the latest information in the second table and use it to update data in the first. The first table has only data that is related one to many in the second table. You probably need to be more specific about what you are trying to do exactly (what to update).

Well, in my database there is one main table that contains all the base information for a customer (ie. Name, address, equipment, etc). There is another table that contains construction information (mainly dates that different stages of the construction are complete and permit numbers). It is a possibility that a customer can have multiple constructions for several reasons (remodels and demolition/renovations to name two). I'm trying to find a way to query the construction table to return the latest construction for a customer based on a field call Const_StartDate. The only way I know how to do so is to do a totals query grouping the customer name (unique field) and the construction start date, then using the results to filter the construction table. Unfortunately the total query is read only.

 

[Saberan]

....Additional Note (sorry), Ideally I would of liked to tie the latest construction information to the base information from the main table in a form to be able to update the information regarding the current construction as it happens....for now i'll be happy with being able to query the construction table to return the latest construction information for each customer in that table in an updatable format.

 

[xenou]

Off the top of my head, for form design, perhaps use a subform that shows only one record. The subform is in descending order. Then you see the lastest record. Assuming it's possible to only show one record in a continuous sub form. Or simplest of all just show all records and of course the lastest is at the top of the list.

Possibly put a hidden field on the main form with a dmax() function, and use it to filter the subform.

You might also be able to put a correlated criteria in the subform rowsource:
{where} constr_date = (select max(const_date) from Table where Cust = [MainForm].[Cust])

 

[Saberan]

Just figured out an odd way to do it....using a criteria to filter the customer name, and a dmax formula as a criteria to filter the construction start date....may not be as elegant as I hoped, but it will do the trick...thanks for the help xenou