Need help with calculation

SKU June08 NET LBS/CS July08 NET LBS/CS AUG08 NET LBS/CS
15010664 #error 24
#error
MCL03622 #error #error
#error
BCI00255 17 17
17
GFR40 #error #error
19
MCL03622 24 24
#error
80010094 #error 32
32

Need help with getting at a sku level the net LBS/CS. What I have is 12
months of data and in some months there is a value and in some there are not.
The good thing is that the vlaues are always the same but what I don't have
is one complete column that has the value. I think this is some sort of IIF
sttement but don't know how to write this multi dimensional statement... All
I want is one final column (query) that has one column of sku's and one
column of NET LBS/CS.

--
GS

Gary F Shelton wrote:
SKU June08 NET LBS/CS July08 NET LBS/CS AUG08 NET
LBS/CS 15010664 #error 24
#error
MCL03622 #error #error
#error
BCI00255 17 17
17
GFR40 #error #error
19
MCL03622 24 24
#error
80010094 #error 32
32

Need help with getting at a sku level the net LBS/CS. What I have is
12 months of data and in some months there is a value and in some
there are not. The good thing is that the vlaues are always the same
but what I don't have is one complete column that has the value. I
think this is some sort of IIF sttement but don't know how to write
this multi dimensional statement... All I want is one final column
(query) that has one column of sku's and one column of NET LBS/CS.

Is that your source data? You really have a column for each month? That is
not really a good database design ... and why do you show those #errors?
Those don't exist in the source data do they?

Or is that the result of your initial try to get what you want? If so, show
us a few rows of sample source data followed by the rows of results you want
from that sample data.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

The database is setup with a table called Sku's with Net LBS by 12 months. A
second Table is Sku's with Case QTY by 12 months. In my new query I match
sku to sku and then I divide Net LBS month 1 by case QTY month 1 and I did
that for all tweleve months. Some months have null fields so the calculation
errors out. Also I can't just sum the fields as it would then sum the null
fields to zero and make my netlbs per case incorrect. So now that I have the
12 months of data all I need is a final column with the at least one of the
netlbs/cs so that I know what the sku netlbs/cs is..... Hope this helps...
GFS
--
GS


"Bob Barrows [MVP]" wrote:

Gary F Shelton wrote:
SKU June08 NET LBS/CS July08 NET LBS/CS AUG08 NET
LBS/CS 15010664 #error 24
#error
MCL03622 #error #error
#error
BCI00255 17 17
17
GFR40 #error #error
19
MCL03622 24 24
#error
80010094 #error 32
32

Need help with getting at a sku level the net LBS/CS. What I have is
12 months of data and in some months there is a value and in some
there are not. The good thing is that the vlaues are always the same
but what I don't have is one complete column that has the value. I
think this is some sort of IIF sttement but don't know how to write
this multi dimensional statement... All I want is one final column
(query) that has one column of sku's and one column of NET LBS/CS.

Is that your source data? You really have a column for each month? That is
not really a good database design ... and why do you show those #errors?
Those don't exist in the source data do they?

Or is that the result of your initial try to get what you want? If so, show
us a few rows of sample source data followed by the rows of results you want
from that sample data.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Use Nz to take care of the Nulls (look it up in online help), but I'm still
at a loss as to how your table is set up so I cannot help as yet with your
final column. A "picture" would really help. Sample rows of source data
followed by desired results using that sample data.

Gary F Shelton wrote:
The database is setup with a table called Sku's with Net LBS by 12
months. A second Table is Sku's with Case QTY by 12 months. In my
new query I match sku to sku and then I divide Net LBS month 1 by
case QTY month 1 and I did that for all tweleve months. Some months
have null fields so the calculation errors out. Also I can't just sum
the fields as it would then sum the null fields to zero and make my
netlbs per case incorrect. So now that I have the 12 months of data
all I need is a final column with the at least one of the netlbs/cs
so that I know what the sku netlbs/cs is..... Hope this helps... GFS

Gary F Shelton wrote:
SKU June08 NET LBS/CS July08 NET LBS/CS AUG08 NET
LBS/CS 15010664 #error 24
#error
MCL03622 #error #error
#error
BCI00255 17 17
17
GFR40 #error #error
19
MCL03622 24 24
#error
80010094 #error 32
32

