How to create editable form that resemble pivot table?

[weekend00]

Join query is not updatable(maybe some exception).

then you may use temporary table

[pav]

Join query is not updatable(maybe some exception).

I found it not to be the case. Consider this simple query for example:

SELECT dates.Date_DMY, qc1.rtime AS Running
FROM dates LEFT JOIN qc1 ON dates.ID = qc1.edate;

It produces this result

DATE_DMY......RUNNING
04.10.20........10.....
05.10.20........17.....

and it is updatabe.
But the more complicated query

SELECT dates.Date_DMY, qc1.rtime AS Running, qc2.stime AS Spinning
FROM (dates LEFT JOIN qc1 ON dates.ID = qc1.edate) LEFT JOIN qc2 ON dates.ID = qc2.edate;

which produces this result

DATE_DMY......RUNNING......SPINNING
04.10.20........10...............25......
05.10.20........17........................

is not updatable

[Rawb]

Any Query that has only 1 to 1 relationships will be updateable. If there is no direct relationship or if there is a one to many relationship then the results of the Query will not be updateable.

If you're looking for a way to emulate a Pivot Table, you can certainly do it, but no matter how you do it's going to take a lot of work. I know because I've just spent the better part of two weeks working on the same kind of thing.

Your best bet is to go the Temporary Table route. Once you have the Temporary Table set up, you leave all the Form Objects "Unbound" and fill them in programmatically with the Form's On Load Event.

Then you'll need to use the After Update or On Change Events of the various Form Objects to handle updates and changes to the Form.

[slave138]

You can use joined queries in an update query as long as none of them are aggregate queries (which includes crosstab queries). If they are, you will need to output the query to a temp table and then use that table in the update query.

Or at least that's what I keep getting for a response every I have problems with an Update query.

[pav]

You can use joined queries in an update query as long as none of them are aggregate queries (which includes crosstab queries). If they are, you will need to output the query to a temp table and then use that table in the update query.

Or at least that's what I keep getting for a response every I have problems with an Update query.

In my example above, non of the queries are aggregate. The only difference between the first qurey (which IS updatable) and the second query (which is NOT updatable) is that an extra relation was added. That's what puzzls me