How to create editable form that resemble pivot table?

[pav]

I want to create a form that I can use to enter data, and I want that form to resemble a pivot table (instead of using multiple single-entry forms). But whenever I create a form from a pivot table, I can’t add (or change) data in it.
To illustrate, consider a simple exercise planner database. It includes a date, exercise routine and the amount of time spent on that exercise.
I can create the database, and the pivot table where rows correspond to a day and colons correspond to exercise routines. That view is the most convenient for planning. And I can create a form that is based on the pivot table. However, I can neither edit nor add new entries in that form. What would be the work around that issue?

[weekend00]

Is a pivot table(I think it's a Query) editable? I don't think so.

You need to create a form base on the original table.

[pav]

Here is the problem. My original table has three colons: date, exercise routine and time. When I create form from this table, it shows only one record at a time, which makes it difficult to plan exercises, since you can't see what else you are doing that day, or when else did you plan that routine. I do want to have time-by-routine form that I can edit, so what would be the best way to do it?

[weekend00]

do a continuous form, not a single form, you can see all records.

[pav]

With continuous form my records show all in one colon. How can I arrange them in a time-by-routine format, in a similar way a planner page is arranged?

[evander]

Hi.

Your problem is similar to creating a timesheet system, in which you display the cells in a matrix format even though data is stored in rows in some table.

The technique relies on using LEFT JOINS, and creating column aliases for those rows that you want to be displayed as columns.

You can read the book "Visual Basic 2005 Databases" by Thearon Willis (I know, my copy is really old). That's where I learned this technique.

Hope this helps. Good luck.

[weekend00]

why do you have on column in continuous form? you can have all fields. but they are all in one row and can't be arranged freely.

[pav]

why do you have on column in continuous form? you can have all fields. but they are all in one row and can't be arranged freely.

With continuous form my records show like this:

DATE............ACTIVITY..........TIME
01.01.2010....running.............25....
01.01.2010....spinning............30....
01.02.2010....running.............35....

... etc.

I would rather have them arranges in matrix format:

DATE ..............running..........spinning
01.01.2010.......25.................30.......
01.02.2010.......35...........................

as it's a much easier to plan activities. I want that form to be editable.

[weekend00]

[quoto]
DATE ..............running..........spinning
01.01.2010.......25.................30.......
01.02.2010.......35...........................
[/quoto]
I don't think you can do this unless using some complex code.
Let's look at you sample data, if you change the bold 01.01.2010 to 02.02.2020, Access need to change two cell values in the table reflecting this one textbox. I don't think Access will/can do that.

Maybe you can look for alternated way, e.g. redesign you table in this way:
DATE ..............running..........spinning
01.01.2010.......25.................30.......
01.02.2010.......35...........................

Or, you can create a temporary table as above, let user edit the temporary table, when the form close, update the original table from temporary table. But I don't suggest the temporary table.

[pav]

I don't intend to change dates, only values in "running" and "spinning" colons. Each of those values corresponds to only one value in the original table, so its not too complicated. As for the dates, the form should list every value (every day) consecutively for a given range, in the same way a paper planner has pages ruled.

[weekend00]

do you want to redesign you table as I suggested?

[pav]

Evander, thank you for your suggestion. I've checked on amazon and found that Thearon Willis has also authored "Beginning VB.NET Databases", may be it has similar examples. I have to check if my locale bookstores carry it. I'll let you know.

[pav]

do you want to redesign you table as I suggested?

what is the difference between the top and the bottom table? they both look the same to me

[weekend00]

the top is a PIVOT query, the bottom is a table. they look the same. but a PIVOT query is not updatable while a table is.

[pav]

Did you mean to design the original table this way? It won't do because if you would want to add things like person's name (i.e. schedules for more than one person) or an exercise place (e.g. indoors / outdoors), e.t.c you won't be able to do it.

What I did try is to create two intermediate queries, q1 and q2 based on the original table. q1 had two colons - date and activity filtered to include only running, and q2 was similar except it was filtered to include only spinning. I then used LEFT JOIN to create a final query that resembled the pivot table that I want; it had three colons DATE, RUNNING and SPINNING. And I couldn't update it, even though q1 and q2 are updatable.