12 month's of data

[grnzbra]

What you are doing is setting along the lines of creating nested IIf statements. That's not what you want to do. You want to create several IIf statements, one for each Month row. That will move the value out of the Savings column and into the correct month column.

Take a look at the SELECT statement I wrote. Each comma outside the IIf statements sets off another column. Also, inside the IIf statement, there is no "else" clause; "If the date criteria is met, put the Savings value here, otherwise, don't do anything."

 

[thart21]

SELECT tblProjectDetails.projectid, IIf(#1/1/2006#>=[cutindate] And IsNull([reduction]),([currentprice]-[newprice])*[tblVolumes].[volume],IIf(#1/1/2006#>=[cutindate],[reduction]*[tblVolumes].[volume])) AS Jan06, IIf(#2/1/2006#>=[cutindate] And IsNull([reduction]),([currentprice]-[newprice])*[tblVolumes].[volume],IIf(#2/1/2006#>=[cutindate],[reduction]*[tblVolumes].[volume])) AS Feb06, IIf(#3/1/2006#>=[cutindate] And IsNull([reduction]),([currentprice]-[newprice])*[tblVolumes].[volume],IIf(#3/1/2006#>=[cutindate],[reduction]*[tblVolumes].[volume])) AS Mar06
FROM tblVolumes INNER JOIN tblProjectDetails ON (tblVolumes.monthdate = tblProjectDetails.cutindate) AND (tblVolumes.product = tblProjectDetails.product);

I was trying to get it into one column because I thought I could then do a crosstab query and also thought it would make it easier to run queries between dates. But at this point I just need to get it done any way possible. The above code is giving me the same amount for each month no matter what so I have some work to do, but I believe this is looking better. I will continue to work on it.

Thanks

 

[grnzbra]

It looks like you second column (Feb06) will give you January values. But you do seem to have it now. Have you tried the crosstab wizard?

 

[thart21]

grnzbra,

I want to try the crosstab but can't do that until I can get all of the calculated values into one field, which I am still trying to figure out. I only seem to be able to get it to create a field for each month and not all of the values into one field to enable the crosstab query.

I keep getting "wrong number of arguments" and the "invalid syntax" message at my comma as described earlier when I try to do it all in one field.

Thanks

 

[grnzbra]

No, that's not how a crosstab works. If you have three fields, it will take all the values in one of them and break them out into columns. So if you have a YearMonth field with values like 0601, 0602, 0603 (one in each record), it will give you a column for each labeled 0601, 0602, and 0603.

Another field will be row headings. Let's say these are account numbers.
So you would have
0001
0002
0003
etc.
Now, the third value would go into the place where it's associated AccountNumber and YearMonth cross

So if account 0001 had 1,000 dollars in January, 06 and 1,500 in February, 06 and 2,000 in March, 06,

The output of the query would be something like

AccountNo.....0601......0602......0603
0001............1,000.....1,500.....2,000
etc for
0002
0003
and so forth.

You don't need to get all of them into one field of one record.

 

[thart21]

Sorry, I think I gave you the impression that I was trying to do the calculations in my crosstab query. I am trying to create one query that will give me:

projectid
totalsavings
product

The savings amount has to be calculated for every month for every project, because the volumes for each month are different.

projectid 001 will have at least 12 rows when running the query, one for each month. I then want to take that query and create the crosstab query. Bottom line is that I cannot create a crosstab query with a field for each month, right?

 

[grnzbra]

No, you can't. The crosstab will generate the column for each month for which you have a value. You need one more column, YearMonth (or something like that). I would go with Format([Date],"yyyymm")

 

[thart21]

I do have the cut in date field in my query. So I want to end up with:

projectid cutindate calculatedsavings
001 1/1/06 5000
001 2/1/06 4500
001 3/1/06 3200
001 4/1/06 1000
002 1/1/06 3000
002 2/1/06 2200

The "cutindate" field should have the date of the actual cut in of the project, i.e. 3/1/06 and every month thereafter. If it cuts in in May, then it should have rows with a calculated savings amount for 5/1/06, 6/1/06, etc.

I can then do my crosstab query to split out the cutindate field to one column for each "cutindate". I don't think I'm getting this across too well and I apologize for that, or perhaps I am just not getting it on how to get everything into one field in the first query.

 

[thart21]

Think I've finally got it.

Query 1:
SELECT tblProjectDetails.projectid, tblVolumes.volume, tblVolumes.product, tblProjectDetails.cutindate, tblVolumes.monthdate, IIf(IsNull([reduction]),([currentprice]-[newprice])*[tblVolumes].[volume],[reduction]*[tblVolumes].[volume]) AS Savings
FROM tblVolumes INNER JOIN tblProjectDetails ON tblVolumes.product = tblProjectDetails.product
WHERE (((tblVolumes.monthdate)>=[tblProjectDetails].[cutindate]));


This gave me one row for each project and monthly calculation

Crosstab query
TRANSFORM Sum(Query11.Savings) AS SumOfSavings
SELECT Query11.projectid
FROM Query11
GROUP BY Query11.projectid
PIVOT Query11.monthdate;

Thanks so very much for your time and extreme patience with me in this. I think that I had one too many joins between my tables. I had joined tblVolumes.monthid to tblProjectDetails.cutinmonth. Once I removed that and left only the "product" join, the query gave me the results I need - one row for each project and month.
Thanks again!

Toni