combine rows

[summerAIS]

Hello everyone! First post here. I'm and accounting major and I'm taking an accounting information systems class (AIS) this summer. We have to solve some MS Access problems. I'm still kind of a newbie with Access and my prof is one of those types that won't help anyone, expects us to be experts already, and is pretty much useless, and is just there to collect a paycheck and needs to be fired. (can you say "tenure"?...LOL).



I've also taken a few Java programming classes, and some DB classes using SQL and Oracle. However, that was last year, and I have forgotten most of it already since I have not used it everyday. In addition, the prof for this class has us using a textbook that uses MS Access 2000 (I know, a little "outdated"), and everything I have done in the past has been with much more current software. It uses VBA, which I am not familiar with at all.

I am having trouble with what would probably seem like a very basic problem to most of you.

So, here it is:
I need to calculate the total purchases for each item, and the total for all purchases. I have managed to get the output I want, but I need to get the "InventoryID" column to have no repeating rows. Is there a way to combine the "KC0000" and the "IM0000" which each show up twice in so that they each only show up on one row, and their totals (for quantity and cost) are added together?

I was thinking I might need a second query based on the first one. What do you think? Or can I modify the design view query to do just as I need?

I can probably figure this out in an SQL statement, but again, I have to use MS Access 2000 for this which is set up for VBA.

In SQL, I would probably use DISTINCT which would read something like:

SELECT DISTINCT InventoryID
FROM Inventory Table

(And include the rest of the statements in the query to get the results I need, which I can't think of off the top of my head)

But is there a way to do this using the Query Builder in design view?

I have included a couple screenshots.

[pbaldy]

In that second query, change the "Group By" on the columns you want totaled to "Sum". See if that gets what you want. The only Group By should be on your InventoryID field.

[summerAIS]

In that second query, change the "Group By" on the columns you want totaled to "Sum". See if that gets what you want. The only Group By should be on your InventoryID field.

That did the trick! It is exactly what needed. Thanks a million pbaldy.

Now I can move on to some harder probs!

[pbaldy]

No problemo, and welcome to the site by the way!