If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Need help with calculation
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" |
#3
|
|||
|
|||
Need help with calculation
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" |
#4
|
|||
|
|||
Need help with calculation
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" |
#6
|
|||
|
|||
Need help with calculation
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. |
Thread Tools | |
Display Modes | |
|
|