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 access/sql server conversion

I am new to the db world. I am trying to convert an Access 2000 database to SQL Server 2000. f/e has forms, reports, macros, modules, b/e has tables which the f/e file has linked to via link manager. The b/e is on a shared network drive. A dozen users, about 150 tables. The b/e file is appoaching 1Gb. The plan is to keep the Access f/e.

I have read the microsoft Q;s on Upsizing, DTS, Access Project vs Access database, Access sv SQL, etc. I am having diffculty understanding the best approach for my situation.

Looking for some expert guidance before I jump in.

Thanx

CFWhy do you need to migrate your data hauling operation from pickup trucks to 18 wheelers in the first place? Load? Speed? Safety?

How about - just for starters - a simple/non-radical/initial approach:
Keep everything the way it is except for actual data in Access tables that could go into SQL tables instead with Access tables linked to it. This will for instance overcome one of the biggest Access limits: 2 GB per "mdb".
(As you probably know, once over 2 GB - in 2000 & 2002/XP - Access could crash at any moment with data from it never to be seen again. )|||Thanks for your quick reply.

Speed is the main thing, and there have been multiple user contention problems leading to lockup. The site is upgrading everything for speed.

When you say "move the actual data only" What's the proper method - simplest, to move the data only, i.e., how do i create the SQL tables automatically/safely (150 tables), and then weeks/days later move the Access data to the new tables?

Thanks,

CF|||The real Client-Server solution would be "by the book" conversion of Access "mdb" into Access Project (mdp) as a front end and storing data with data processing in SQL S.
This of course is easier said than done, especially on live and often used db.

What is really easy (although not a real solution) is to first setup tables in SQL S., work out minor differences of data types between Access an SQL S., run SQL S. import wizard to populate the tables and then link to them from Access. For one thing it is a quick workaround for JET limit of 2 GB per mdb since linked tables do not take lot of room, for another is getting data into SQL S. to find out stress-free any conversion issues, like for instance often with date/time fields.

I had Access run like hell this primitive way on well indexed datasets of over 50 GB.sql