Solved Help with subqueries (1 Viewer)

NauticalGent · Friday at 4:36 PM

I am having a problem with this query, it is giving me the wrong results.

Code:

INSERT INTO tmp_TimeInPhase
    (PRON, [TimeStamp], [Current Proc Point], ProcPointStartDate, EndDate, TimeInProc, [Updated By], PALT)
SELECT
    p.PRON,
    a.TimeStamp,
    c.[Current Proc Point],
    a.ProcPointStartDate,
    IIf(
        (SELECT Min(b.ProcPointStartDate)
         FROM tbl_Actual_PROC_Point AS b
         WHERE b.PRON_ID = a.PRON_ID
           AND b.ProcPointStartDate > a.ProcPointStartDate) Is Not Null,
        DateAdd("d", -1, (SELECT Min(b.ProcPointStartDate)
                          FROM tbl_Actual_PROC_Point AS b
                          WHERE b.PRON_ID = a.PRON_ID
                            AND b.ProcPointStartDate > a.ProcPointStartDate)),
        Date()
    ) AS EndDate,
    Int(
        IIf(
            (SELECT Min(b.ProcPointStartDate)
             FROM tbl_Actual_PROC_Point AS b
             WHERE b.PRON_ID = a.PRON_ID
               AND b.ProcPointStartDate > a.ProcPointStartDate) Is Not Null,
            DateAdd("d", -1, (SELECT Min(b.ProcPointStartDate)
                              FROM tbl_Actual_PROC_Point AS b
                              WHERE b.PRON_ID = a.PRON_ID
                                AND b.ProcPointStartDate > a.ProcPointStartDate)),
            Date()
        ) - a.ProcPointStartDate
    ) AS TimeInProc,
    a.[Updated By],
    c.PALT
FROM
    (tbl_PRON AS p
     INNER JOIN tbl_Actual_PROC_Point AS a ON p.PRON_ID = a.PRON_ID)
    INNER JOIN tbl_Current_PROC_Point AS c ON a.CurrentProcPointID = c.CurrentProcPointID
WHERE
    (((p.PRON_ID) = [TempVars]![PRON_ID]))
ORDER BY
    a.ProcPointStartDate;

Here it the result:

PRON	TimeStamp	Current Proc Point	ProcPointStartDate	EndDate	TimeInProc	Updated By	PALT
AX6F2449AX	PRON Received Buyer	Christopher Butler
AX6F2449AX	Planning	P9CTT - Matt Tatum

[td width="103pt"]

12/11/2025 13:47

[/td]

[td width="89pt"]

12/11/2025

[/td]

[td width="68pt"]

1/9/2026

[/td]

[td width="44pt"]

29

[/td]

[td width="48pt"]

-1

[/td]

[td width="103pt"]

12/11/2025 14:20

[/td]

[td width="89pt"]

12/11/2025

[/td]

[td width="68pt"]

1/9/2026

[/td]

[td width="44pt"]

29

[/td]

[td width="48pt"]

-1

[/td]

Here is the desired result:

PRON	TimeStamp	Current Proc Point	ProcPointStartDate	EndDate	TimeInProc	Updated By	PALT
AX6F2449AX	PRON Received Buyer	Christopher Butler
AX6F2449AX	Planning	P9CTT - Matt Tatum

[td width="103pt"]

12/11/2025 13:47

[/td]

[td width="89pt"]

12/11/2025

[/td]

[td width="68pt"]

12/11/2025

[/td]

[td width="44pt"]

0

[/td]

[td width="48pt"]

-1

[/td]

[td width="103pt"]

12/11/2025 14:20

[/td]

[td width="89pt"]

12/11/2025

[/td]

[td width="68pt"]

1/9/2026

[/td]

[td width="44pt"]

29

[/td]

[td width="48pt"]

-1

[/td]

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. I could use a sub to do this, but I am sure it can be done with a query, but obviously I am not doing it right.

Any ideas?

Gasman · Friday at 4:44 PM

John, there is a problem pasting tables direct here?

Pasting table to a post

Copying and pasting a table or query to a post looks as expected until you save it - then its all code and unreadable. e.g. I copy/paste: but go to preview or save and it looks like this: what an I missing? Query1 PricesOrderedFromFK PricesQuantity RegisteredOn PricesRegisteredBy...

www.access-programmers.co.uk

So unsure what is wrong with yours.
Copied to Notepad and copy and pasted from there.

03/02/2025 24.32 1.2031 MXN 24,320.00 USD 1,805.00
04/02/2025 23.722 1.216 MXN 23,722.00 USD 1,824.00
05/02/2025 24.096 1.225 MXN 24,096.00 USD 1,838.00
06/02/2025 24.205 1.2254 MXN 24,205.00 USD 1,838.00
07/02/2025 23.916 1.2219 MXN 23,916.00 USD 1,833.00
08/02/2025 24.037 1.2243 MXN 24,037.00 USD 1,836.00

Copied to Word and copy and pasted from there.

03/02/2025 24.32 1.2031 MXN 24,320.00 USD 1,805.00

04/02/2025 23.722 1.216 MXN 23,722.00 USD 1,824.00

05/02/2025 24.096 1.225 MXN 24,096.00 USD 1,838.00

06/02/2025 24.205 1.2254 MXN 24,205.00 USD 1,838.00

07/02/2025 23.916 1.2219 MXN 23,916.00 USD 1,833.00

08/02/2025 24.037 1.2243 MXN 24,037.00 USD 1,836.00

cheekybuddha · Friday at 5:07 PM

Break it down into bitesize chunks.

First make sure you get the right end dates from the start dates.

SQL:

SELECT
  a.PRON_ID,
  a.ProcPointStartDate,
  (
    SELECT TOP 1
      b.ProcPointStartDate
    FROM tbl_Actual_PROC_Point AS b
    WHERE b.ProcPointStartDate > a.ProcPointStartDate
  ) AS EndDate
FROM tbl_Actual_PROC_Point AS a

Then build from there

cheekybuddha · Friday at 5:22 PM

Next, add in the other fields from tbl_Actual_PROC_Point :

SQL:

SELECT
  a.PRON_ID,
  a.CurrentProcPointID,
  a.TimeStamp,
  a.ProcPointStartDate,
  (
    SELECT TOP 1
      b.ProcPointStartDate
    FROM tbl_Actual_PROC_Point AS b
    WHERE b.ProcPointStartDate > a.ProcPointStartDate
  ) AS EndDate,
  a.[Updated By]
FROM tbl_Actual_PROC_Point AS a

You can use this subquery in the FROM part of your query, and SELECT its fields normally:

SQL:

SELECT
  p.PRON,
  a1.TimeStamp,
  c.[Current Proc Point],
  a1.ProcPointStartDate,
  Nz(a1.EndDate, Date()) AS EndDate,
  Nz(a1.EndDate, Date()) - a1.ProcPointStartDate AS TimeInProc,
  a1.[Updated By],
  c.PALT
FROM (
  tbl_PRON AS p
  INNER JOIN (
    SELECT
      a.PRON_ID,
      a.CurrentProcPointID,
      a.TimeStamp,
      a.ProcPointStartDate,
      (
        SELECT TOP 1
          b.ProcPointStartDate
        FROM tbl_Actual_PROC_Point AS b
        WHERE b.ProcPointStartDate > a.ProcPointStartDate
      ) AS EndDate,
      a.[Updated By]
    FROM tbl_Actual_PROC_Point AS a
  ) AS a1
          ON p.PRON_ID = a1.PRON_ID
)
INNER JOIN tbl_Current_PROC_Point AS c
        ON a1.CurrentProcPointID = c.CurrentProcPointID
WHERE p.PRON_ID = [TempVars]![PRON_ID]
ORDER BY
  a1.ProcPointStartDate;

[EDITED to add missing field in subquery]

cheekybuddha · Friday at 5:22 PM

NB. All the above is complete AIRCODE!

cheekybuddha · Friday at 6:48 PM

Ouch! Just noticed CurrentProcPointID used in the join isn't selected in the subquery - will edit the query in Post #4 to correct that.

NauticalGent · Friday at 9:23 PM

Hi Dave, something in your replies made the light bulb go off in my wee brain. The only time I got bad results was when the "next" record and the same StartDate - in my test data, 12/11/2025.

Here is the revised query and it is working (for now!)

Code:

INSERT INTO tmp_TimeInPhase
    (PRON, [TimeStamp], [Current Proc Point], ProcPointStartDate, EndDate, TimeInProc, [Updated By], PALT)
