1

Topic: It was tangled over a solution of a problem on automation

Hello!
I here was a little tangled. I ask to prompt.
There is a problem.
There are some excel files with difficult tables. Where File B is formed of file A, the File With is formed of file A, and file D is formed of file B. Files among themselves  either laborious handwork, or macroes by means of comparison. But if the structure it is necessary to change in all files changes. Also it is necessary to do carefully and not to break the formula. It very much . And sometimes it is necessary to form of these files files E, F and G. Therefore I consider that it is necessary to enter all in one place and proceeding from it to form the necessary excel files.

The possible decision.

In Access there are the given tables "Elements" and "Subelements". Tablitsa1 is formed Of these two tables with the necessary structure dataless on months.
Further Tablitsa1 it is deduced on the special form for review and editing. It should be deduced as in Excel th file with the intermediate lines. That is created the empty table with the necessary structure. Further it is necessary to enter manually the data those lines which do not have descendants (in excel th file dark blue italics). At those lines which descendants are formulas are stitched.
Clearly that in Access-tablitse1 in the intermediate lines there are no formulas as is registered in Excel th file. Therefore it is important to consider the following:
1. What that of Elements have Subelements, and some - miss. Excel th file see.
2. On the form there should be button "Save". After entered the data into lines which do not have descendant, it is pushed the button and will be saved the table.
3. Later this the table will be adjusted. Opens on this special form. Immediate adjustments. If there were adjustments in some cells - is painted over.
4. Probably there will be a necessity to correct structure. That is the data imported, but it is necessary to add a line without loss of the gated in data and for the further adjustment.
5. The next year  the similar table, but is probably a little with the changed structure, for example in "Elements" the hierarchy changed or in "Subelements" the subelement was added still. The table for previous year is saved in the general table.
I try to think as to implement. Similar here I not correctly started to do. I put Access th file. I feel that it is necessary to do on another. Prompt, how here it is necessary to move? Who can that had similar experience. How the data should be edited? The intermediate tables are probably necessary what that. In Access that really to implement?

2

Re: It was tangled over a solution of a problem on automation

3

Re: It was tangled over a solution of a problem on automation

ferzmikk wrote:

4. Probably there will be a necessity to correct structure. That is the data imported, but it is necessary to add a line without loss of the gated in data and for the further adjustment.

