Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009

    Unanswered: Access to SQL Crosstab

    At work I have been given the project of converting an Access database to SQL server. (This is literally my first project and when I explained to them I've never done SQL and that the only coding I know is some JAVA and HTML, the only response was "You can pick it up.").

    Now, I have been doing a lot of work and have accomplished a lot, moving data, creating views, linking tables via ODBC, etc. Work still wants to use Access as a front end which is why I am using views because it is built on forms that need to be updatable and that's what Google told me to do.

    I have run into a situation. We have two crosstab queries that I do not know how to convert to SQL due to T-SQL not being able to use TRANSFORM, val, and NZ. Quite honestly I am not even 100% sure what any of those commands actually do. Any help would be appreciated.

    These are the two codes from Access.

    TRANSFORM val(NZ(Sum(dbo_q_earned_assigned_this_year.earned),0)) AS SumOfearned
    SELECT dbo_q_earned_assigned_this_year.centerID, dbo_q_earned_assigned_this_year.EMIS_ID, val(NZ(Sum(dbo_q_earned_assigned_this_year.earned),0)) AS [Total Assigned_Earned]
    FROM dbo_q_earned_assigned_this_year
    GROUP BY dbo_q_earned_assigned_this_year.centerID, dbo_q_earned_assigned_this_year.EMIS_ID
    PIVOT dbo_q_earned_assigned_this_year.Department;

    TRANSFORM val(NZ(Sum(dbo_q_earned_prior_year.earned),0)) AS SumOfearned
    SELECT dbo_q_earned_prior_year.EMIS_ID, dbo_q_earned_prior_year.centerID, val(NZ(Sum(dbo_q_earned_prior_year.earned),0)) AS Total_Prior_earned
    FROM dbo_q_earned_prior_year
    GROUP BY dbo_q_earned_prior_year.EMIS_ID, dbo_q_earned_prior_year.centerID
    PIVOT dbo_q_earned_prior_year.Department;

  2. #2
    Join Date
    Jan 2003
    Nottinghamshire, UK
    TSQL Does'nt have Transform/Pivot table native ability.

    I'm afraid your in a world of pain - there are ways but they're quite complex - this is an often asked question.

    Don't know about SQL 2008 though - try googling for more info

    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Nov 2004
    Provided Answers: 4
    I would only convert the Access tables and queries to MSSQL tables and views.
    Then make those MSSQL tables and views accessible in Access through linked tables and keep the reporting front end in Access. Problem solved.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2004
    One Flump in One Place
    Agreed re using Access for the transformations. This isn't a big deal - just keep in your mind that you will shunt all data retrieval and storage to SQL Server but anything to do with displaying the data should be done by Access.

    BTW - val in the above does nothing. For NZ look up COALESCE().
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Thanks for the replies. Since the crosstabs are using the linked tables the only thing I will have to change is q_ to dbo_q_ because of the schema from SQL, correct?
    Last edited by apurgert; 07-30-09 at 13:58.

  6. #6
    Join Date
    Jul 2009
    Now next question. If I leave the crosstabs in Access can SQL use them? For some reason we have other queries that reference the crosstabs. For example:

    SELECT tbl_student_record.EMIS_ID, tbl_student_record.Last, tbl_student_record.First, IIf(IsNull([q_earned_prior_year_Crosstab]![Total_Prior_earned]),0,[q_earned_prior_year_Crosstab]![Total_Prior_earned]) AS Total_Prior_earned, IIf(IsNull([q_earned_assigned_this_year_Crosstab]![Total Assigned_Earned]),0,[q_earned_assigned_this_year_Crosstab]![Total Assigned_Earned]) AS [Total Assigned_Earned], [Total_Prior_earned]+[Total Assigned_Earned] AS Total_earned, IIf(IsNull([q_earned_assigned_this_year_Crosstab]![English]),0,[q_earned_assigned_this_year_Crosstab]![English])+IIf(IsNull([q_earned_assigned_this_year_Crosstab]![Mathematics]),0,[q_earned_assigned_this_year_Crosstab]![Mathematics])+IIf(IsNull([q_earned_assigned_this_year_Crosstab]![Science]),0,[q_earned_assigned_this_year_Crosstab]![Science])+IIf(IsNull([q_earned_assigned_this_year_Crosstab]![Social Studies]),0,[q_earned_assigned_this_year_Crosstab]![Social Studies])+IIf(IsNull([q_earned_prior_year_Crosstab]![English]),0,[q_earned_prior_year_Crosstab]![English])+IIf(IsNull([q_earned_prior_year_Crosstab]![Mathematics]),0,[q_earned_prior_year_Crosstab]![Mathematics])+IIf(IsNull([q_earned_prior_year_Crosstab]![Science]),0,[q_earned_prior_year_Crosstab]![Science])+IIf(IsNull([q_earned_prior_year_Crosstab]![Social Studies]),0,[q_earned_prior_year_Crosstab]![Social Studies]) AS Total_core
    FROM (q_earned_assigned_this_year_Crosstab RIGHT JOIN tbl_student_record ON q_earned_assigned_this_year_Crosstab.EMIS_ID = tbl_student_record.EMIS_ID) LEFT JOIN q_earned_prior_year_Crosstab ON tbl_student_record.EMIS_ID = q_earned_prior_year_Crosstab.EMIS_ID;
    That was copied right out of Access (the SQL view). Would it be bad to run this in the frontend also? I just don't want it to be too slow. According to my manager this query is used rarely so as long as it isn't taking 10 minutes to load it is ok.


  7. #7
    Join Date
    Jul 2009
    Good news.

    When referencing the cross tab from another query it runs smoothly. I have finished moving everything over to views and am about to start linking everything to SQL today.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts