form based on stacked query - doesn't allow edits/additions

[krowe]

Hi All

I have done some research on this already, and realise that my form doesnt work as it is based on a stacked query, but I dont know what the best solution to this is.

I have tblPerson with client details. tblAddressHistory with addresses for each client. I use 2 queries to get the most recent address for each client:

qryCurrentAddress_A (gets the most current PropID for each person)

SELECT DISTINCT tblPropHistory.ID, Max(tblPropHistory.MovedIn) AS MaxOfMovedIn
FROM tblPropHistory
GROUP BY tblPropHistory.ID;

qryCurrentAddress_B (adds in the rest of the address details)

SELECT DISTINCT tblPropHistory.ID, tblPropHistory.PropID, tblPropHistory.PropA1, tblPropHistory.PropA2, tblPropHistory.PropA3, tblPropHistory.PropAPostCode, tblPropHistory.MovedIn, tblPropHistory.MovedOut, tblPropHistory.Tenure, tblPropHistory.Notes, tblPropHistory.[Current User], tblPropHistory.MortAmountOutstanding, tblPropHistory.MortType, tblPropHistory.MortRepemption, tblPropHistory.CMI, tblPropHistory.MortArrears, tblPropHistory.MRef, tblPropHistory.Mortgagee, tblPropHistory.MortOutcome, tblPropHistory.MortReason, tblPropHistory.Rent, tblPropHistory.TenancyType, tblPropHistory.TenancyStatus, tblPropHistory.Arrears, tblPropHistory.TenancyStartDate, tblPropHistory.TenancyEndDate, tblPropHistory.NoticeExpirationDate, tblPropHistory.PosessionExpirationDate, tblPropHistory.WarrentExpirationDate, tblPropHistory.Agent, tblPropHistory.Landlord, tblPropHistory.Current, tblPropHistory.LLPGAdd1, tblPropHistory.LLPGAdd2, tblPropHistory.LLPGAdd3, tblPropHistory.LLPGAdd4, tblPropHistory.LLPGAddPC, tblPropHistory.LLPGAddUPRN
FROM tblPropHistory INNER JOIN qryCurrentAddress_A ON tblPropHistory.MovedIn = qryCurrentAddress_A.MaxOfMovedIn
WHERE (((tblPropHistory.ID)=[qryCurrentAddress_A]![ID]));

then I have a query to link the current address with the person, which the form is based upon:

SELECT qryPerson.*, qryCurrentAddress_B.PropID, qryCurrentAddress_B.PropA1, qryCurrentAddress_B.PropA2, qryCurrentAddress_B.PropA3, qryCurrentAddress_B.PropAPostCode, qryCurrentAddress_B.MovedIn, qryCurrentAddress_B.MovedOut, qryCurrentAddress_B.Tenure, qryCurrentAddress_B.Notes, qryCurrentAddress_B.[Current User], qryCurrentAddress_B.MortAmountOutstanding, qryCurrentAddress_B.MortType, qryCurrentAddress_B.MortRepemption, qryCurrentAddress_B.CMI, qryCurrentAddress_B.MortArrears, qryCurrentAddress_B.MRef, qryCurrentAddress_B.Mortgagee, qryCurrentAddress_B.MortOutcome, qryCurrentAddress_B.MortReason, qryCurrentAddress_B.Rent, qryCurrentAddress_B.TenancyType, qryCurrentAddress_B.TenancyStatus, qryCurrentAddress_B.Arrears, qryCurrentAddress_B.TenancyStartDate, qryCurrentAddress_B.TenancyEndDate, qryCurrentAddress_B.NoticeExpirationDate, qryCurrentAddress_B.PosessionExpirationDate, qryCurrentAddress_B.WarrentExpirationDate, qryCurrentAddress_B.Agent, qryCurrentAddress_B.Landlord, qryCurrentAddress_B.Current, qryCurrentAddress_B.LLPGAdd1, qryCurrentAddress_B.LLPGAdd2, qryCurrentAddress_B.LLPGAdd3, qryCurrentAddress_B.LLPGAdd4, qryCurrentAddress_B.LLPGAddPC, qryCurrentAddress_B.LLPGAddUPRN
FROM qryPerson LEFT JOIN qryCurrentAddress_B ON qryPerson.ID = qryCurrentAddress_B.ID;

The form doesn't allow edits or additions.

I am thining one work around could be to change the final query to a make-table query - however would that update tblPerson and tblAddressHistory?

The other idea is that the current address could be presented as a subform with the main person form however I think this would be a bit messy.

Please could I call upon your collective experience for some advice and /or better solutions.

Thanks

Kev

 

[spikepl]

The issue is not "stacked" query, but what is in it: http://allenbrowne.com/ser-61.html

To avoid the "most recent" which makes the query not updateable, you could use the domain funcrtions (DMAX etc) to get the "most recent" values, and then use them as parameters in your query. Alternatively, an address could be tagged as "most recent" , or the ID of the most recent address could be stored in the record. Likewise for the other "most recent"

You have not specified what exactly you wish to remain updateable (or "addable"? ) among the currently existing data.

 

[krowe]

Thanks for your reponse.

so looking at that link, you think it is the GROUP BY clause that is causing me problems?

The data I need addable is the fields in tblPerson, the data in tblProp history is just for information, it is inputted using a different form.

I've never looked at DMax before, I will see if i can make that work. I dont really want to be adding in extra fields for most recent property, as it requires more data entry and maintenace when someone moves.