Calculate days between dates in 2 rows in the same table

[Jan de Zoveelste]

[Solved]



I have a table tblGroupHistory, with the fields pID, gID, ghStartDate, ghComment. (pID refers to tblPerson, gID to tblGroup).
I would like to have a query with the number of days in a group, preferably with also the ghComment of both records.

Example:

pID  gID  ghStartDate  ghComment
 1    3    02/20/2018  excellent performance
 2    6    02/23/2018
 1    2    02/28/2018  group 3 was to much

Preferable result:

pID  gID  StartDate      EndDate        Days  CommentStart              CommentEnd
  1   3   02/20/2018     02/28/2018       8   excellent performance     group 3 was to much
  2   6   02/23/2018
  1   2   02/28/2018                          group 3 was to much

(but a query that results in pID, gID, ghStartDate, ghComment, Days helps already a lot)

Can anyone help me with this?

[ranman256]

1 row cannot see another row, so, you need a few queries:
Q1 pull rec 1,
Q2, pull the record ID using MAX(id) where ID < Q1.ID
Q3, put Q1 & Q2 together

[isladogs]

It can also be done fairly easily using a subquery.
See the section 'Get Value from another record' in this link http://allenbrowne.com/subquery-01.html

[Jan de Zoveelste]

I've solved it with ridders52s link.

Thank you both for your help!

[isladogs]

You're welcome