Sumarize data in Query like in a Pivot Table

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.

--
Build a little, test a little.


"excelCPA" wrote:

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

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

excelCPA wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

On Dec 3, 5:49*pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
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





excelCPA wrote:
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.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1- Hide quoted text -

- Show quoted text -

Thanks, I tried this, but it is still all indivual Payroll_No's
instead of a single row for each. Any ideas?

On Dec 3, 5:49*pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
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





excelCPA wrote:
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.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1- Hide quoted text -

- Show quoted text -

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;

--
Build a little, test a little.


"excelCPA" wrote:

On Dec 3, 5:49 pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
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





excelCPA wrote:
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.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1- Hide quoted text -

- Show quoted text -

Also when use this SQL, I get a "Syntax error in GROUP BY clause"
.

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

excelCPA wrote:
On Dec 3, 5:49 pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
You can also do it by a modest amendment to the existing SQL:

[quoted text clipped - 49 lines]

- Show quoted text -

Also when use this SQL, I get a "Syntax error in GROUP BY clause"

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

I got it working. I forgot to select "Group By" instead of sum in the
desin view. Thanks for your help.



On Dec 4, 12:48*pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
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

excelCPA wrote:
On Dec 3, 5:49 pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
You can also do it by a modest amendment to the existing SQL:

[quoted text clipped - 49 lines]

- Show quoted text -

Also when use this SQL, I get a "Syntax error in GROUP BY clause"

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1