Cross tab chart as a form?

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Cross tab chart as a form?

Post by Jeff H »

I have a garden db in Access 365 and I’ve been collecting info on the growth phases of the plants. The data is collected weekly, designated by the weeks of a month (for example, 5.3 is the third week of May). There are nine growth phases such as “new foliage”, “mature foliage”, “flower buds”, “full bloom”, etc.

Can the following be done? I’d like to create a form that displays the weeks as row headings and the plants as column headings and the intersections displaying each plant’s growth phase in the corresponding weeks. It sounds like a cross-tab, but I only see aggregated data in the cross-tab query display options.

I’d like it to be a form and be able to update the growth phases directly on the form. At the present time I use a sub-form in each plant's main form to record the weekly phases. But once I’ve collected the data for a couple of years, the task would be more of a look-up reference with incidental tweaking.

The objective is to help plan the garden to maintain successive blooming throughout the growing season.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cross tab chart as a form?

Post by HansV »

You could create a crosstab query with:

Week - Group By - Row Heading
Plant - Group By - Column Heading
Growth Phase - First - Value

Next, create a continuous form based on the crosstab query.

Warning: this could pose a problem if the number of different plants is large. Th maximum width of a form is 22.75".

A crosstab query is read-only by default, so a form based in it too.
You would have to use (for example) the On Dbl Click event of the plant text boxes to display a popup form in which the user can update the value in the underlying table, then requery the main form.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Cross tab chart as a form?

Post by Jeff H »

Great. I'll give that a try and see how it turns out. Currently there are about 95 plants, so for the width, perhaps I could make the plant name text vertical and use the growth phase numbers instead of the text. That's how I've been collecting the data so the numbers alone are adequate.

Thanks Hans!

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Cross tab chart as a form?

Post by Jeff H »

FYI, the cross tab works well. I had not understood the grouping setup.

Then, after fiddling around a bit I realized I can get the datasheet output I want by exporting the query to Excel. There I can make a macro to easily give me the formatting and highlighting that I want to see.

As always, thanks for showing me the way.

- Jeff