Solved Help with subqueries (1 Viewer)

[Ken Sheridan]

What I am trying to do is use the StartDate of the next record as the EndDate for the current record until we get to the last record, in that case the EndDate will be the current date/time.

The following query returns the rows from a Transactions table, numbered sequentially in TransactionDate order. In the event of two or more transactions being on the same date the TransactionID primary key acts as the tie breaker:

SELECT
    COUNT(*) AS RowNumber,
    T1.TransactionDate,
    T1.CustomerID,
    T1.TransactionAmount
FROM
    Transactions AS T1
    INNER JOIN Transactions AS T2 ON (
        T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate
    )
    AND (T2.TransactionDate <= T1.TransactionDate)
GROUP BY
    T1.TransactionDate,
    T1.TransactionID,
    T1.TransactionAmount,
    T1.CustomerID;

The following query is based on the above query, and adds the EndDate to each row:

SELECT
    Q1.*,
    NZ (
        (
            SELECT
                TransactionDate
            FROM
                qryTransactions AS Q2
            WHERE
                Q2.RowNumber = Q1.RowNumber + 1
        ),
        NOW()
    ) AS EndDate
FROM
    qryTransactions AS Q1;