Your problem that you gave to levels faceless names (element/pod'element)
It would be easier to you to understand the task at semantic names
For example
Factory expenditures, article, sort field
, Electricity, 10
, Water, 30
, Transport, 20
....,;
Water, hot, 10
,Cold, 20
,Distilled, 21
Water cold, technical, 40
,Drinking, 30
Totals collect on hierarchy, and are printed on sorting (always it is possible to add a line in the middle or to rearrange)

4

Re: It was tangled over a solution of a problem on automation

EAV (at will) + the hierarchical reference manual () +  account system of an output in Excel,
Allowing on basis before the described several templates to build set of reports, type:
1. The list of templates and the reports constructed on their basis (behind daggers):

5

Re: It was tangled over a solution of a problem on automation

2. The report data:

6

Re: It was tangled over a solution of a problem on automation

3. The given lines (=)

7

Re: It was tangled over a solution of a problem on automation

4. The given lines (=)

8

Re: It was tangled over a solution of a problem on automation

And as a result all is spat out in book Excel on the given template to algorithms, formats, etc.
With the criteria/options installed by the report.
Somehow so.

9

Re: It was tangled over a solution of a problem on automation

wrote:

it is passed...
Your problem that you gave to levels faceless names (element/pod'element)
It would be easier to you to understand the task at semantic names

Here the logician such that some elements have subelements. And these subelements repeat in some other elements. The screenshot see.

10

Re: It was tangled over a solution of a problem on automation

ferzmikk wrote:

Here the logic such that at some elements is subelements. And these subelements repeat in some other elements. The screenshot see.

Subelement 6 from 1.1 and 1.2 are different concepts though their name can coincide
Though I had stat-forms when ordinary lines entered into different total lines
For example the subelement 6 of all elements was added in the end of the report at line
--Total on an element 6...............
--In percentage by the line ALL

11

Re: It was tangled over a solution of a problem on automation

Predeclared;
On screenshots it is approximately clear. But it is not clear as the Given lines (=) and (=) that is as draw out the necessary data work the Given lines. Whether there is a link or a similar example to understand logic and how works, what data structure?

ferzmikk wrote:

4. Probably there will be a necessity to correct structure. That is the data imported, but it is necessary to add a line without loss of the gated in data and for the further adjustment.

And it is important to consider such logical nuance

12

Re: It was tangled over a solution of a problem on automation

Similar it is necessary to do so.
We have reference tables "Elements" and "Subelements". there is the intermediate reference table where Year, Elements and Subelements is underlined. At this intermediate table there is a necessary structure of elements with the saved links to certain year. It is necessary to consider that in different years what that Elements and-or Subelements are present or miss.
If that that I do not consider or not correctly I do, correct me.

13

Re: It was tangled over a solution of a problem on automation

wrote:

it is passed...
Subelement 6 from 1.1 and 1.2 are different concepts though their name can coincide

Then what data structure should be in this case?

14

Re: It was tangled over a solution of a problem on automation

Prompt, what tables in Access for the given task should be?

15

Re: It was tangled over a solution of a problem on automation

In the elementary case, it is enough of it:

16

Re: It was tangled over a solution of a problem on automation

Predeclared wrote:

In the elementary case, it is enough of it:

At first forms by Spravochnik_Elementy and Spravochnik_Podelementy . Then Dokument_Elementy and Dokument_Podelementy which are set for certain years. And further Dokument_ElementyIPodelementy where for for Elements which should have descendants, Subelements for certain years are anchored. On a screenshot the data scheme is specified.

17

Re: It was tangled over a solution of a problem on automation

18

Re: It was tangled over a solution of a problem on automation

It seems in the table to Dokument_ElementyIPodelementy to communication incorrectly specified

19

Re: It was tangled over a solution of a problem on automation

It is enough of that I above represented for storage of the initial data of your task.

20

Re: It was tangled over a solution of a problem on automation

Predeclared wrote:

it is enough of that I above represented For storage of the initial data of your task.

How much I correctly understood, in one table as the reference manual we create elements (including subelements), and in the second as the document we specify year, we select elements (subelements) and we put values on months.

21

Re: It was tangled over a solution of a problem on automation

ferzmikk wrote:

..., in one table as the reference manual we create elements (including subelements)...

Yes.

ferzmikk wrote:

... We specify year, we select elements (subelements) and we put values on months.

No.
Values it is entered or for specific dates of expenditures if such detailing is necessary;
Or the aggregated values for a month for the last date month.
(I  simultaneously both variants therefore as for a part of expenditures specific dates are important)
Year and month from date in reports easily turn out regular tools from values of dates of expenditures.

22

Re: It was tangled over a solution of a problem on automation

Predeclared wrote:

it is passed...
No.
Values it is entered or for specific dates of expenditures if such detailing is necessary;
Or the aggregated values for a month for the last date month.
(I  simultaneously both variants therefore as for a part of expenditures specific dates are important)
Year and month from date in reports easily turn out regular tools from values of dates of expenditures.

On dates it is not necessary, on months.
Year, Month, Element (Subelement), Value
2017, January, the Element 1, 10
2017, January, the Element 1.1, 20
...,...,...,...
2017, February, the Element 1, 30
So?

23

Re: It was tangled over a solution of a problem on automation

Predeclared wrote:

... Year and month from date in reports easily turn out regular tools from values of dates of expenditures.

What not clear in this sentence?

24

Re: It was tangled over a solution of a problem on automation

Now if to deduce the table on the special form, you are deduced on the normal tabular form, not TreeView. Descendants have indents for convenience of perception of the hierarchical table. If there are parent lines they are not accessible to input. If the total on finite the descendant color of a cell changes is entered and there is a recalculation in parent lines as dependent lines.
For an output and adjustments of the data it is deduced on object of type the subordinate form?
How to set formulas for dependent lines and how will be c formulas on the form? For formation of formulas for dependent lines enough to be guided by the parent? Whether there is a similar link or an example for learning?

25

Re: It was tangled over a solution of a problem on automation

By operation with databases, including in Access;
It is necessary to get rid of all plane Excel-like working methods.
It is necessary to divide methods accurately: data storage, data input, extraction of the data and data mapping in reports.
You all time try to mix in uniform Excel - the similar decision and storage, both input, and an output.