I am generating a report from access using sql query. Attached is query. However, the retrives only three columns they are
EMS Line Item
No of Errors
I need two more columns named “DPMO*16” and “DPMO*8”. “DPMO*16” column value is the multiplication on “Entered Invoices” value * 16 and “DPMO*8” column value is the multiplication on “EMS Line Item” value * 8. Also, I require the sum of “DPMO*16” and “DPMO*8”.
I have attached the excel file for your better understanding.
Please help me. I have also attached the current sql query. Please help me on this.
strSQL = "DELETE FROM SummaryReport"
strSQL = "INSERT INTO SummaryReport ( Clients, [Entered Invoices], [EMS Line Item], [No Of Errors] ) " _
& "SELECT ClientName.ClientName AS Clients, Count(QCLog.Noli) AS [Entered Invoices], " _
& "Sum(QCLog.Noli) AS [EMS Line Item], Sum(QCLog.NumofErr) as [No of Errors] " _
& "FROM ClientName LEFT JOIN QCLog ON ClientName.ClientName = QCLog.ClientName " _
& " WHERE (([QCLog].[CheckedDate]) >= # " & st1 & " #) AND (([QCLog].[CheckedDate]) <= # " & st2 & " #) GROUP BY ClientName.ClientName;"
On Error GoTo errorhandler
DoCmd.OutputTo acOutputTable, "SummaryReport", acFormatXLS