Using group, sum, first and last in a query

[dxfoxd]

I have a database with the following records

+--------------+------------+-------------+---------+
| period_start | period_end | customer_id | revenue |
+--------------+------------+-------------+---------+
| 1-1-2023     | 31-3-2023  |           1 |    1000 |
| 1-4-2023     | 30-06-2023 |           1 |    1500 |
| 12-1-2023    | 31-3-2023  |           2 |    1000 |
| 1-4-2023     | 30-06-2023 |           2 |    1900 |
| 1-1-2023     | 31-5-2023  |           3 |    1200 |
+--------------+------------+-------------+---------+

I want to create a query resulting in

+-------------+--------------+------------+---------+
| customer_id | period_start | period_end | revenue |
+-------------+--------------+------------+---------+
|           1 | 01-01-2023   | 20-06-2023 |    2500 |
|           2 | 12-01-2023   | 30-06-2023 |    2900 |
|           3 | 1-1-2023     | 31-5-2023  |    1200 |
+-------------+--------------+------------+---------+

How can I get this result? Trying for hours now, but cannot figure this out.

 

[CJ_London]

What have you tried?

 

[GPGeorge]

You want "Min" and "Max", not "First" and "Last", but also, showing us your existing SQL could go a long way towards suggesting approaches to try.

 

[monheimx9]

Could you try this:

SELECT customer_id,
    MIN(period_start) AS period_start,
    MAX(period_end) AS period_end,
    SUM(revenue) AS revenue
FROM
    tblSomething
GROUP BY
    customer_id;

 

[The_Doc_Man]

You have an example of the correct way to do things, but you need to know WHY they are steering you away from FIRST and LAST.

A table in an Access database is an unordered set of records that might HAPPEN to have a particular order if you have just imported it. But it got that order by processing something in the order it was presented. When a table undergoes UPDATE or INSERT INTO queries, the order of records can become jumbled. After several rounds of UPDATE/INSERT activity, the records would appear to become disordered. In fact, they are ordered chronologically with respect to time of last table update involving that record, whether or not the record had a timetag in it. For that reason, FIRST and LAST are of questionable value in a table. If you have a usable field for sorting, the MIN and MAX functions would do the job.

HOWEVER... if you have a QUERY with an ORDER BY clause, then FIRST and LAST have meaning with respect to the field being ordered. In that context, the order of record appearance is predictable and you can expect a usable return based on FIRST/LAST.

 

[ebs17]

| 1 | 01-01-2023 | 20-06-2023 | 2500

For me it would be highly unclear how to get from 30-06-2023 to 20-06-2023.