Solved

SQL - Error 3067 Query input must contain at least one query or input

Posted on 2010-09-07
3
515 Views
Last Modified: 2012-05-10
Trying to build a update query that will pull in new records and then group and sum them.  Keep getting error 3067 - Query input must contain at least one query or input.
Want to group on 4 fields: ADP, Corp, Fleet, and Quarter.  Quarter is generated in the function and the value is assigned using a SET command.

What is causing this error?
'9/6/10  New SQL to Group and Sum - trying to capture multiple records for same ADP/Corp/Fleet/Quarter

DoCmd.RunSQL ("INSERT INTO tblYTDCustomerSummary ( ADP, Corp, [Fleet #], TotCapCost, NewLeaseCM, ExLeaseCM, " & _

     "IBCM, NetIncome, CumulativeANI, CustomerROI, MinROI, NewLeaseFact, NewLeaseSIC, ExLeaseFact, IBFact, ExLeaseSIC, IBSIC, NewPMCM, " & _

     "ExPMCM, NewPMFact, ExPMFact, NewPMSIC, ExPMSIC, NewServicesCM, NewServicesFact, NewServicesSIC, SynCM, SynCM, SynFact, SynSIC, TotSIC ) " & _

     "SELECT tblReports_CustomerSummaryAM.ADP, tblReports_CustomerSummaryAM.Corp, tblReports_CustomerSummaryAM.[Fleet #], " & _

     "Sum(tblReports_CustomerSummaryAM.TotCapCost) AS SumOfTotCapCost, Sum(tblReports_CustomerSummaryAM.NewLeaseCM) AS SumOfNewLeaseCM, " & _

     "Sum(tblReports_CustomerSummaryAM.ExLeaseCM) AS SumOfExLeaseCM, Sum(tblReports_CustomerSummaryAM.IBCM) AS SumOfIBCM, " & _

     "Sum(tblReports_CustomerSummaryAM.NetIncome) AS SumOfNetIncome, Sum(tblReports_CustomerSummaryAM.CumulativeANI) AS SumOfCumulativeANI, " & _

     "Max(tblReports_CustomerSummaryAM.CustomerROI) AS MaxOfCustomerROI, Max(tblReports_CustomerSummaryAM.MinROI) AS MaxOfMinROI, " & _

     "Max(tblReports_CustomerSummaryAM.NewLeaseFact) AS MaxOfNewLeaseFact, Sum(tblReports_CustomerSummaryAM.NewLeaseSIC) AS SumOfNewLeaseSIC, " & _

     "Max(tblReports_CustomerSummaryAM.ExLeaseFact) AS MaxOfExLeaseFact, Max(tblReports_CustomerSummaryAM.IBFact) AS MaxOfIBFact, " & _

     "Sum(tblReports_CustomerSummaryAM.ExLeaseSIC) AS SumOfExLeaseSIC, Sum(tblReports_CustomerSummaryAM.IBSIC) AS SumOfIBSIC, " & _

     "Sum(tblReports_CustomerSummaryAM.NewPMCM) AS SumOfNewPMCM, Sum(tblReports_CustomerSummaryAM.ExPMCM) AS SumOfExPMCM, " & _

     "Max(tblReports_CustomerSummaryAM.NewPMFact) AS MaxOfNewPMFact, Max(tblReports_CustomerSummaryAM.ExPMFact) AS MaxOfExPMFact, " & _

     "Sum(tblReports_CustomerSummaryAM.NewPMSIC) AS SumOfNewPMSIC, Sum(tblReports_CustomerSummaryAM.ExPMSIC) AS SumOfExPMSIC1, " & _

     "Sum(tblReports_CustomerSummaryAM.NewServicesCM) AS SumOfNewServicesCM, " & _

     "Max(tblReports_CustomerSummaryAM.NewServicesFact) AS MaxOfNewServicesFact, " & _

     "Sum(tblReports_CustomerSummaryAM.NewServicesSIC) AS SumOfNewServicesSIC, Sum(tblReports_CustomerSummaryAM.SynCM) AS SumOfSynCM1, " & _

     "Sum(tblReports_CustomerSummaryAM.SynCM) AS SumOfSynCM, Max(tblReports_CustomerSummaryAM.SynFact) AS MaxOfSynFact, " & _

     "Sum(tblReports_CustomerSummaryAM.SynSIC) AS SumOfSynSIC, Sum(tblReports_CustomerSummaryAM.TotSIC) AS SumOfTotSIC " & _

     "SET tblYTDCustomerSummary.Quarter = " & Q & " " & _

     "FROM tblReports_CustomerSummaryAM " & _

     "GROUP BY tblReports_CustomerSummaryAM.ADP, tblReports_CustomerSummaryAM.Corp, tblReports_CustomerSummaryAM.[Fleet #] " & _

     "ORDER BY tblReports_CustomerSummaryAM.ADP")

Open in new window

0
Comment
Question by:Lambel
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 33621031
You cannot do an INSERT and an UPDATE in the same query.

Line 2 indicates (INSERT) this is an append query, but line 21 indictates (SET) an update query.

My guess is that line 21 is your problem.
0
 

Author Comment

by:Lambel
ID: 33621409
Yes, removing the SET statement avoided the error.  How can I assign the Quarter field to a value that is referenced from outside the sql?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33622095
It depends.

Is this a static value that you want to add to each of the records created by the GROUP BY clause in the original SELECT statement, or would you have a different value for each of those groupings?
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now