calculating additional data help

[calculating additional data help]

Hello,

I have a table with the following data below and trying to find a way to add
Spares automatically to the table whether via query, I am not sure how.

Original data:
Yr Mth Type Value
2008 1 939 30.8
2008 2 939 30.8
2008 3 939 30.8
2008 4 939 30.8
2008 5 939 30.8
2008 6 939 30.8
2008 1 940 1.5
2008 2 940 1.5
2008 3 940 1.5
2008 4 940 1.5
2008 5 940 1.5
2008 6 940 1.5
2008 1 950 5.0
2008 2 950 5.0
2008 3 950 5.0
2008 4 950 5.0
2008 5 950 5.0
2008 6 950 5.0

Add to original value:
Yr Mth Type Value
2008 1 Spare 1.9
2008 2 Spare 1.9
2008 3 Spare 1.9
2008 4 Spare 1.9
2008 5 Spare 1.9
2008 6 Spare 1.9

where Spare is 5% of the total value of 939, 940 and 950 on the same yr and
mth. Thanks

 

[Fred]

There are several quandariess / problems presented by your question if taken
literally.

In addition to the above, 99% of the time you should do calculations when
needed & not store the results.

Solution: in your output (e.g. a report) group by years and then by
months. (if there are other types that you haven't listed, also filter for
those three types) In the month footer put a control with =sum([value])*.05.


You don't have to print/display the detail if you don't want to. If you
display "value" change the name (not the control source) of the control to
something else.

Hope that helps.

 

[Stefan Hoffmann]

I have a table with the following data below and trying to find a way to add
Spares automatically to the table whether via query, I am not sure how.

Original data:
Yr Mth Type Value
2008 1 939 30.8
2008 2 939 30.8
2008 3 939 30.8
Add to original value:
Yr Mth Type Value
2008 1 Spare 1.9
2008 2 Spare 1.9

Basically a JOIN like this (SQL view) should work:

SELECT i.Yr, i.M, Nz(i.Value, 0) + Nz(o.Value, 0) AS CalcValue
FROM yourTable i
LEFT JOIN yourTable o
ON i.Yr = o.Yr AND i.Mth = o.Mth
AND i.Type <> "Spare" AND o.Type = "Spare"

This query cannot be displayed in Design View.

I assume your are storing the data in one table. Using two helper
queries makes it more transparent:

NormalValues: SELECT * FROM yourTable WHERE [Type] <> "Sparse"
SparseValues: SELECT * FROM yourTable WHERE [Type] = "Sparse"

btw, [Type] is a reserved word.


Now you can use this SQL:

SELECT n.Yr, n.M, Nz(n.Value, 0) + Nz(s.Value, 0) AS CalcValue
FROM NormalValues n
LEFT JOIN SparseValues s
ON n.Yr = s.Yr AND n.Mth = s.Mth

This SQL can be displayed by the designer in Design View.

mfG
--> stefan <--

 

[calculating additional data help]

Hello Stefan,

I put in the 1st code and it gave me this error:
"Join expression not supported"

2nd code, it error:
"Invalid SQL statement; expected 'DELETE','INSERT', 'PROCEDURE','SELECT', or
'UPDATE'."

I have a table with the following data below and trying to find a way to add
Spares automatically to the table whether via query, I am not sure how.

Original data:
Yr Mth Type Value
2008 1 939 30.8
2008 2 939 30.8
2008 3 939 30.8
Add to original value:
Yr Mth Type Value
2008 1 Spare 1.9
2008 2 Spare 1.9

Basically a JOIN like this (SQL view) should work:

SELECT i.Yr, i.M, Nz(i.Value, 0) + Nz(o.Value, 0) AS CalcValue
FROM yourTable i
LEFT JOIN yourTable o
ON i.Yr = o.Yr AND i.Mth = o.Mth
AND i.Type <> "Spare" AND o.Type = "Spare"

This query cannot be displayed in Design View.

I assume your are storing the data in one table. Using two helper
queries makes it more transparent:

NormalValues: SELECT * FROM yourTable WHERE [Type] <> "Sparse"
SparseValues: SELECT * FROM yourTable WHERE [Type] = "Sparse"

btw, [Type] is a reserved word.


Now you can use this SQL:

SELECT n.Yr, n.M, Nz(n.Value, 0) + Nz(s.Value, 0) AS CalcValue
FROM NormalValues n
LEFT JOIN SparseValues s
ON n.Yr = s.Yr AND n.Mth = s.Mth

This SQL can be displayed by the designer in Design View.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I put in the 1st code and it gave me this error:
"Join expression not supported"

There is are parenthesis missing:

2nd code, it error:
"Invalid SQL statement; expected 'DELETE','INSERT', 'PROCEDURE','SELECT', or
'UPDATE'."

Works for me.


mfG
--> stefan <--