Using Totals Query for next query to show further values that were not included in totals query (like note field) (1 Viewer)

TB11

Member
Local time
Yesterday, 20:15
Joined
Jul 7, 2020
Messages
78
Hi. I'm stuck.

My table:
IDProjectTask
fkProject
fkTask This involves just 6 different tasks.
DateTask
TimeTask
fkTaskStatus
TaskNote

My totals query, to give me the latest DateTimeTask for each fkTask.
fkTask - grouped by
DateTimeTask - Max. Formula is [as I added DateTask + TimeTask] Max([DateTask]+[TimeTask])

My problem: I can't get to the point where I can use the totals query in a second query in which I can add the TaskNote, fkTaskStatus and IdProjectTask fields, without getting more results than the totals query. I just can't figure out what I am missing.

Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:15
Joined
Feb 19, 2002
Messages
42,981
Once you aggregate the data with a totals query, you loose the keys necessary to link back to data for specific records. So, it is not clear what your objective is. Either you want summary data or detail data.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:15
Joined
May 7, 2009
Messages
19,169
use Sub-Query.
 

TB11

Member
Local time
Yesterday, 20:15
Joined
Jul 7, 2020
Messages
78
@Pat Hartman I want both the aggregate query to get the last date for a project task, then a details query to show the details, like the note field, of that last project task.

@arnelgp That's where I am stuck. I have tried several different sub-queries, but each has given me more entries than what is involved in the totals query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:15
Joined
May 7, 2009
Messages
19,169
can you can upload a sample table?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:15
Joined
Feb 19, 2002
Messages
42,981
The subquery should solve the problem. Please post what you have. Or post the db if you can
 

TB11

Member
Local time
Yesterday, 20:15
Joined
Jul 7, 2020
Messages
78
@Pat Hartman and @arnelgp Thank you, I have been tying to figure this out for awhile. You both got me thinking on a different approach, and I solved it.

The answer, should anyone else have the same issue: the first query is the aggregate query. The second, sub-query, is based on the totals query (which I included the DateTask field instead of the DateTimeTask calculated field), with joins from the totals query to the original table to the fkTask and the DateTask fields.

.
 

Users who are viewing this thread

Top Bottom