How to make joined queries editable (1 Viewer)

[AlefAelol]

Hello everyone, I have a query "qryStudentMarks" which include students marks for the whole academic year, and a group-by query "StudentsResults" that hold some calculation fields such as student semester result (Pass and Fail) and averages. I want to combine the both quires but want the resulted query be editable for further uses. I joined them via "StudentRegID", however, the resulted query became non editable because of group-by query . How to handle this issue please ?

 

[mike60smart]

Hi

Can you upload a zipped copy of the Db?

 

[AlefAelol]

Hi

Can you upload a zipped copy of the Db?

Thank you Mike for the replay, sure ! Uploaded with regards View attachment MyDataBase Sample.accdb

 

[arnelgp]

use Left Join instead of Inner Join

EDIT: late to see that one of the wuery is calculated, perhaos it wont at all.

 

[AlefAelol]

use Left Join instead of Inner Join

EDIT: late to see that one of the wuery is calculated, perhaos it wont at all.

Thank you arnelgp for the replay, Unfortunately, not worked

 

[mike60smart]

Hi

Your query will not work.

Why are you trying to edit records by means of a query anyway??

You would normally have a Form for editing records?

 

[CJ_London]

the only way you could make it editable is to change TheResultedQuery into a make table to create a temporary table and edit that instead.

Alternatively use a form/subform arrangement for the different queries - but the group by query will never be editable

 

[AlefAelol]

Hi

Your query will not work.

Why are you trying to edit records by means of a query anyway??

You would normally have a Form for editing records?

In the "TheResultedQuery" there is a field named "SAFirstAssest". This field in calculated depending on the two joined queries. I can't find another way to find "SAFirstAssest" unless join the two queries. In the query mode or the form mode, the resulted query would be non editable

 

[AlefAelol]

the only way you could make it editable is to change TheResultedQuery into a make table to create a temporary table and edit that instead.

Alternatively use a form/subform arrangement for the different queries - but the group by query will never be editable

It would be hard to create/delete table each time the record is edited for thousand of records

 

[AlefAelol]

the only way you could make it editable is to change TheResultedQuery into a make table to create a temporary table and edit that instead.

Alternatively use a form/subform arrangement for the different queries - but the group by query will never be editable

Also, the field "SAFExam" would be filled by hand once "SAFirstAssest" get values

 

[CJ_London]

personally I would drop trying to do this by a user editing a query - it is bad practice anyway.

Your approach seems to be based on the excel way of doing things - combining data and presentation in one row. Databases do not work that way and again would urge you to consider a form/subform approach

 

[AlefAelol]

personally I would drop trying to do this by a user editing a query - it is bad practice anyway.

again would urge you to consider a form/subform approach

For some reasons, I would keep the first part of your statement with no comment . Well, I am already using a form and subfrom, however, the subfrom is based on "TheResultedQuery" because all fields should be shown to the user. Again, based the subfrom on a non editable query would led to non editable subfrom too right ?

 

[CJ_London]

based the subfrom on a non editable query would led to non editable subfrom too right ?

correct - but the rule for forms is one form, one table - you have 2 or more.

If you are already using a form, then perhaps in the form use dlookups, dcounts etc for the fields you want to see, or have another subform to display the results

The point is, your question should be how to make a form editable, not a query

 

[AlefAelol]

correct - but the rule for forms is one form, one table - you have 2 or more.

If you are already using a form, then perhaps in the form use dlookups, dcounts etc for the fields you want to see, or have another subform to display the results

The point is, your question should be how to make a form editable, not a query

Can I DlookUP "ResFCourse" from "qryStudentsResults" into "qryStudentsMark" by "StudentRegID"?

 

[The_Doc_Man]

From your initial description, you are facing issues with a query that contains or otherwise presents averages, which is an aggregate function. You also have computed fields.

The query will never be updateable due to those two factors. The GROUP BY actually doesn't make a difference with respect to updating because it only affects order of presentation, not actual content. But the aggregation of data and use of formulas is fatal to this process.

What you do in this case is to recognize that a form can do more than a query can do. A form can, on Form_Current event (e.g.) compute averages using the DAvg() function and can compute formulas, then fill in UNBOUND text boxes to show you what you wanted to know. So if you REMOVE all aggregates and formulas from the query and instead compute those things in the Form_Current event, you still see what you want, but the rest of the query can be updated. If in fact you are working with a one/many JOIN, the form equivalent is a parent/child form structure, and that should allow you to keep relevant records together where you can see them.

So this is a "divide and conquer" approach to your problem. Do the aggregates and formula in the form. Do the gathering of data in the query. Do the presentation to your users through a form.

 

[CJ_London]

you can, but a) not efficient and b) unlikely to make your query editable - but try it and see

I was suggesting put your dlookup as a controlsource to your 'ResFCourse' textbox control on the form, rather than binding it to the field in your form recordsource.

I repeat the rule for forms is one form, one table. Typically as soon as you use a query that references two tables, that query (and the form based on it) becomes uneditable

If you have your relationships set up correctly then sometimes changing the form recordsettype from dynaset to dynaset inconsistent updates will work - but all depends on the query and the type of updates you want to do

 

[AlefAelol]

compute averages using the DAvg() function and can compute formulas, then fill in UNBOUND text boxes to show you what you wanted to know

information in UNBOUND controls are not for further uses as I know. In my case, fields like average or ResFCourse later they would be used to filter the students. Example, if I want to find the top 3 students according to their averages, how could achieve this task if there is no students average stored in a query. How could do it with averages saved in UNBOUND text boxes. Sorry for bothering, but really that is my understanding of using forms and queries.

 

[The_Doc_Man]

You are asking for the impossible. So the only solution is to somehow change the nature of the question to something that IS possible. Which is why I suggested the course of action that I did. But if you MUST store this stuff for future consideration, the only way I know to do this is to look into multi-layered queries.

First, write a query to generate the averages. This aggregate query will not be updateable.

Second, write a query to sort the output of the first query by averages. You can do a grouping of this if you wish.

Third, there is a way to do a TOP 3 "SELECT" query that would use the 2nd query as input.

The only OTHER way to do this is to write an UPDATE query by writing a sub-query as the source of your data. You CANNOT update a JOIN with an aggregate as the primary part of the FROM clause but you can update a query by pulling the value from a sub-query that names the other table in the sub-query's WHERE clause. Or you can do a DAvg or DLookup, though I wouldn't recommend it due to your comment about having thousands of records. Domain Aggregates in queries of that size will slow things down to a nightmarish crawl.

 

[Pat Hartman]

The GROUP BY actually doesn't make a difference with respect to updating because it only affects order of presentation, not actual content

Group by aggregates data so it renders the query not updateable. Order by orders the rows. However in Access SQL, Group By seems to also order rows even though it doesn't in SQL Server.

 

[The_Doc_Man]

Pat, a GROUP BY without an aggregate function acts like an ORDER BY, or at least it did so the last time I tried it. (I admit it has been a while.) Of course, a GROUP BY that HAS an aggregate cannot be updated.