Solved

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

Posted on 2010-09-07
3
518 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 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