Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Insert Into Query Failure

    This is one on 6 queries . . .

    Code:
    INSERT INTO year_month_city_counts
    SELECT YEAR(closingdate) AS closing_year, MONTH(closingdate) AS closing_month, county AS county, city AS city, 'attached' AS walls, closeprice AS closeprice, COUNT(*) AS occurs, AVG(buildingsize) AS avg_size
    FROM tblCARETSData
    WHERE closingdate>=DATESERIAL([Enter Year],[Enter Month],1) And closingdate<DATEADD("m",1,DATESERIAL([Enter Year],[Enter Month],1)) And closeprice>0 And buildingsize>0 And CommonWalls Like '*attached*'
    GROUP BY YEAR(closingdate), MONTH(closingdate), county, city, closeprice;
    The values are all correct . . . closing_month / closingdate / closeprice, etc. but the year_month_city_counts table is blank - NOTHING? No errors at all.

    I'm going crazy as I can't figure it out.

    Thanks . . . Rick

  2. #2
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Rick,

    Avg(building size) is not included in your query's Group By.

    How are you running the query?

    Wayne

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I'm getting this error now . . .

    "circular reference caused by alias 'county' definition's SELECT list."

  4. #4
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Rick,

    If I recall correctly, you can't use the SAME name for an alias.

    Quirk of Access.

    Wayne

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Found the problem . . . it was the date format in the table. ClosingDate need to be a "short" format date.

    Once I discovered this (after near three days) I ran the program AND IT WORKED!

    Thanks to all for your replies!

    Rick

Posting Permissions

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