Display Similiar rows as one row, with Quantity and Sums totalled.

[It_thug]

Okay, I'm a bit new to Access development, and I'm wondering if this is possible to have returned as a query. I know how I can do it in a Report, but a Query would be better for later manipulation and data analysis.

What I have is something along the along the lines of the following:

Company CODE Title Quantity Amount
ACA 01 Book Title 1 1 $10.00
ABD 01 Book Title 1 1 $10.00
AZF 01 Book Title 1 1 $10.00
APO 01 Book Title 1 1 $10.00
ADF 01 Book Title 1 1 $10.00

What I would like is to have a Query that returns it in the following fashion:

Code Title Quantity Amount
01 Book Title 1 5 $50.00

Is this even possible with a Query in Access?

 

[Scooterbug]

Sure is. Create a query with the following Fields:

Code (I would change the name...CODE is a reserved word)
Title
Quantity
Amount

Now, up top you will see the greek symbol Sigma (Looks like an uppercase E). Click on that, you will now see a new line for each field that says Sum. Click the drop down list for the Quantity and Amount and select sum. That should do it.

 

[It_thug]

Sure is. Create a query with the following Fields:

Code (I would change the name...CODE is a reserved word)
Title
Quantity
Amount

Now, up top you will see the greek symbol Sigma (Looks like an uppercase E). Click on that, you will now see a new line for each field that says Sum. Click the drop down list for the Quantity and Amount and select sum. That should do it.

Thanks much. Code was just an example.... That is what I was attempting to do when I finally started banging my head on this.....

Unfortunately it was still bringing back multiple rows...But. I think I see where I screwed up. I still had a column in there that would have had unique values in it.

So, thank you very much..... Half the help was seeing what I wrote as a question and the other half was your answer proving that I wasn't crazy in what I was doing.

Thanks a bunch!

 

[It_thug]

Sure is. Create a query with the following Fields:

Code (I would change the name...CODE is a reserved word)
Title
Quantity
Amount

Now, up top you will see the greek symbol Sigma (Looks like an uppercase E). Click on that, you will now see a new line for each field that says Sum. Click the drop down list for the Quantity and Amount and select sum. That should do it.

What's kind of interesting here, is that I created the query, and the intial one still has multiple rows coming back for the Product Code. Which I think is a bit odd, but can't really tell why.

So, I then took that query, and made another query based off of it's results....

and Voila! the data I want, in the way that I want it.

Thanks again.

 

[Scooterbug]

Glad you got it worked out!