0

I've defined a query that filters out records that are null in a specific field. I'd like to also calculate a query field that returns the type of record that follows the record that was filtered out, if it matches the parameters. The way I thought to do this was with an IIf statement with multiple parameters:

Preparing: IIf([tblCustomers!OrderId]=([tblCustomers!OrderId]+1)
    AND [tblCustomers!OrderStatus]="Preparing","Preparing","")

This didn't work as I hoped, but I wasn't too surprised, as it would have to return data from the field initially tested. So, the argument that adds 1 is actually doing nothing.

Is there a way to target the next record in the table, test if it matches one of two or three strings, then return which one it is?

Edit: Following @mazoula's solution, it seems a correlated subquery is indeed the answer here. Following the guide on allenbrowne.com (linked by June7), I seemed to be on the right track. Here is my code for retrieving the status of a previous record:

SELECT tblCustomers.AccountId, 
tblCustomers.OrderId, 
tblCustomers.OrderStatus,
tblCustomers.OrderShipped,
tblCustomers.OrderNotes,
    (SELECT TOP 1 Dupe.OrderStatus                 
    FROM tblCustomers AS Dupe                     
    WHERE Dupe.AccountId = tblCustomers.AccountId 
        AND Dupe.OrderId > tblCustomers.OrderId   
    ORDER BY Dupe.AccountId DESC, Dupe.OrderId) AS NextStatus
FROM tblCustomers
WHERE (((tblCustomers.OrderShipped)="N") AND 
((tblCustomers.OrderNotes) Is Null))
ORDER BY tblCustomers.AccountId DESC;

Unfortunately, I am met with the following error:

At most one record can be returned by this subquery

Doing a little more research, I found that incorporating an INNER JOIN expression should solve this.

...
FROM tblCustomers
INNER JOIN OrderStatus Dupe ON Dupe.AccountId = tblCustomers.AccountId
WHERE ...

This is where I've hit another roadblock and, when the syntax is at least correct, I receive the error:

Join expression not supported.

Is this a simple syntax issue, or have misunderstood the role of a Join expression?

Kyle Barnes
  • 29
  • 10
  • Some sample data and the expected output would help a lot. – Gustav Jul 12 '22 at 19:55
  • 1
    For a record to 'see' data in another record of same table in query requires correlated subquery, or self-join query, or DLookup, or help from a VBA custom function - for a start review http://allenbrowne.com/subquery-01.html#AnotherRecord – June7 Jul 13 '22 at 02:43

1 Answers1

1

in Access 2016 I do this in two parts because access throws the error: must use an updateable query when I try to update based on a subquery. For instance, if I want to replace the Null Values in TableA.Field3 with 'a' if the next record's Field3 is 'a'

tableA:
-------------------------------------------------------------------------------------
|         ID         |       Field1       |       Field2       |       Field3       |
-------------------------------------------------------------------------------------
|                  1 | a                  |                  1 |                    |
-------------------------------------------------------------------------------------
|                  2 | b                  |                  2 |                    |
-------------------------------------------------------------------------------------
|                  3 | c                  |                  3 | a                  |
-------------------------------------------------------------------------------------
|                  4 | d                  |                  4 | b                  |
-------------------------------------------------------------------------------------
|                  5 | e                  |                  5 |                    |
-------------------------------------------------------------------------------------
|                  6 | f                  |                  6 | b                  |
-------------------------------------------------------------------------------------

I make a table on which to base the update query:

enter image description here

Replacement: (SELECT TOP 1 Dupe.Field3 FROM [TableA] as Dupe WHERE Dupe.ID > [TableA].[ID])

'SQL PANE'

SELECT TableA.ID, TableA.Field1, TableA.Field2, TableA.Field3, (SELECT TOP 1 Dupe.Field3 FROM [TableA] as Dupe WHERE Dupe.ID > [TableA].[ID]) AS Replacement INTO TempTable
FROM TableA;

TempTable:

