SQL code to group records with successive dates in a row

[seb]

Hello. For a query, I have this SQL code that works well.

SELECT Min(tblmod.dates) AS date1, Max(tblmod.dates) AS date2, tbltaks.tbltaskname, Format(Sqr([Qty]),"Standard") AS rc, tblmod.plot_idFROM tbltaks INNER JOIN (tblplot INNER JOIN tblmod ON tblplot.plotid = tblmod.plot_id) ON tbltaks.task_id = tblmod.task_id
GROUP BY tbltask.tbltaskname, tblmod.dates, tblmod.plot_id
HAVING (((tblmod.plot_id)=54))
ORDER BY tblmod.dates;

However, I am looking for an SQL code to group tasks stored in successive dates into a single row.
I have been searching the web but I don't know type the correct keywords.
In the attached image:
The table on the left is my SQL code.


The table on the right is the one I need to program.

I really appreciate your help.

[June7]

I am not understanding the right side output. There is no task6 - I think the second task5 should be task6. Why are the two task3 records combined to one row and task2 records are not? What is rc?

You need explain the rules for this data aggregation. If you were to do this analysis manually, what decision steps would you follow?

Show original raw data. If you want to provide db for analysis, follow instructions at bottom of my post.

[CJ_London]

looks like you have free typed your code - there appear to be typos (highlighted red) which would prevent the code from running. Best to just copy/paste what you have. You should only need to makes some small changes, try this

SELECT Min(tblmod.dates) AS date1, Max(tblmod.dates) AS date2, tbltaks.tbltaskname, Format(sum(Sqr([Qty])),"Standard") AS rc FROM tbltaks INNER JOIN (tblplot INNER JOIN tblmod ON tblplot.plotid = tblmod.plot_id) ON tbltaks.task_id = tblmod.task_id
WHERE (((tblmod.plot_id)=54))
GROUP BY tbltask.tbltaskname
ORDER BY tblmod.dates;

I have removed fields which appear to not be required, changed the HAVING to WHERE and summed the Qty before you format it. Note that best practice is to format values in a form or report, not in the underlying query

[seb]

Many thanks for your reply
June I give you my apologies for the errors you indicate. As Ajax said, I typed the SQL code.
June and Ajax. I want you to briefly explain what I’m trying to do.
I have a form with a textbox for date1 and another textbox for date2 and a combobox for selecting the plot number. Then a command button exports the query to Excel.
This is my copy and paste SQL code:

SELECT Min(tblmod.date) AS date1, Max(tblmod.date) AS date2, tbltaskname.taskname, Format(Sqr([hours]),"Standard") AS rc, Format(Sum([quantity]*8),"Standard") AS hours
FROM tbltaskname INNER JOIN (tblplot INNER JOIN tblmod ON tblplot.plot_id = tblmod.plot_id) ON tbltaskname.task_id = tblmod.task_id
GROUP BY tbltaskname.taskname, tblmod.plot_id, tblmod.date
ORDER BY Min(tblmod.date);

Here is the query:
date1 date2 taskname rc hours
18/12/2017 18/12/2017 task1 6,32 40,00
19/12/2017 19/12/2017 task1 6,32 40,00
20/12/2017 20/12/2017 task1 6,32 40,00
21/12/2017 21/12/2017 task1 6,32 40,00
11/01/2018 11/01/2018 task2 2,83 8,00
15/01/2018 15/01/2018 task2 4,00 16,00
15/01/2018 15/01/2018 task3 13,56 184,00
16/01/2018 16/01/2018 task3 14,97 224,00
16/01/2018 16/01/2018 task2 4,90 24,00
29/01/2018 29/01/2018 task2 2,83 8,00
21/06/2018 21/06/2018 task2 4,90 24,00
22/06/2018 22/06/2018 task2 4,90 24,00
21/06/2018 21/06/2018 task4 6,32 40,00
22/06/2018 22/06/2018 task5 4,90 24,00
26/06/2018 26/06/2018 task5 10,95 120,00
26/06/2018 26/06/2018 task2 2,83 8,00
28/06/2018 28/06/2018 task2 2,83 8,00

I’ve been looking for an SQL query code to displays the records like this:
date1 date2 taskname rc hours
18/12/2017 21/12/2017 task1 25,28 160,00
15/01/2018 16/01/2018 task3 28,53 408,00
21/06/2018 22/06/2018 task2 9,80 48,00
21/06/2018 21/06/2018 task4 6,32 40,00
22/06/2018 22/06/2018 task5 4,90 24,00
26/06/2018 26/06/2018 task5 10,95 120,00
26/06/2018 26/06/2018 task2 2,83 8,00
28/06/2018 28/06/2018 task2 2,83 8,00

I spent time searched google many hours about this, but not sure the keywords to use.
Please help.

[June7]

Need your raw data, not results of query you don't like.

If I understand what you mean by 'successive', I don't see way to do this with query alone. I would probably use VBA writing records to temp table.

[seb]

June. Thanks for your reply.
I am preparing my MS Relational DB file to send it to you.
Cheers

[rpeare]

seb.zip

Example database using your data, though your output does not match what it should be I think.

I just added one additional field for sequencing within a particular task then just ran an aggregate query on the data in each sequence.