[AccessD] Append query question

First of all GET RID OF THE SPACES!!!  Spaces in object names are a no-no
for a variety of reasons.

Second do NOT use reserved words as your own object names (Date as a field
name).

Third, AFAIK you cannot use a "single" query to do what you are trying to
do.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Wednesday, May 04, 2005 11:50 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Append query question


The following append query works fine:

 

INSERT INTO [tbl Cum Oil Test] ( [Well Name], [Date], [Cum Oil] )

SELECT [tbl Monthly Oil Test].[Well Name], [tbl Monthly Oil Test].Date, [tbl
Cum Oil Test]![Cum Oil]+[tbl Monthly Oil Test]![Prod Oil] AS [New Cum]

FROM [tbl Monthly Oil Test], [tbl Cum Oil Test]

WHERE ((([tbl Monthly Oil Test]![Well Name])=[tbl Cum Oil Test]![Well
Name]));

 

However if I change the query as follows it will not run. I get a message of
"You tried to execute a query that does not include the specified expression
'Well Name' as part of an aggregate function". Is it not possible to do this
in one query?

 

INSERT INTO [tbl Cum Oil Test] ( [Well Name], [Date], [Cum Oil] )

SELECT [tbl Monthly Oil Test].[Well Name], Max([tbl Monthly Oil Test].Date),
[tbl Cum Oil Test]![Cum Oil]+[tbl Monthly Oil Test]![Prod Oil] AS [New Cum]

FROM [tbl Monthly Oil Test], [tbl Cum Oil Test]

WHERE ((([tbl Monthly Oil Test]![Well Name])=[tbl Cum Oil Test]![Well
Name]));

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