1

Topic: Line TOTAL with request

Friends,  all!
Help, please, with idea implementation
Printed all in Word and saved as a picture - so it will be more clear.
Head already almost "broke"

2

Re: Line TOTAL with request

It .

3

Re: Line TOTAL with request

Akina, yes you are right, but is as is

4

Re: Line TOTAL with request

Normalize the data here in such format:
Date, Number, Range, Format, Boxes
22.07.2017,1,8-9,130,123
22.07.2017,1,9-10,130,345
22.07.2017,1,10-11,130,200
...,...,...,...,...
22.07.2017,2,11-12,100,250
Well or at once take product of Format*Korobki:
Date, Number, Range, Format_Korobki
22.07.2017,1,8-9,15590
22.07.2017,1,9-10,44850
22.07.2017,1,10-11,26000
...,...,...,...,...
22.07.2017,2,11-12,25000
Well and further it is stupid

SELECT Date, Number, the Range, SUM (_/1000*8*60)/IIF (Nomer=1; 524; 234)
FROM [the converted table]
GROUP BY Date, Number, the Range

Further, if it would be desirable, it is possible to contract again by means of TRANSFORM/PIVOT

5

Re: Line TOTAL with request

Akina, it is made so specially - for display in such type and fillings
Request to convert in your variant it is possible? Cross-tab query tried - it is impossible to transpose.

6

Re: Line TOTAL with request

Citizen_avaya wrote:

the Head already almost "broke"

Naturally, super wise formulas, especially for reading your question
It is possible to assume
--Weight of unit 130/1000
--Weight of boxes of 123 barks * *
Further I
Division on 524 both 234 and multiplication on 60 - could not understand

7

Re: Line TOTAL with request

Citizen_avaya wrote:

Cross-tab query

Here Tried reverse operation is necessary. But Access does not know about UNPIVOT, it is necessary to do it "manually".

Citizen_avaya wrote:

it is made so specially - for display in such type and fillings

It is very bad, when display influences structure, rather the reverse.

8

Re: Line TOTAL with request

THE PENSIONER,
TRULY
It is possible to assume
--Weight of unit 130/1000
--Weight of boxes of 123 barks * *
Further I
Division on 524 both 234 and multiplication on 60
524 and 234 is a maximum of boxes for 60 mines

9

Re: Line TOTAL with request

Citizen_avaya;
And dividing weight of boxes on (boxes in mines)
Let's receive weight a minute
---
Can now sound that from itself the task in normal presentation Russian words represents

10

Re: Line TOTAL with request

The pensioner!
smile))

11

Re: Line TOTAL with request

wrote:

can now sound....

also it is desirable in a format mp3

12

Re: Line TOTAL with request

: Tasks solve somehow so:
Tables-directories:
-The description
-Intervals: from; to
And principal in which all is considered the user function in dependence as I understood, from weight and the description

13

Re: Line TOTAL with request

Perhaps something like it?

SELECT * FROM Request
UNION ALL
SELECT TOP 1 Date, "Total";
((DLookUp ("[8-9]", "Request", "the Description ="" the Box 1 "" ") * (DLookUp ("[8-9]","Request"," the Description = "" the Format 1 "" ")/1000) *8)/524) *60 +
((DLookUp ("[8-9]", "Request", "the Description ="" the Box 2 "" ") * (DLookUp ("[8-9]","Request"," the Description = "" the Format 2 ")/1000) *8)/234) *60;
((DLookUp ("[9-10]", "Request", "the Description ="" the Box 1 "" ") * (DLookUp ("[9-10]","Request"," the Description = "" the Format 1 "" ")/1000) *8)/524) *60 +
((DLookUp ("[9-10]", "Request", "the Description ="" the Box 2 "" ") * (DLookUp ("[9-10]","Request"," the Description = "" the Format 2 "" ")/1000) *8)/234) *60;
((DLookUp ("[10-11]", "Request", "the Description ="" the Box 1 "" ") * (DLookUp ("[10-11]","Request"," the Description = "" the Format 1 "" ")/1000) *8)/524) *60 +
((DLookUp ("[10-11]", "Request", "the Description ="" the Box 2 "" ") * (DLookUp ("[10-11]","Request"," the Description = "" the Format 2 "" ")/1000) *8)/234) *60;
((DLookUp ("[11-12]", "Request", "the Description ="" the Box 1 "" ") * (DLookUp ("[11-12]","Request"," the Description = "" the Format 1 "" ")/1000) *8)/524) *60 +
((DLookUp ("[11-12]", "Request", "the Description ="" the Box 2 "" ") * (DLookUp ("[11-12]","Request"," the Description = "" the Format 2 "" ")/1000) *8)/234) *60
FROM Request

Probably, it is necessary to enter handling of empty values still.
To put down in proper places Nz or checks to fulfill.

14

Re: Line TOTAL with request

Comma superfluous in the first line of request.
Between Date and "Total".

15

Re: Line TOTAL with request

[quote = __ Michelle] Probably, it is necessary to enter handling of empty values still.
To put down in proper places Nz or checks to fulfill.

But boxes and formats it can be far not 2