how to sum several rows by a common column (ie customer)

[Transpose data in rows to columns]

I've created an access query of which I'm trying to "summarize" by part by
customer. Example of query "raw data" and what I'm attempting to summarize,
below:

"Raw Query Data"
Part # Customer Qty Sold
XYZ John 5
XYZ John 6
XYZ Pete 4


"What I'm attempting to summarize when I run query"
Part # Customer Qty Sold
XYZ John 11
XYZ Pete 4

I would appreciate any help you can offer regarding this matter.

Thanks,
Pete

 

[OfficeDev18 via AccessMonster.com]

Hi, Pete, try this.

SELECT [Part #], Customer, Sum([Qty Sold]) As [SumOfQty Sold] FROM
YourDataTableNameHere GROUP BY [Part #], Customer;

But wait, Pete; this is Access 101 stuff. There's gotta be a part 2 to this
question that changes everything. What's really up?

In any event,

HTH

 

[Transpose data in rows to columns]

Thank you for your response....I'm definately a novice when it comes to
access (no part 2 at all). Would you mind providing a bit more guidance on
exactly where I input/enter the your solution below (ie. in "Query Design"
under "Criteria" input, or under "total" input......on the pane grids)??

Thank you for your support.

Pete

Hi, Pete, try this.

SELECT [Part #], Customer, Sum([Qty Sold]) As [SumOfQty Sold] FROM
YourDataTableNameHere GROUP BY [Part #], Customer;

But wait, Pete; this is Access 101 stuff. There's gotta be a part 2 to this
question that changes everything. What's really up?

In any event,

HTH

I've created an access query of which I'm trying to "summarize" by part by
customer. Example of query "raw data" and what I'm attempting to summarize,
below:

"Raw Query Data"
Part # Customer Qty Sold
XYZ John 5
XYZ John 6
XYZ Pete 4

"What I'm attempting to summarize when I run query"
Part # Customer Qty Sold
XYZ John 11
XYZ Pete 4

I would appreciate any help you can offer regarding this matter.

Thanks,
Pete

 

[OfficeDev18 via AccessMonster.com]

in the query design grid, click on the SQL icon in the upper left corner, and
copy and paste the SQL code there. Just make sure you put in the correct
table name where I left a place for it.

Thank you for your response....I'm definately a novice when it comes to
access (no part 2 at all). Would you mind providing a bit more guidance on
exactly where I input/enter the your solution below (ie. in "Query Design"
under "Criteria" input, or under "total" input......on the pane grids)??

Thank you for your support.

Pete

Hi, Pete, try this.

[quoted text clipped - 27 lines]

 

[Transpose data in rows to columns]

I followed your instructions, below...by entering the following SQL
code.....however, received an error message stating "characters found after
end of SQL statement?? I must by using improper syntaxs??

Please advise.....and I thank you for your patience.

Pete

SELECT [Part #], Customer, Sum([Qty Sold]) As [SumOfQty Sold] FROM
dbo_sop30300 GROUP BY [Part #], Customer;

in the query design grid, click on the SQL icon in the upper left corner, and
copy and paste the SQL code there. Just make sure you put in the correct
table name where I left a place for it.

Thank you for your response....I'm definately a novice when it comes to
access (no part 2 at all). Would you mind providing a bit more guidance on
exactly where I input/enter the your solution below (ie. in "Query Design"
under "Criteria" input, or under "total" input......on the pane grids)??

Thank you for your support.

Pete

Hi, Pete, try this.

[quoted text clipped - 27 lines]

Thanks,
Pete

 

[OfficeDev18 via AccessMonster.com]

Did you perhaps enter the last character as a comma "," It should be a semi-
colon ";"

That's why I suggested copy-and-paste....... Also, the semi-colon should be
the last character in the SQL statement. You might want to check that out
(although I don't know that blank spaces following the s-c would do any harm).

I followed your instructions, below...by entering the following SQL
code.....however, received an error message stating "characters found after
end of SQL statement?? I must by using improper syntaxs??

Please advise.....and I thank you for your patience.

Pete

SELECT [Part #], Customer, Sum([Qty Sold]) As [SumOfQty Sold] FROM
dbo_sop30300 GROUP BY [Part #], Customer;

in the query design grid, click on the SQL icon in the upper left corner, and
copy and paste the SQL code there. Just make sure you put in the correct

[quoted text clipped - 14 lines]