Search code examples
sqlsubqueryms-access-2007create-table

Subqueries as the input to the MS Jet CREATE TABLE statement


I am trying to write a CREATE TABLE sql that pulls its columns and data from a subquery (basic Select with an Aggregate MAX function). I've tested the subquery by itself and it works fine, but once i put it into the CREATE TABLE MapData AS ... it errors out. Any advice? or is this not even possible in MS Access?

Thanks, Stacy


Solution

  • Access SQL's CREATE TABLE Statement doesn't provide what I think you're looking for. Consider SELECT…INTO instead.

    A very simple example ...

    SELECT
        fld1,
        fld2,
        etc
    INTO NewTable
    FROM OldTableOrQuery;
    

    If you run into trouble with that approach, show us the query from which you want to create a table. Meanwhile, here's another example which you may find useful ...

    SELECT
        sub.FacNo,
        sub.QualRating AS Qrate,
        sub.MaxOfSqFeet AS sqFeet
    INTO MapData
    FROM
        (
            SELECT FACNO, QualRating, MAX(SqFeet) AS MaxOfSqFeet
            FROM April_2_ISR_report
            GROUP BY FACNO, QualRating
        ) AS sub;