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
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;