Need help with getting at a sku level the net LBS/CS. What I have is
12 months of data and in some months there is a value and in some
there are not. The good thing is that the vlaues are always the same
but what I don't have is one complete column that has the value. I
think this is some sort of IIF sttement but don't know how to write
this multi dimensional statement... All I want is one final column
(query) that has one column of sku's and one column of NET LBS/CS.

Is that your source data? You really have a column for each month?
That is not really a good database design ... and why do you show
those #errors? Those don't exist in the source data do they?

Or is that the result of your initial try to get what you want? If
so, show us a few rows of sample source data followed by the rows of
results you want from that sample data.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

This message board would not let me past in a .bmp but here is some sample
data that I shrank down... I took a few columns out of the example but do
note that there are 12 columns... So the iff(statement) needs to account for
12 parts of the equation.

SKU_CD Field_1 Field_2 Field _3 Field_4 Field_5 Field_6
Answer
15010664 8 8 8 0 0 0 8
15010665 8 0 8 0 0 0 8
15010667 0 12 12 0 0 0 12
15010669 0 0 0 0 0 0 24
15010677 0 0 0 0 0 32 32
15010678 0 16 16 0 0 0 16
15010697 0 0 0 25 25 0 25

If you would like an excel file I can email one to someone.. Please feel
free to email me at

Regards,
Gary



--
GS


"Bob Barrows [MVP]" wrote:

Use Nz to take care of the Nulls (look it up in online help), but I'm still
at a loss as to how your table is set up so I cannot help as yet with your
final column. A "picture" would really help. Sample rows of source data
followed by desired results using that sample data.

Gary F Shelton wrote:
The database is setup with a table called Sku's with Net LBS by 12
months. A second Table is Sku's with Case QTY by 12 months. In my
new query I match sku to sku and then I divide Net LBS month 1 by
case QTY month 1 and I did that for all tweleve months. Some months
have null fields so the calculation errors out. Also I can't just sum
the fields as it would then sum the null fields to zero and make my
netlbs per case incorrect. So now that I have the 12 months of data
all I need is a final column with the at least one of the netlbs/cs
so that I know what the sku netlbs/cs is..... Hope this helps... GFS

Gary F Shelton wrote:
SKU June08 NET LBS/CS July08 NET LBS/CS AUG08 NET
LBS/CS 15010664 #error 24
#error
MCL03622 #error #error
#error
BCI00255 17 17
17
GFR40 #error #error
19
MCL03622 24 24
#error
80010094 #error 32
32

Need help with getting at a sku level the net LBS/CS. What I have is
12 months of data and in some months there is a value and in some
there are not. The good thing is that the vlaues are always the same
but what I don't have is one complete column that has the value. I
think this is some sort of IIF sttement but don't know how to write
this multi dimensional statement... All I want is one final column
(query) that has one column of sku's and one column of NET LBS/CS.

Is that your source data? You really have a column for each month?
That is not really a good database design ... and why do you show
those #errors? Those don't exist in the source data do they?

Or is that the result of your initial try to get what you want? If
so, show us a few rows of sample source data followed by the rows of
results you want from that sample data.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Gary F Shelton wrote:
This message board would not let me past in a .bmp

Good, because I wanted to have text so I could copy/paste and play with
it.


but here is some
sample data that I shrank down... I took a few columns out of the
example but do note that there are 12 columns... So the
iff(statement) needs to account for 12 parts of the equation.

SKU_CD Field_1 Field_2 Field _3 Field_4 Field_5
Field_6 Answer
15010664 8 8 8 0 0 0 8
15010665 8 0 8 0 0 0 8
15010667 0 12 12 0 0 0 12
15010669 0 0 0 0 0 0 24
15010677 0 0 0 0 0 32 32
15010678 0 16 16 0 0 0 16
15010697 0 0 0 25 25 0 25


OK, now show the results you wish to obtain from this data. I reread
your previous explanation and am still having problems following it.

I think one of your problems is the poor design. You should have a
single table with 5 columns based on what I think I understand from your
description:

SKU_CD Mth Yr NetLbs CaseQty
15010664 1 2008 8 ?
15010664 2 2008 8 ?

I don't really know your application so I may be off-base. However,
given that you want to sum columns, you should already be realizing that
summing rows is much simpler.



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.