SELECT
    p.PRON,
    a.TimeStamp,
    c.[Current Proc Point],
    a.ProcPointStartDate,
    IIf(
         (SELECT Min(b.ProcPointStartDate)
         FROM tbl_Actual_PROC_Point AS b
         WHERE b.PRON_ID = a.PRON_ID
           AND (b.ProcPointStartDate > a.ProcPointStartDate
                OR (b.ProcPointStartDate = a.ProcPointStartDate AND b.TimeStamp > a.TimeStamp)
               )
        ) Is Not Null,
        (SELECT Min(b.ProcPointStartDate)
         FROM tbl_Actual_PROC_Point AS b
         WHERE b.PRON_ID = a.PRON_ID
           AND (b.ProcPointStartDate > a.ProcPointStartDate
                OR (b.ProcPointStartDate = a.ProcPointStartDate AND b.TimeStamp > a.TimeStamp)
               )
        ),
        Date()
    ) AS EndDate,
    Int(
        IIf(
            (SELECT Min(b.ProcPointStartDate)
             FROM tbl_Actual_PROC_Point AS b
             WHERE b.PRON_ID = a.PRON_ID
               AND (b.ProcPointStartDate > a.ProcPointStartDate
                    OR (b.ProcPointStartDate = a.ProcPointStartDate AND b.TimeStamp > a.TimeStamp)
                   )
            ) Is Not Null,
             (SELECT Min(b.ProcPointStartDate)
             FROM tbl_Actual_PROC_Point AS b
             WHERE b.PRON_ID = a.PRON_ID
               AND (b.ProcPointStartDate > a.ProcPointStartDate
                    OR (b.ProcPointStartDate = a.ProcPointStartDate AND b.TimeStamp > a.TimeStamp)
                   )
            ),
            Date()
        ) - a.ProcPointStartDate
    ) AS TimeInProc,
    a.[Updated By],
    c.PALT
FROM
    (tbl_PRON AS p
     INNER JOIN tbl_Actual_PROC_Point AS a ON p.PRON_ID = a.PRON_ID)
    INNER JOIN tbl_Current_PROC_Point AS c ON a.CurrentProcPointID = c.CurrentProcPointID
WHERE
    p.PRON_ID = [TempVars]![PRON_ID]
ORDER BY
    a.ProcPointStartDate, a.TimeStamp;

To fix it, I used a field I was already using, TimeStamp (Date/Time) as "tie breaker". My head hurts - subqueries have always been a bit of a black-box for me. Almost as intimidating as Class Modules...

NauticalGent · Friday at 9:25 PM

Hi Paul, yeah...not sure what happened there. I had pasted it into Excel and the did a copy and paste. I didn't see the garbage until after I had posted.

Gasman said:
John, there is a problem pasting tables direct here?

Pasting table to a post

Copying and pasting a table or query to a post looks as expected until you save it - then its all code and unreadable. e.g. I copy/paste: but go to preview or save and it looks like this: what an I missing? Query1 PricesOrderedFromFK PricesQuantity RegisteredOn PricesRegisteredBy...

www.access-programmers.co.uk

cheekybuddha · Friday at 9:26 PM

I'm always curious why you need to insert data that can be got with a select query into another table when you could just use the query instead?

NauticalGent · Friday at 9:36 PM

Funny you mention that! I was using a temp table to hold the data to populate a report. While I was working it out (about a year ago), I thought that would be the easiest way to go.

At some point today, while I was mucking about, I realized I could just use the query as a select and base the report off that...

cheekybuddha · Friday at 9:39 PM

Sometimes speed is the issue, or a requirement to have data frozen at a fixed point in time, but often it seems to be done unnecessarily.

Using the SELECT query directly is usually safer since it will reflect live data and not need to be updated when underlying data changes.

NauticalGent · Friday at 9:44 PM

cheekybuddha said:
Sometimes speed is the issue, or a requirement to have data frozen at a fixed point in time, but often it seems to be done unnecessarily.

Using the SELECT query directly is usually safer since it will reflect live data and not need to be updated when underlying data changes.

Noted, thanks again. And...if the query could be optimized further, please tell me. I'm glad I got it working, but I am not too proud of it to know that it could use some polishing

cheekybuddha · Friday at 9:44 PM

Also curious as to how different this query would be from yours

