Please Help me Conversion CrossTab Access to SQL Server 2000 Query

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

Please Help me

Thank You

Hi Sam,

Today, my recommendation is the same one that I gave you yesterday. Click here to check it out. Several folks have taken their time and given you suggestions -and you don't seem to be learning from those suggestions.

In fact, I'll post it again:

I suggest that you take the time to work through the suggestions that have been provided to your previous posts until you understand the process.

When you work through the process and begin to understand the process, you will be able to work these out for yourself. (Otherwise, we might think that you are just trying to get folks to do your work for you. And that probably wouldn't be fair to you ...)

|||not understand the process Function TRANSFORM and PIVOT?please help me

thank you

|||

Transform is NOT a Transact-SQL keyword -in this situation, you can probably safely ignore it.

PIVOT is a SQL 2005 keyword that displays rows as columns.

You 'should' have a copy of SQL Server 2005 Books Online. You can get a lot of good information from that source.

A couple of folks indicated to you in your other posts that IIF was not a SQL function (it is an Access function), and they offered suggestions about how to turn IIF functions into CASE structures. Perhaps looking in Books Online and reading about using CASE would be useful.

I, for one, am willing to assist those that seem intent on learning and helping themselves. BUT I get annoyed to repeated be asked to do someone's work for them -UNLESS, of course, I'm getting paid for it.

Just in case others here are not so compelled as I, I shall refrain from additional comments on your posts. Good luck.

|||

This query will help you...

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join OILPALM as [?] On [?].Year=? and [?].NAME=[Main].NAME '
Select @.PreparedSelectQuery =',Count([?].BLOCK) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From OILPALM Group By Year

Exec ('Select [Main].NAME' + @.SelectQuery + ' From (Select Distinct NAME From OILPALM) as [Main]' + @.JoinQuery + ' Group By [Main].NAME')

|||This query help me....Thank you so much...

Please Help Beginners

The followong statement is working

iif ([Measures].[Docsum]>500, "1","0")

But there is a need to count these Docsums
so
iif ([Measures].[Docsum]>500, Count[Docsum],"0")

but not this way
Can somebody help and explains, how to Count these sums
Thank'scan the count or sum wrap around the iif-statement?

Please correct syntax

hi there

RS2005 - Layout tab

I have a field that says

txtTrackerTrue=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I want another field that says

txtAllTrue=Sum(Iif ((Fields!Tracker.Value, ClosedBy.Value = Fields!RecvdBy, 1,0 ))

obviously this is going to error

how would I do this please

thanks

jewelfire,

I am not 100% what you are trying to get help with here.

I assume you have a field (txtTrackerTrue) that has the expression (starting with an equal sign)

I think you need a double equals sign like this...

=Sum(Iif (Fields!Tracker.Value == Fields!RecvdBy.Value , 1,0 ))

In your second example you were missing closing paren. and I am not sure what you are trying to do,

Can you explain in words what you are wanting to do in your second example and I will try to help you out!

Thanks

|||

Hi,

you are missing a ")" before the 1,0.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

hey there thanks for jumping in, Reading back I didn't explain myself very well at all - sorry about that

RS2005 - layout tab

Name of txt box = txtTrackerTrue

Value of this txt box = Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

This is correct - this is just for guidelines.

I need another field

Lets call the txt box = txtAllTrue

Value of this txt box to be

Sum If Tracker/RecvdBy/ClosedBy all the same then true.

I thought if I could reference the 1st txt box as part of the sum is already done and then just add the closedby into the equasion?

But not sure how to reference a txt box.

thanks

Jewel

|||

Hi,

You will have to use the prefix ReportItems!SomeItem to use the value of that field, I am not sure if that works for expressions, but It hink the processor will take care of the informationen and the precendence that the expression has to be evaluated.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

thanks Jens

I tried that but it came up with

ReportItem is a type and cannot be used as an expression.

any other suggestions?

cheers

|||You need to use the Value property on the ReportItem. For example, ReportItems!txtTrackerTrue.Value|||

thanks Ian

I thought I could as well but as mentioned when Jens suggested same I am getting an error (Report Item is a type and cannot be used as an expression)

this is what I need to do

=Sum(Iif ((ReportItem!txtTrackerTrue.Value = Fields!ClosedBy.Value), 1,0 ))

with ReportItem!txtTrackerTrue - already a combination of two fields being equal.

cheers

Jewel

|||Jens suggested that you do the same thing, but Jens did not mention that you need to use the .Value property. Make sure that the .Value property is used on the ReportItem, otherwise you will get the this error.

Ian|||

thanks but still get the error with using the .Value

cheers

J

|||

I noticed that in the expression "ReportItem" is used, try using "ReportItems" instead.

Ian

|||

thanks Ian

yes I tried that as well - I noticed that and thought I would give it a go - error is:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

so am just trying to see if there is anything else I can do.

cheers for your help though

jewel

|||

hey there

thanks all for participating but I still have not solved this problem. Is this something I cannot do?

so I have a text box called txttrackertrue

the expression in this box is

=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I have another text box called txtClosedtrue

the expression needed for this box is

=Sum(Iif ((ReportItems!txtTrackerTrue.Value = Fields!ClosedBy.Value), 1,0 ))

error is

[rsAggregateReportItemInBody] The Value expression for the textbox 'txtClosedTrue' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsReportItemReference] The Value expression for the textbox ‘txtClosedTrue’ refers to the report item ‘txtTrackerTrue’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

any suggestions please.

thanks

jewel

|||
Where is the textbox 'txtClosedtrue' in relation to the textbox 'txtTrackerTrue'? The textbox 'txtClosedtrue' needs to be in the same scope or a child scope of the scope that contains the textbox 'txtTrackerTrue'. In other words, textboxes can only refer to other text boxes above them in the grouping hierarchy--Only one-to-one or many-to-one relationships can exist.

Also, you cannot refer to textboxes in an Aggregate function in the body of the report. This is only allowed in the page header and footer.

Ian|||

thanks Ian for your input

both text boxes are on a details line of a table - there is no group in this table

txtTrackerTrue comes before txtClosedTrue

I am presuming by Aggregate you mean the (sum) part.

so if this is so is there another way I should do this then?

cheers

Jewel

sql