[AccessD] Append Query syntax error

Mon Dec 12 13:40:44 CST 2011

I used your suggestion but was getting some duplicates. Here is the final solution I ended up with. Thanks for the help

INSERT INTO [tbl Monthly Well Count] ( [Eng Area], [Well Count] )
SELECT Count(A.PID) AS [Active Flowing Count],EngArea 
FROM (SELECT DISTINCT A.PID, EngArea 
FROM [tbl Statuses During Prior Month] AS A 
WHERE(A.Status In ("FL","FM","FH"))) 
Group by EngArea;

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
Sent: Monday, December 12, 2011 12:53 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Append Query syntax error

Doh, wait.  Sorry, I didn't really bother to try to understand WHAT you're trying to do - just looked at your syntax.  If you're trying to insert a row containing the count of active flowing wells for each EngArea, something like this should do it:

INSERT INTO
  [tbl Monthly Well Count] 
  (EngArea, [Name_Of_Column_Containing_The_Count])
  (
    SELECT
      a.EngArea,
      Count(a.PID)
    FROM
      [tbl Statuses During Prior Month] as a
    WHERE
      a.Status in ("FL","FM","FH")
    GROUP BY
      a.EngArea
  )

Where I've specified [Name_Of_Column_Containing_The_Count] above, substitute the name of the column in the destination table where you want to place the count.

Again, not sure if Access requires the subQ to be in parens.  If this chokes try removing them.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Monday, December 12, 2011 10:26 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Append Query syntax error

That is kind of what I was thinking. Trying to do something that cannot be done. Thanks for confirming it.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
Sent: Monday, December 12, 2011 11:34 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Append Query syntax error

Missed that.  You can't insert into "Count(A.PID)" of the destination table.  Think about it . . . 

Remove the Count() from around A.PID.  That should do it.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Monday, December 12, 2011 9:27 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Append Query syntax error

I tried your suggestions ( I think correctly) and still get an insert into syntax error. The cursor goes to the "(" after count on the first line when the syntax error comes up.

Thanks for the ideas.

Here is how the sql reads now
INSERT INTO [tbl Monthly Well Count] (Count(A.PID) AS [Active Flowing Count],EngArea) (SELECT DISTINCT A.PID, EngArea FROM [tbl Statuses During Prior Month] AS A WHERE(A.Status In ("FL","FM","FH")));

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
Sent: Monday, December 12, 2011 10:57 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Append Query syntax error

Try this:

1)	Lose the FROM before your subquery.
2)	Not sure whether you need the parentheses around your subquery.  Access SQL may require it, but other flavors (e.g. Oracle) don't.
3)	The GROUP BY clause was part of the main query - not the subquery - in the original.  Take that out.

HTH,

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Monday, December 12, 2011 8:18 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Append Query syntax error

The following select query works great

SELECT Count(A.PID) AS [Active Flowing Count],EngArea 
FROM (SELECT DISTINCT A.PID, EngArea 
FROM [tbl Statuses During Prior Month] AS A 
WHERE(A.Status In ("FL","FM","FH"))) 
Group by EngArea;

When I tried to make it an append query I get a syntax error. I don't see the error. Maybe someone else can

INSERT INTO [tbl Monthly Well Count](Count(A.PID) AS [Active Flowing Count],EngArea) 
FROM (SELECT DISTINCT A.PID, EngArea 
FROM [tbl Statuses During Prior Month] AS A 
WHERE(A.Status In ("FL","FM","FH"))) 
Group by EngArea;

Thanks

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799

No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com