SQL:

SELECT
  p.PRON,
  a1.TimeStamp,
  c.[Current Proc Point],
  a1.ProcPointStartDate,
  Nz(a1.EndDate, Date()) AS EndDate,
  Int(Nz(a1.EndDate, Date()) - a1.ProcPointStartDate) AS TimeInProc,
  a1.[Updated By],
  c.PALT
FROM (
  tbl_PRON AS p
  INNER JOIN (
    SELECT
      a.PRON_ID,
      a.CurrentProcPointID,
      a.TimeStamp,
      a.ProcPointStartDate,
      (
        SELECT TOP 1
          b.ProcPointStartDate
        FROM tbl_Actual_PROC_Point AS b
        WHERE b.ProcPointStartDate > a.ProcPointStartDate
          AND b.Timestamp > a.Timestamp
      ) AS EndDate,
      a.[Updated By]
    FROM tbl_Actual_PROC_Point AS a
  ) AS a1
          ON p.PRON_ID = a1.PRON_ID
)
INNER JOIN tbl_Current_PROC_Point AS c
        ON a1.CurrentProcPointID = c.CurrentProcPointID
WHERE p.PRON_ID = [TempVars]![PRON_ID]
ORDER BY
  a1.ProcPointStartDate;

NauticalGent · Friday at 10:05 PM

Results are not quite what I am looking for. There can be no gaps in the timeline.

Here is what your query produced:
Query3
PRON TimeStamp Current Proc Point ProcPointStartDate EndDate TimeInProc Updated By PALT
AX6F2452AX 12/12/2025 12:40:31 PM PRON Received Buyer 12/12/2025 12/13/2025 1 Christopher Butler Yes
AX6F2452AX 12/12/2025 1:42:14 PM Planning 12/13/2025 12/15/2025 2 P9CTT - Matt Tatum Yes
AX6F2452AX 1/8/2026 12:05:41 PM Solicitation 1/8/2026 1/9/2026 1 P9CTT - Matt Tatum Yes

Here is the desired results:

tmp_TimeInPhase
PRON TimeStamp Current Proc Point ProcPointStartDate EndDate TimeInProc Updated By PALT
AX6F2452AX 12/12/2025 12:40:31 PM PRON Received Buyer 12/12/2025 12/13/2025 1 Christopher Butler -1
AX6F2452AX 12/12/2025 1:42:14 PM Planning 12/13/2025 1/8/2026 26 P9CTT - Matt Tatum -1
AX6F2452AX 1/8/2026 12:05:41 PM Solicitation 1/8/2026 1/9/2026 1 P9CTT - Matt Tatum -1

NauticalGent · Friday at 10:13 PM

Figured this is easier to read.

Query3
PRON	TimeStamp	Current Proc Point	ProcPointStartDate	EndDate	TimeInProc	Updated By	PALT
AX6F2452AX	12/12/2025 12:40:31 PM	PRON Received Buyer	12/12/2025	12/13/2025	1	Christopher Butler	Yes
AX6F2452AX	12/12/2025 1:42:14 PM	Planning	12/13/2025	12/15/2025	2	P9CTT - Matt Tatum	Yes
AX6F2452AX	1/8/2026 12:05:41 PM	Solicitation	1/8/2026	1/9/2026	1	P9CTT - Matt Tatum	Yes
tmp_TimeInPhase
PRON	TimeStamp	Current Proc Point	ProcPointStartDate	EndDate	TimeInProc	Updated By	PALT
AX6F2452AX	12/12/2025 12:40:31 PM	PRON Received Buyer	12/12/2025	12/13/2025	1	Christopher Butler	-1
AX6F2452AX	12/12/2025 1:42:14 PM	Planning	12/13/2025	1/8/2026	26	P9CTT - Matt Tatum	-1
AX6F2452AX	1/8/2026 12:05:41 PM	Solicitation	1/8/2026	1/9/2026	1	P9CTT - Matt Tatum	-1

cheekybuddha · Friday at 10:30 PM

Interesting!

I wonder where that 12/152025 came from in the second record.

My guess it came from the Timestamp tie-splitter.

I know you have a solution, so no worries if you can't be bothered, I'm still curious if either of the following variations might work (since I have no data to test with!):

SQL:

