CODEWITHSUNDEEP
sqlms-access
LRO
LRO

Reputation: 39

Complex Query MSACCESS

I have a MS ACCESS table named Sales, and it contain 5 fields and need to create a query to get a report showing the sale of each item.. Table Example..

Sales Table

+------------+--------+---------+-----------+-----------+
|   SaleDate | ItemID | SaleQty | SalePrice | SaleTotal |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1105 |   30.00 |      5.25 |    157.50 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1105 |    2.00 |      5.25 |     10.50 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1105 |   10.00 |      6.00 |     60.00 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1105 |    4.00 |      6.00 |     24.00 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1106 |    3.00 |      8.00 |     24.00 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1106 |    8.00 |      8.00 |     64.00 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1106 |   12.00 |      8.50 |    102.00 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1106 |    6.00 |      8.50 |     51.00 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1105 |   12.00 |      6.00 |     72.00 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1105 |    9.00 |      6.00 |     54.00 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1105 |    1.00 |      6.50 |      6.50 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1105 |    2.00 |      6.50 |     13.00 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1106 |    7.00 |      8.50 |     59.50 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1106 |    4.00 |      8.50 |     34.00 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1106 |    3.00 |      8.70 |     26.10 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1106 |   16.00 |      8.70 |    139.20 |
+------------+--------+---------+-----------+-----------+`

it's so difficult to explain.. but i try In the table exist two record (First)

Example: The first two records in the table has the same SaleDate,ItemID and SalePrice

what i need to do is SUM the field SaleQty with all the records that have the same SaleDate,ItemID and SalePrice (Also SUM the SaleTotal)

Sales Query Result (Needed)

+------------+--------+---------+-----------+-----------+
|   SaleDate | ItemID | SaleQty | SalePrice | SaleTotal |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1105 |   32.00 |      5.25 |    168.00 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1105 |   14.00 |      6.00 |     10.50 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1106 |   11.00 |      8.00 |     88.00 |
+------------+--------+---------+-----------+-----------+
| 02/25/2017 |   1106 |   18.00 |      8.50 |    153.00 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1105 |   21.00 |      6.00 |    126.00 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1105 |    3.00 |      6.50 |     19.50 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1106 |   11.00 |      8.50 |     93.50 |
+------------+--------+---------+-----------+-----------+
| 03/25/2017 |   1106 |   19.00 |      8.70 |    165.30 |
+------------+--------+---------+-----------+-----------+`

Thanks..

Upvotes: 1

Views: 38

Answers (1)

nicogen
nicogen

Reputation: 204

Try with this

SELECT SaleDate, ItemID, SalePrice, SUM(SaleQty) AS SaleQty, SUM(SaleTotal) AS SaleTotal

FROM Sales

GROUP BY SaleDate, ItemID, SalePrice

To learn more about group by in MS Access, see https://support.office.com/en-gb/article/GROUP-BY-Clause-84eeb766-25d2-4aa1-8eea-002bb65ef3a0

Upvotes: 2

Related Questions