'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")
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
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.