-- ...
  INNER JOIN (
    SELECT
      a.PRON_ID,
      a.CurrentProcPointID,
      a.TimeStamp,
      a.ProcPointStartDate,
      (
        SELECT TOP 1
          b.ProcPointStartDate
        FROM tbl_Actual_PROC_Point AS b
        WHERE b.ProcPointStartDate > a.ProcPointStartDate
          AND b.Timestamp >= a.Timestamp
      ) AS EndDate,
      a.[Updated By]
    FROM tbl_Actual_PROC_Point AS a
  ) AS a1
-- ...

or:

SQL:

-- ...
  INNER JOIN (
    SELECT
      a.PRON_ID,
      a.CurrentProcPointID,
      a.TimeStamp,
      a.ProcPointStartDate,
      (
        SELECT TOP 1
          b.ProcPointStartDate
        FROM tbl_Actual_PROC_Point AS b
        WHERE b.ProcPointStartDate > a.ProcPointStartDate
        ORDER BY
          b.ProcPointStartDate,
          b.Timestamp
      ) AS EndDate,
      a.[Updated By]
    FROM tbl_Actual_PROC_Point AS a
  ) AS a1
-- ...

NauticalGent · Friday at 10:32 PM

It is no bother at all...and if you want a small mock-up to play with I can port it over from my work computer and upload it.

NauticalGent · Friday at 10:38 PM

cheekybuddha said:

Interesting!

I wonder where that 12/152025 came from in the second record.

My guess it came from the Timestamp tie-splitter.

I know you have a solution, so no worries if you can't be bothered, I'm still curious if either of the following variations might work (since I have no data to test with!):

SQL:

-- ...
  INNER JOIN (
    SELECT
      a.PRON_ID,
      a.CurrentProcPointID,
      a.TimeStamp,
      a.ProcPointStartDate,
      (
        SELECT TOP 1
          b.ProcPointStartDate
        FROM tbl_Actual_PROC_Point AS b
        WHERE b.ProcPointStartDate > a.ProcPointStartDate
          AND b.Timestamp >= a.Timestamp
      ) AS EndDate,
      a.[Updated By]
    FROM tbl_Actual_PROC_Point AS a
  ) AS a1
-- ...

or:

SQL:

-- ...
  INNER JOIN (
    SELECT
      a.PRON_ID,
      a.CurrentProcPointID,
      a.TimeStamp,
      a.ProcPointStartDate,
      (
        SELECT TOP 1
          b.ProcPointStartDate
        FROM tbl_Actual_PROC_Point AS b
        WHERE b.ProcPointStartDate > a.ProcPointStartDate
        ORDER BY
          b.ProcPointStartDate,
          b.Timestamp
      ) AS EndDate,
      a.[Updated By]
    FROM tbl_Actual_PROC_Point AS a
  ) AS a1
-- ...

Same results as before...

cheekybuddha · Friday at 10:49 PM

Ah! I completely missed the PRON_ID!

How about this:

SQL:

-- ...
  INNER JOIN (
    SELECT
      a.PRON_ID,
      a.CurrentProcPointID,
      a.TimeStamp,
      a.ProcPointStartDate,
      (
        SELECT TOP 1
          b.ProcPointStartDate
        FROM tbl_Actual_PROC_Point AS b
        WHERE b.PRON_ID = a.PRON_ID
          AND b.ProcPointStartDate > a.ProcPointStartDate
        ORDER BY
          b.ProcPointStartDate,
          b.Timestamp
      ) AS EndDate,
      a.[Updated By]
    FROM tbl_Actual_PROC_Point AS a
  ) AS a1
-- ...

NauticalGent · Friday at 11:13 PM

Here is some data, and the query to play with. You will have to explicitly set the TempVar, but I am sure you would have worked that out...

Solved Help with subqueries (1 Viewer)

Ignore List Poster Boy

Enthusiastic Amateur

AWF VIP

AWF VIP

AWF VIP

AWF VIP

Ignore List Poster Boy

Ignore List Poster Boy

AWF VIP

Ignore List Poster Boy

AWF VIP

Ignore List Poster Boy

AWF VIP

Ignore List Poster Boy

Ignore List Poster Boy

AWF VIP

Ignore List Poster Boy

Ignore List Poster Boy

AWF VIP

Ignore List Poster Boy

Attachments

Similar threads

Users who are viewing this thread