Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2006
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Make Table (Access 97)

    This is a really odd question - but I'm hoping someone might know (a) what I'm talking about and ([img]/forums/images/smilies/cool.gif[/img] the answer(!)
    I've the following SQL (please see below) but I want to change it into a make table query, as I am returning more than 65,000 rows. However when I try and change the SQL to a make table query I get the following error message "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" - the only thing that has changed in my SQL to "SELECT INTO table" at the beginning of the SQL - what am I doing wrong? I have been told the problem is with UNION ALL SELECT ... thanks for your help with this, h-p



    SELECT Cambs_PCGs.PCG_Name, CambsPractices.[Practice Name], IIf(Month([Spell_Dis_Date])>3,Month([Spell_Dis_Date])-3,Month([Spell_Dis_Date])+9) AS Month, Clin_Effect.Code_Description, PatientType.Patient_Type, dbo_All_Episode_Snapshot.fin_year, Count(dbo_All_Spells_Snapshot_v35.PatPK) AS Spells, Sum(dbo_All_Spells_Snapshot_v35.spell_ep_cost) AS SumOfspell_ep_cost
    FROM (PatientType INNER JOIN (((dbo_All_Episode_Snapshot INNER JOIN dbo_All_Spells_Snapshot_v35 ON dbo_All_Episode_Snapshot.PatPK = dbo_All_Spells_Snapshot_v35.PatPK) INNER JOIN Clin_Effect ON dbo_All_Episode_Snapshot.Primary_ICD_10_Diagnostic _Code = Clin_Effect.Code) INNER JOIN Cambs_PCGs ON dbo_All_Episode_Snapshot.Calculated_PCT = Cambs_PCGs.PCG_Code) ON PatientType.Admin_Type_Code = dbo_All_Episode_Snapshot.Admin_Type_Code) LEFT JOIN CambsPractices ON dbo_All_Episode_Snapshot.Code_of_GP_Practice__Regi stered_GP = CambsPractices.Reg_Practice
    WHERE (((Clin_Effect.Code_Type)="ICD10") AND ((dbo_All_Spells_Snapshot_v35.Provider)="RGT") AND ((dbo_All_Episode_Snapshot.fin_year)>"20022003"))
    GROUP BY Cambs_PCGs.PCG_Name, CambsPractices.[Practice Name], IIf(Month([Spell_Dis_Date])>3,Month([Spell_Dis_Date])-3,Month([Spell_Dis_Date])+9), Clin_Effect.Code_Description, PatientType.Patient_Type, dbo_All_Episode_Snapshot.fin_year

    UNION ALL SELECT Cambs_PCGs.PCG_Name, CambsPractices.[Practice Name], IIf(Month([Spell_Dis_Date])>3,Month([Spell_Dis_Date])-3,Month([Spell_Dis_Date])+9) AS Month, Clin_Effect.Code_Description, PatientType.Patient_Type, dbo_All_Episode_Snapshot.fin_year, Count(dbo_All_Spells_Snapshot_v35.PatPK) AS Spells, Sum(dbo_All_Spells_Snapshot_v35.spell_ep_cost) AS SumOfspell_ep_cost
    FROM ((PatientType INNER JOIN ((dbo_All_Episode_Snapshot INNER JOIN dbo_All_Spells_Snapshot_v35 ON dbo_All_Episode_Snapshot.PatPK = dbo_All_Spells_Snapshot_v35.PatPK) INNER JOIN Cambs_PCGs ON dbo_All_Episode_Snapshot.Calculated_PCT = Cambs_PCGs.PCG_Code) ON PatientType.Admin_Type_Code = dbo_All_Episode_Snapshot.Admin_Type_Code) INNER JOIN Clin_Effect ON dbo_All_Episode_Snapshot.Primary_OPCS_Procedure_Co de = Clin_Effect.Code) LEFT JOIN CambsPractices ON dbo_All_Episode_Snapshot.Code_of_GP_Practice__Regi stered_GP = CambsPractices.Reg_Practice
    WHERE (((Clin_Effect.Code_Type)="OPCS4") AND ((dbo_All_Spells_Snapshot_v35.Provider)="RGT") AND ((dbo_All_Episode_Snapshot.fin_year)>"20022003"))
    GROUP BY Cambs_PCGs.PCG_Name, CambsPractices.[Practice Name], IIf(Month([Spell_Dis_Date])>3,Month([Spell_Dis_Date])-3,Month([Spell_Dis_Date])+9), Clin_Effect.Code_Description, PatientType.Patient_Type, dbo_All_Episode_Snapshot.fin_year

    UNION ALL SELECT Cambs_PCGs.PCG_Name, CambsPractices.[Practice Name], IIf(Month([Spell_Dis_Date])>3,Month([Spell_Dis_Date])-3,Month([Spell_Dis_Date])+9) AS Month, "Benign Skin Lesions" AS [Code Description], PatientType.Patient_Type, dbo_All_Episode_Snapshot.fin_year, Count(dbo_All_Spells_Snapshot_v35.PatPK) AS Spells, Sum(dbo_All_Spells_Snapshot_v35.spell_ep_cost) AS SumOfspell_ep_cost
    FROM (PatientType INNER JOIN ((dbo_All_Episode_Snapshot INNER JOIN dbo_All_Spells_Snapshot_v35 ON dbo_All_Episode_Snapshot.PatPK = dbo_All_Spells_Snapshot_v35.PatPK) INNER JOIN Cambs_PCGs ON dbo_All_Episode_Snapshot.Calculated_PCT = Cambs_PCGs.PCG_Code) ON PatientType.Admin_Type_Code = dbo_All_Episode_Snapshot.Admin_Type_Code) LEFT JOIN CambsPractices ON dbo_All_Episode_Snapshot.Code_of_GP_Practice__Regi stered_GP = CambsPractices.Reg_Practice
    WHERE (((dbo_All_Spells_Snapshot_v35.Provider)="RGT") AND ((dbo_All_Episode_Snapshot.fin_year)>"20022003") AND ((dbo_All_Episode_Snapshot.Primary_OPCS_Procedure_ Code) Like "S05*" Or (dbo_All_Episode_Snapshot.Primary_OPCS_Procedure_C ode) Like "S06*" Or (dbo_All_Episode_Snapshot.Primary_OPCS_Procedure_C ode) Like "S08*" Or (dbo_All_Episode_Snapshot.Primary_OPCS_Procedure_C ode) Like "S09*" Or (dbo_All_Episode_Snapshot.Primary_OPCS_Procedure_C ode) Like "S10*" Or (dbo_All_Episode_Snapshot.Primary_OPCS_Procedure_C ode) Like "S11*") AND ((dbo_All_Episode_Snapshot.Primary_ICD_10_Diagnost ic_Code) Between "D100" And "D369"))
    GROUP BY Cambs_PCGs.PCG_Name, CambsPractices.[Practice Name], IIf(Month([Spell_Dis_Date])>3,Month([Spell_Dis_Date])-3,Month([Spell_Dis_Date])+9), "Benign Skin Lesions", PatientType.Patient_Type, dbo_All_Episode_Snapshot.fin_year;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Make Table (Access 97)

    You can't change a union query into a make-table query. Instead, leave the union query as it was (selecting records), and create a new query based on the union query. Change this new query to a make-table query.

  3. #3
    New Lounger
    Join Date
    Jul 2006
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Make Table (Access 97)

    Thanks for your help with this Hans - I am running the query now - so far so good... <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    h-p

Posting Permissions

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