Complex Form Design

[Guest]

I need to develop a complex form and am having a couple difficulties. I
normally use VFP, but this particular application requires Access, so I am
struggling a bit.

The application has do main challenges. First, the data is stored in a
normalized fashion, but needs to be displayed/edited in a pivot table format.
I.E. the table is organized like:

dept # Text 4
Month Text 3
Sales Goal Currency

The form needs to be in the format

Dept 1000
Jan Feb Mar .... Nov Dec
Sale Goal 100 150 120 .... 75 80

The other challenge is the number of columns required. The seems to be a
limit of 255 columns per query and I will need well beyond that number of
columns. I VFP, I can have multiple tables open per form, is that possible
with Access? Most of the data is for display only, so I could problably use
a dlookup functions to populate the data instead of including it in a query.

Any suggestions or tips on how to proceed will be very much appreciated.

 

[Dirk Goldgar]

(comments inline)

I need to develop a complex form and am having a couple difficulties.
I normally use VFP, but this particular application requires Access,
so I am struggling a bit.

The application has do main challenges. First, the data is stored in
a normalized fashion, but needs to be displayed/edited in a pivot
table format. I.E. the table is organized like:

dept # Text 4
Month Text 3
Sales Goal Currency

The form needs to be in the format

Dept 1000
Jan Feb Mar .... Nov Dec
Sale Goal 100 150 120 .... 75 80

If it doesn't need to be updatable, I don't see why you can't use a
crosstab query as the basis for this form.

The other challenge is the number of columns required. The seems to
be a limit of 255 columns per query and I will need well beyond that
number of columns.

Under what circumstances? Are you saying you need more than 255 columns
in the crosstab query you describe above? Or what? In your example,
you only showed twelve "month" columns.

I VFP, I can have multiple tables open per form,
is that possible with Access?

A form can contain multiple subforms, each of which displays a fully
functional form object with its own record source. That may be the way
to solve your problem. Also, subforms can be nested, under the right
circumstances.

Most of the data is for display only,
so I could problably use a dlookup functions to populate the data
instead of including it in a query.

I suppose that could be a solution, but it doesn't sound like a very
good one. If you'll describe what you're trying to achieve in a bit
more detail, we can probably tell you how to to do it with Access, or
else suggest alternatives.

 

[Guest]

Dirk,

Thanks for your feedback.

In my application, I need to display 24 months of history for a number of
business statistics, sales being just one of them. Some of the others
include inventory, markdowns, merchandise receipts, etc. There is about 10
in total. If I add the plan data to the history, if pushes me over the 255
column limit.

The other requirement is to have 12 month plans for each of these. The
plans do have to be editable. Additionally, the months are inter-related, so
for example, changing the sales plan for this month will effect the inventory
plan for next month.

The way I am leaning now is to create a de-normarlized temporary table for
the 12 month plan figures. I would popluate the table from the normalized
plan table via an append query, using expressions like iif(period = '01',
Sales, 0) when the planning form is opened. I would then have to reverse the
process of saving the changes from the temporary table to the plan table when
any changes are saved.

I agree that the using DSUM may not be the best way to go. I will try
creating subforms for the history data. I expect that I would need ten
subforms since the data needs to be presented in a manner where sales history
and sales plan are presented as a group, inventory history and plan are a
group, etc. I did not see a way to have controls linked to different data
souces on the same form. I would have the primary table include the
temporary plan table and the subforms contain the related history.

 

[Dirk Goldgar]

Dirk,

Thanks for your feedback.

In my application, I need to display 24 months of history for a
number of business statistics, sales being just one of them. Some of
the others include inventory, markdowns, merchandise receipts, etc.
There is about 10 in total. If I add the plan data to the history,
if pushes me over the 255 column limit.

I'd be inclined to have each statistic as a separate subform.

The other requirement is to have 12 month plans for each of these.
The plans do have to be editable. Additionally, the months are
inter-related, so for example, changing the sales plan for this month
will effect the inventory plan for next month.

The way I am leaning now is to create a de-normarlized temporary
table for the 12 month plan figures. I would popluate the table from
the normalized plan table via an append query, using expressions like
iif(period = '01', Sales, 0) when the planning form is opened. I
would then have to reverse the process of saving the changes from the
temporary table to the plan table when any changes are saved.

That might be a workable approach. Are you sure you can't show these
12-month plans vertically, so that the months are the rows, thereby
avoiding the need to denormalize the table? I have no idea how the plan
for one month may be related to the plan for another month for a
different statistic, so I don't know what may be necessary to "ripple" a
change through the table(s).

I agree that the using DSUM may not be the best way to go. I will try
creating subforms for the history data. I expect that I would need
ten subforms since the data needs to be presented in a manner where
sales history and sales plan are presented as a group, inventory
history and plan are a group, etc.

You could conceivably use the same form object as the source object of
multiple subform controls, just changing the recordsource at load time.

I did not see a way to have
controls linked to different data souces on the same form.

No, a form has only one recordsource, though that recordsource may be a
query that joins multiple tables.

I would
have the primary table include the temporary plan table and the
subforms contain the related history.

It sounds feasible, but the devil will be in the details.

I have to say, this sounds more like the sort of thing that Excel is
designed to do, rather than Access.