Sumarize data in Query like in a Pivot Table

  • Thread starter Thread starter excelCPA
  • Start date Start date
E

excelCPA

I have a query that pulls in data from payroll and sales tables.
Results are similar to the following:

Payroll_No L_Name Sale_No Sale_Amt
12345 Smith 2434 100
12345 Smith 3434 200
12345 Smith 3654 50
23456 Jones 7870 50
23456 Jones 7987 200
45678 Phillips 2223 100
45678 Phillips 9898 50
45678 Phillips 9896 300
45678 Phillips 7878 200
45678 Phillips 8989 400

The SQL is:

SELECT tblPerson.Payroll_No, tblPerson.L_Name, tblSale.Sale_No,
tblSale.Sale_Amt
FROM tblSale INNER JOIN tblPerson ON tblSale.Payroll_No = tblPerson.
Payroll_No

I need to make the query summarize the data per payroll number by
count, sum, and average similar to a Pivot Table. The results for the
data above would look like this:

Payroll_No L_Name Count_Sale_No Sum_Sale_Amt Avg_Sale_Amt
12345 Smith 3
350 116.67
23456 Jones 2
250 175
45678 Phillips 5
1050 2100

How can this be accomplished? Thanks.
 
It is called a 'Totals' query.
Open your query in design view and click on the icon that looks like an 'M'
on its side - ∑ - and then change the GROUP BYs to the math function you want
to use.
 
You can also do it by a modest amendment to the existing SQL:

SELECT tblPerson.Payroll_No, tblPerson.L_Name,
COUNT(*) AS Count_Sale_No,
SUM(tblSale.Sale_Amt) AS Sum_Sale_Amt,
AVG(tblSale.Sale_Amt) AS Avg_Sale_Amt
FROM tblSale INNER JOIN tblPerson
ON tblSale.Payroll_No = tblPerson.Payroll_No
GROUP BY tblPerson.Payroll_No, tblPerson.L_Name;

Ken Sheridan
Stafford, England
Click to expand...

Thanks, I tried this, but it is still all indivual Payroll_No's
instead of a single row for each. Any ideas?
 
You can also do it by a modest amendment to the existing SQL:

SELECT tblPerson.Payroll_No, tblPerson.L_Name,
COUNT(*) AS Count_Sale_No,
SUM(tblSale.Sale_Amt) AS Sum_Sale_Amt,
AVG(tblSale.Sale_Amt) AS Avg_Sale_Amt
FROM tblSale INNER JOIN tblPerson
ON tblSale.Payroll_No = tblPerson.Payroll_No
GROUP BY tblPerson.Payroll_No, tblPerson.L_Name;

Ken Sheridan
Stafford, England
Click to expand...

Also when use this SQL, I get a "Syntax error in GROUP BY clause"
 
Try it this way --
SELECT tblPerson.Payroll_No, tblPerson.L_Name, COUNT(*) AS Count_Sale_No,
SUM(tblSale.Sale_Amt) AS Sum_Sale_Amt, AVG(tblSale.Sale_Amt) AS Avg_Sale_Amt
FROM tblPerson LEFT JOIN tblSale ON tblSale.Payroll_No = tblPerson.Payroll_No
GROUP BY tblPerson.Payroll_No, tblPerson.L_Name;
 
I got it working. I forgot to select "Group By" instead of sum in the
desin view. Thanks for your help.



I can't spot any error in the SQL statement at first sight.  Copy the SQL
statement exactly a you've used it when it raises the error, and post it back
here.

Ken Sheridan
Stafford, England
[quoted text clipped - 49 lines]
- Show quoted text -
Click to expand...
Click to expand...
Also when use this SQL, I get a "Syntax error in GROUP BY clause"
Click to expand...
Click to expand...
 
Back
Top