Need help with calculation

[Gary F Shelton]

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.

 

[Bob Barrows [MVP]]

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.

 

[Gary F Shelton]

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

 

[Bob Barrows [MVP]]

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]

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 (e-mail address removed)

Regards,
Gary

 

[Bob Barrows [MVP]]

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.