Solved

Muliplication of two columns

Posted on 2010-09-22
9
351 Views
Last Modified: 2013-11-28
Hi,
I am generating a report from access using sql query. Attached is query. However, the retrives only three columns they are

Entered Invoices
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.

Regards,
Prashanth

strSQL = "DELETE FROM SummaryReport"

CurrentDb.Execute strSQL

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;"

CurrentDb.Execute strSQL

On Error GoTo errorhandler

DoCmd.SetWarnings False

DoCmd.OutputTo acOutputTable, "SummaryReport", acFormatXLS

Open in new window

SummaryReport.xls
0
Comment
Question by:pg1533
  • 5
  • 3
9 Comments
 
LVL 7

Expert Comment

by:klakkas
ID: 33732508
Add these 3 lines in your select:
& " Count(QCLog.Noli) * 16 AS [DPMO_16], " _
& " Count(QCLog.Noli) * 8 AS [DPMO_8], " _
& " (Count(QCLog.Noli) * 16) + (Count(QCLog.Noli) * 8) AS [TotalDPMO], " _

The total query should be:
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], " _
& " Count(QCLog.Noli) * 16 AS [DPMO_16], " _
& " Count(QCLog.Noli) * 8 AS [DPMO_8], " _
& " (Count(QCLog.Noli) * 16) + (Count(QCLog.Noli) * 8) AS [TotalDPMO], " _
& "FROM ClientName LEFT JOIN QCLog ON ClientName.ClientName = QCLog.ClientName " _
& " WHERE (([QCLog].[CheckedDate]) >=  # " & st1 & " #) AND (([QCLog].[CheckedDate]) <=  # " & st2 & " #) GROUP BY ClientName.ClientName;"
CurrentDb.Execute strSQL
0
 
LVL 7

Expert Comment

by:klakkas
ID: 33732516
!!!! Correction. DPMO_8 is incorrect

This is the correct response:

Add these 3 lines in your select:
& " Count(QCLog.Noli) * 16 AS [DPMO_16], " _
& " Sum(QCLog.Noli) * 8 AS [DPMO_8], " _
& " (Count(QCLog.Noli) * 16) + (Sum(QCLog.Noli) * 8) AS [TotalDPMO], " _

The total query should be:
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], " _
& " Count(QCLog.Noli) * 16 AS [DPMO_16], " _
& " Sum(QCLog.Noli) * 8 AS [DPMO_8], " _
& " (Count(QCLog.Noli) * 16) + (Sum(QCLog.Noli) * 8) AS [TotalDPMO], " _
& "FROM ClientName LEFT JOIN QCLog ON ClientName.ClientName = QCLog.ClientName " _
& " WHERE (([QCLog].[CheckedDate]) >=  # " & st1 & " #) AND (([QCLog].[CheckedDate]) <=  # " & st2 & " #) GROUP BY ClientName.ClientName;"
CurrentDb.Execute strSQL
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33732701
a little correction to klakkas

don't forget to add more field lists into INSERT INTO part

INSERT INTO SummaryReport (Clients, [Entered Invoices], [EMS Line Item], [No Of Errors] , [DPMO*16] , [DPMO*8], [TotalDPMO] )
0
 

Author Comment

by:pg1533
ID: 33733115
Hi, I am getting this error

"Number of query values and destination fields are not the same"

Please advice
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:pg1533
ID: 33733122
I have attached the code snippet
strSQL = "DELETE FROM SummaryReport"

CurrentDb.Execute strSQL



strSQL = "INSERT INTO SummaryReport ( Clients, [Entered Invoices], [EMS Line Item], [No Of Errors], [DPMO*16] , [DPMO*8], [TotalDPMO] ) " _

& "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] " _

& " Count(QCLog.Noli) * 16 AS [DPMO*16], " _

& " Count(QCLog.Noli) * 8 AS [DPMO*8], " _

& " (Count(QCLog.Noli) * 16) + (Count(QCLog.Noli) * 8) AS [TotalDPMO], " _

& " FROM ClientName LEFT JOIN QCLog ON ClientName.ClientName = QCLog.ClientName " _

& " WHERE (([QCLog].[CheckedDate]) >=  # " & st1 & " #) AND (([QCLog].[CheckedDate]) <=  # " & st2 & " #) GROUP BY ClientName.ClientName;"





CurrentDb.Execute strSQL

On Error GoTo errorhandler

Open in new window

0
 
LVL 7

Expert Comment

by:klakkas
ID: 33733134
You must do as JoeNuvo suggested.
The correct query is this:

strSQL = "INSERT INTO SummaryReport ( Clients, [Entered Invoices], [EMS Line Item], [No Of Errors] " _
& "[DPMO_16], [DPMO_8], [TotalDPMO]) " _
& "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], " _
& " Count(QCLog.Noli) * 16 AS [DPMO_16], " _
& " Sum(QCLog.Noli) * 8 AS [DPMO_8], " _
& " (Count(QCLog.Noli) * 16) + (Sum(QCLog.Noli) * 8) AS [TotalDPMO], " _
& "FROM ClientName LEFT JOIN QCLog ON ClientName.ClientName = QCLog.ClientName " _
& " WHERE (([QCLog].[CheckedDate]) >=  # " & st1 & " #) AND (([QCLog].[CheckedDate]) <=  # " & st2 & " #) GROUP BY ClientName.ClientName;"
0
 
LVL 7

Expert Comment

by:klakkas
ID: 33733150
Sorry friend, the problem is an extra ',' afther [TotalDPMO] in line 9 or your code, right before the FROM keyword.
0
 
LVL 7

Accepted Solution

by:
klakkas earned 500 total points
ID: 33733158
Full query:

strSQL = "INSERT INTO SummaryReport ( Clients, [Entered Invoices], [EMS Line Item], [No Of Errors] " _
& "[DPMO_16], [DPMO_8], [TotalDPMO]) " _
& "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], " _
& " Count(QCLog.Noli) * 16 AS [DPMO_16], " _
& " Sum(QCLog.Noli) * 8 AS [DPMO_8], " _
& " (Count(QCLog.Noli) * 16) + (Sum(QCLog.Noli) * 8) AS [TotalDPMO] " _
& "FROM ClientName LEFT JOIN QCLog ON ClientName.ClientName = QCLog.ClientName " _
& " WHERE (([QCLog].[CheckedDate]) >=  # " & st1 & " #) AND (([QCLog].[CheckedDate]) <=  # " & st2 & " #) GROUP BY ClientName.ClientName;"
0
 

Author Closing Comment

by:pg1533
ID: 33733248
Got It
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 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

16 Experts available now in Live!

Get 1:1 Help Now