Count qty of almost identical lines

[Robo0502]

Hi guys,

could you pls help with my issue? It is better to describe it on example.

Lets say I have these rows:

ordernumber / unit / qty
345 / laptop / 3
345 / laptop / 5
345 / PC / 7
345 / PC / 9
345 / PC / 2

And I d like to get this result.
345 / laptop / 8
345 / PC / 18

Basically i d like to count number of ordered units and put it in one line.

Could you pls help?

Thank you

 

[plog]

What you want is a totals query. Check out this link:

https://support.office.com/en-us/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

 

[plog]

Wow, I posted that without actually looking at it. Its really a horrible link that's so in-depth its not much help to beginners. Try this one instead:

https://www.599cd.com/tips/access/aggregate-query/

 

[jdraw]

You need an aggregate query with a Group BY

If your table is called OrdersPost the query will be

SELECT OrdersPost.ordernumber, OrdersPost.unit,Sum(OrdersPost.qty) AS SumOfqty
FROM OrdersPost
GROUP BY OrdersPost.ordernumber, OrdersPost.unit;

And the result:

ordernumber	unit	SumOfqty
345	laptop	8
345	PC	18

Change the table name to match yours.

See http://www.w3schools.com/sql/sql_func_sum.asp for more info.

 

[Robo0502]

thanks guys. Now I remember I already used aggregate query before, but after a year I am back in Access and I forgot most of the stuff.

And what would be the best way to replace original lines in source table with these aggregate ones?

 

[plog]

And what would be the best way to replace original lines in source table with these aggregate ones?

You shouldn't. Data that can be calculated should be calculated, not stored. If you need to reference this aggregate data in another query or in a report, you don't save it to a table, you simple reference the aggregate query.

 

[Robo0502]

I need to replace them though. Otherwise the table is useless. any idea?

 

[plog]

Is this a one time deal or will you be updating the table frequently with these totals?

 

[Robo0502]

new table with actual data will be imported every day, thus every day needs to be done this aggregate query.

 

[plog]

To add data to an existing table you would use an APPEND query: https://support.office.com/en-us/ar...nd-query-71cd4fe3-c2d7-4856-a0c8-a2638ccf4ad0

You would use the existing totals query we built before then in the ribbon click the Append option and designate which table the records are to go to.

However, I'm still not convinced this is the best way to achieve this. I'd have to know more about the import and what happens to old data.

 

[Guus2005]

I need to replace them though. Otherwise the table is useless. any idea?

What you're describing should only be done in a datawarehouse or be written in a different table for speed issues. Once you have overwritten the original values, you can never go back to the old situation and when you run the query again, all the values are wrong.

According to good relational database design you should store calculated fields.
Just run the query and you can see the current status of the quantities.

HTH

 

[Robo0502]

I think I got it.
At first using aggregate query I create a so called "aggregate" table where I can store sum of qty. Then I delete duplicates using method described here ( http://www.satorisoftware.com/support-article/eliminating-duplicate-records-in-microsoft-access/ ) . I chose alternate one because is much more simple. So right now I have the original table without duplicates but with wrong qty. That I can get from my Aggregate table using update query. Bit complicated but accomplished.

Thank you for your help guys.