----------------------------------------------------------------------------------------------------------
|         ID         |       Field1       |       Field2       |       Field3       |    Replacement     |
----------------------------------------------------------------------------------------------------------
|                  1 | a                  |                  1 |                    |                    |
----------------------------------------------------------------------------------------------------------
|                  2 | b                  |                  2 |                    | a                  |
----------------------------------------------------------------------------------------------------------
|                  3 | c                  |                  3 | a                  | b                  |
----------------------------------------------------------------------------------------------------------
|                  4 | d                  |                  4 | b                  |                    |
----------------------------------------------------------------------------------------------------------
|                  5 | e                  |                  5 |                    | b                  |
----------------------------------------------------------------------------------------------------------
|                  6 | f                  |                  6 | b                  |                    |
----------------------------------------------------------------------------------------------------------

Finally do the Update

enter image description here

UPDATE TempTable INNER JOIN TableA ON TempTable.ID = TableA.ID SET TableA.Field3 = [TempTable].[Replacement]
WHERE (((TempTable.Replacement)='a'));

TableA after update

-------------------------------------------------------------------------------------
|         ID         |       Field1       |       Field2       |       Field3       |
-------------------------------------------------------------------------------------
|                  1 | a                  |                  1 |                    |
-------------------------------------------------------------------------------------
|                  2 | b                  |                  2 | a                  |
-------------------------------------------------------------------------------------
|                  3 | c                  |                  3 | a                  |
-------------------------------------------------------------------------------------
|                  4 | d                  |                  4 | b                  |
-------------------------------------------------------------------------------------
|                  5 | e                  |                  5 |                    |
-------------------------------------------------------------------------------------
|                  6 | f                  |                  6 | b                  |

notes: In the Make Table query remember to sort TableA and Dupe in the same way. Here we use the default sort of increasing ID for TableA then grab the first record with a higher ID using the default sort again. the only reason I did the filtering to 'a' in the update query is it made the Make Table query simpler.

mazoula
  • 1,221
  • 2
  • 11
  • 20
  • 1
    Use the OR column in the query designer for additonal conditions – mazoula Jul 13 '22 at 07:13
  • 1
    replacement is an example of a correlated subquery – mazoula Jul 13 '22 at 07:18
  • Thank you for the helpful response. Correlated subqueries got me on the right track, but I'm still having some issues. Please see my edit in the original post. – Kyle Barnes Jul 13 '22 at 17:47
  • You edited the question too much after you got an answer. If possible un-edit. Then ask your new question using a minimum reproduceable example: https://stackoverflow.com/help/minimal-reproducible-example Basically, reproduce the tables with minimal data and save the tables as text files, then add the tables to the new question so they can be copied easily, then show your attempted code and what you want the code to do, There is a good chance you solve the problem doing this as well. – mazoula Jul 14 '22 at 05:00
  • How does one edit a question too much? I retained the initial inquiry and added information distinctly labeled, _Edit_, which I hoped would help explain what my code was meant to produce, how it changed as I researched solutions, and what my lasting issues were. I can add dummy tables with minimal data, but it seemed unnecessary to do so. This was a question of SQL fundamentals, expressions, and best practices. I was not troubleshooting syntax errors or asking for help with such, which might have benefited from a dummy table. – Kyle Barnes Jul 14 '22 at 13:46
  • I'm sorry about the edited too much, after scrolling down and seeing the edited section of the question I thought it was fine as well, but I left the comment in because I wasn't sure. After the question is edited, I can't see the original question and I couldn't follow my path from the question to the answer. Most likely, I just inferred a lot into the question based on experience. – mazoula Jul 15 '22 at 21:59
  • correlated subqueries are relatively difficult to learn, and more questions have now been asked than I can clear up in a comment. I tried then deleted them. Several things are going on so to make progress I suggest the following: Hopefully, my answer is a minimal reproduceable example. try reproducing it and edit it into your question. Specifically add variables, change names, change variable types, and finally replace tableA with a query. However you ramp up your skills, ask a new question as now that this one is marked as answered only I am likely to see it. – mazoula Jul 15 '22 at 23:27