asked on

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

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

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lambel

ASKER

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?

Dale Fye

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?