Is this doable in MS Access Query? (1 Viewer)

spenzer · Apr 1, 2012

How do i make an update query in order to update all blank fields below with each of the letters above it?
I have below an illustrated sample of what I'm trying to achieve.

(I need to update the left table to become the right table.)

(left)Table1
Letters
A
--
--
--
B
--
--
C
--
--
--
--

(right) Table1
Letters
A
A
A
A
B
B
B
C
C
C
C
C

I've tried this: (but it updates every unpaid dates based on the MAX payment date only.)

Code:

UPDATE tblUnpaidBalances SET tblUnpaidBalances.paydate = DMax("paydate","tblunpaidbalances","Transactionid = " & "transactionid")
WHERE (((tblUnpaidBalances.paydate) Is Null));

And then I've even tried subquery (but it won't allow me to update, it says "Operation must use an updateable query") which is weird because i can open it in select query.

Code:

UPDATE tblUnpaidBalances SET tblUnpaidBalances.paydate = (select max(paydate) from tblunpaidbalances as ub where ub.transactionid = tblunpaidbalances.transactionid)
WHERE (((tblUnpaidBalances.paydate) Is Null));

plog · Apr 1, 2012

I've tried this: (but it updates every unpaid dates based on the MAX payment date only.)

Did you really think a query that doesn't reference the field 'Letters' would somehow be able to update it?

From what I've seen of the other fields in your table, I am pretty sure this can be accomplished. However, I need more data to be sure my solution will work. For your two sample tables could you please post the paydate and transactionid data for every record you posted above?

spenzer · Apr 1, 2012

@plog

Thank you for trying to help me solve this one.

I have linked an actual picture below of how the query actually looks, i hope it made the case more easier to understand.

I have a table named:
tblUnpaidBalances
tblTransactions

with columns:
UnpaidBalanceID(PK), Paydate, TransactionID(FK)

***I am attempting to update every Null payment dates except for the new record with every payment dates that the client have made for that transaction.

http://www.flickr.com/photos/sleeplessrevolution/6888024528/

sparks80 · Apr 1, 2012

Try this:

UPDATE tblTest SET tblTest.PayDate = DMax("PayDate","tblTest","TransactionID=" & [TransactionID])
WHERE (((tblTest.PayDate) Is Null));

This should update all null paydates for a given transaction ID to the latest paydate.

spenzer · Apr 1, 2012

sparks80 said:
Try this:

UPDATE tblTest SET tblTest.PayDate = DMax("PayDate","tblTest","TransactionID=" & [TransactionID])
WHERE (((tblTest.PayDate) Is Null));

This should update all null paydates for a given transaction ID to the latest paydate.

Thank you sparks for thinking about my case.

I have already tried that one; and it indeed updated all paydates into the latest paydate. However, the one i am trying to solve is updating all Null paydates below every payments made by the client.

As you can see in the image or link i've pasted, there are null paydates below other made paydates and all that nulls must be updated with whatever listed paydate above it.

If it's the maximum paydate is the one im looking. Then it is supposed to be easy as i wish, but this one is a bit different because criteria is based on multiple payments. been thinking about this one since early morning and darn it made me a bit hungry mate.

sparks80 · Apr 1, 2012

in your example the unpaid balance increases with date. Is this always the case or does it vary?

spenzer · Apr 1, 2012

sparks80 said:
in your example the unpaid balance increases with date. Is this always the case or does it vary?

Oh sorry about the image partially covering the column name which is actually UnpaidBalanceID it is the Primary key of tblUnpaidBalances. If you are pertaining to the balance amount of the table, it is not displayed and it decreases every time a payment is made.

sparks80 · Apr 1, 2012

Presumably the UnpaidBalanceID is an Autonumber, and will therefore increase by default with time. That might just be the solution to the problem, because you can limit the number of records that the DMax statement looks at using the UnpaidBalanceID.

How about this?

UPDATE tblTest SET tblTest.PayDate = DMax("PayDate","tblTest","TransactionID=" & [TransactionID] & "AND UnpaidBalanceID>=" & [unpaidbalanceID])
WHERE (((tblTest.TransactionID) Is Null));

spenzer · Apr 1, 2012

sparks80 said:
Presumably the UnpaidBalanceID is an Autonumber, and will therefore increase by default with time. That might just be the solution to the problem, because you can limit the number of records that the DMax statement looks at using the UnpaidBalanceID.

How about this?

UPDATE tblTest SET tblTest.PayDate = DMax("PayDate","tblTest","TransactionID=" & [TransactionID] & "AND UnpaidBalanceID>=" & [unpaidbalanceID])
WHERE (((tblTest.TransactionID) Is Null));

Thanks again sparks. Looks promising!

i'll play with this tomorrow and will let you know. goin' home now.

spenzer · Apr 3, 2012

sparks80 said:
Presumably the UnpaidBalanceID is an Autonumber, and will therefore increase by default with time. That might just be the solution to the problem, because you can limit the number of records that the DMax statement looks at using the UnpaidBalanceID.

How about this?

UPDATE tblTest SET tblTest.PayDate = DMax("PayDate","tblTest","TransactionID=" & [TransactionID] & "AND UnpaidBalanceID>=" & [unpaidbalanceID])
WHERE (((tblTest.TransactionID) Is Null));

Howdy! I've tested this and it populates all the null paydates with the maximum paydate in that transaction. So i tried reversing it into <= or < the result is slightly better since it gets all the payment made except the oldest payment. and i still have blank paydates on the oldest records for that transaction.

by the way, i also used equal (=) sign too. But it didn't affect any record at all.

but it's really close.. i still think there's a simple solution somewhere that I just missed or maybe this is part or access's limitation in update query.

sparks80 · Apr 3, 2012

The reason for the remaining null values is that no paydate can be found for the given transaction where the unpaidbalance ID is less than 393 or 114 (in the case you gave).

What date do you want to assign to these oldest transactions? Possibly the absolute minimum paydate for that transaction?

You can use NZ to test if the result from my previous suggestion is null, and instead return the minimum paydate for that transaction.

Try this:

UPDATE tblTest SET tblTest.PayDate = Nz(DMax("PayDate","tblTest","TransactionID=" & [TransactionID] & "AND UnpaidBalanceID>=" & [unpaidbalanceID]), DMin("PayDate", "tblTest", ""))
WHERE (((tblTest.TransactionID) Is Null));

Pat Hartman · Apr 3, 2012

This is not something you can do with a query. The query has no way of identifying a set of records that should have the same date. You will need to do it with DAO or ADO if you prefer. Create a query that selects all the rows and orders them descending by ID. The Order By is CRITICAL since queries are unordered sets and the order of the records is what determines how the paydate will be populated. You'll create a code loop that moves record by record through the table and updates the current record with the paydate from the previous record when the current paydate is null.

this is psuedo code and is intended to provide a guide for you to write the real procedure.

Code:

Dim db as DAO.Database
Dim rs as DAO.RecordSet
Dim qd as DAO.QueryDef
Dim prevDate as Date
Set db = CurrentDB()
set qd = db.Querydefs("MyQueryName")
set rs = qd.OpenRecordSet
Do Until rs.EOF = True
    If IsDate(rs!TranDate) Then
        prevDate = rs!TranDate
    else
        if IsDate(prevDate) Then
            rs.Edit
            rs!TranDate = prevDate
            rs.Update
        End If
    End If
    rs.MoveNext
Loop
rs.Close

spenzer · Apr 4, 2012

sparks80 said:
The reason for the remaining null values is that no paydate can be found for the given transaction where the unpaidbalance ID is less than 393 or 114 (in the case you gave).

What date do you want to assign to these oldest transactions? Possibly the absolute minimum paydate for that transaction?

You can use NZ to test if the result from my previous suggestion is null, and instead return the minimum paydate for that transaction.

Try this:

UPDATE tblTest SET tblTest.PayDate = Nz(DMax("PayDate","tblTest","TransactionID=" & [TransactionID] & "AND UnpaidBalanceID>=" & [unpaidbalanceID]), DMin("PayDate", "tblTest", ""))
WHERE (((tblTest.TransactionID) Is Null));

Thanks for getting back at the case sparks.

Though, your solution is an inch away for getting it to work; I think that the main problem with this update query solution is that no matter what sign we use when referring to the primary key(UnpaidBalanceID) is that it will ultimately miss some of the paydates needed to be referred to, in order for the Null paydates to be populated.

To break it down:
If i use Greater than with equal sign (>=) all of the blank paydates will only be populated by whatever is the highest maximum paydate in that transaction. so clearly this is not what we want.

If I use Less than with equal sign (<=) this will miss the highest paydate of the transaction. So although, when you look at below some populated paydates; it seems to get it, it does not, because the blank paydates below the maximum paydate is actually populated by the second highest paydate not the other way around which is incorrect.

Now with equal sign (=) alone, it is understandably cannot populate anything because it won't refer to anything.

With this situation, I think it's safe to say that we've reached the dead end with this ms access update query.

Thank you and I appreciate that you have stuck with my case.

spenzer · Apr 4, 2012

Pat Hartman said:
This is not something you can do with a query. The query has no way of identifying a set of records that should have the same date. You will need to do it with DAO or ADO if you prefer. Create a query that selects all the rows and orders them descending by ID. The Order By is CRITICAL since queries are unordered sets and the order of the records is what determines how the paydate will be populated. You'll create a code loop that moves record by record through the table and updates the current record with the paydate from the previous record when the current paydate is null.

this is psuedo code and is intended to provide a guide for you to write the real procedure.

Code:

Dim db as DAO.Database Dim rs as DAO.RecordSet Dim qd as DAO.QueryDef Dim prevDate as Date Set db = CurrentDB() set qd = db.Querydefs("MyQueryName") set rs = qd.OpenRecordSet Do Until rs.EOF = True If IsDate(rs!TranDate) Then prevDate = rs!TranDate else if IsDate(prevDate) Then rs.Edit rs!TranDate = prevDate rs.Update End If End If rs.MoveNext Loop rs.Close

Thank you pat for having an interest with my case.

I will certainly try this solution with my current tricky case and i'll get back on you with whatever results i will get.

spenzer · Apr 4, 2012

@Pat

Your suggestion almost worked! Except for one minor quirk. Notice the 12:00 on the new record? I don't how it got there because it should be Null because it is a new record; but every time i execute the code, every new record from a transaction will get this 12:00 instead of being blank.

So in fact, if there are (5) records is a single unpaid transaction all (5) records will have "12:00" as a paydate instead of leaving it at null.

as you can see from the illustration below

---12:00 -->This one should be blank since it is the newest record.
5/17/2011
---5/17/2011
---5/17/2011
---5/17/2011
---5/17/2011
---5/17/2011
---5/17/2011
---5/17/2011
8/23/2010
---8/23/2010
---8/23/2010
5/14/2010
---5/14/2010
3/12/2010
1/27/2010
11/17/2009
---11/17/2009
---11/17/2009

And this is the exact vba code using your guide:

Code:

Private Sub cmdFix_Click()

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim PrevDate As Date
  
    Set db = CurrentDb
            
    Set rs = db.OpenRecordset("SELECT * FROM tblUnpaidBalances " & _
                "WHERE TransactionID = " & Me.TransactionID " & _
                "ORDER BY UnpaidBalanceID DESC")

                              
    Do Until rs.EOF = True
            
        If IsDate(rs!PayDate) Then
            PrevDate = rs!PayDate
        
        Else
            If IsDate(PrevDate) Then
                rs.Edit
                rs!PayDate = PrevDate
                rs.Update
            End If
        End If
            rs.MoveNext
            Me.sbfUb.Requery
            
    Loop
        rs.Close
        Set rs = Nothing
        
End Sub

Pat Hartman · Apr 4, 2012

The code doesn't look like it is updating rows when it has no valid date so I think it is working. Check your table definition. Make sure you don't have a default defined for the date field. It looks like a 0 is being stored in the date field when you add a record.
PS:
I suggested a saved querydef for a reason. Using SQL strings embedded in code is the biggest cause of database bloat. When you create a querydef and save it, Access creates an execution plan for how to get the requested data and saves it. Then when you go to run the query, the plan has already been created so the query engine just uses it. However, with SQL strings in code, Access needs to build the execution plan each and every time you run the query. Calculating the plan takes a fair amount of workspace (and also some time) and that workspace cannot be recovered until the database is compacted. So for applications that rely heavily on this technique, you can literally watch them grow right in front of your eyes.

To pass a parameter into a query just give it a value.
set qd = db.querydefs!yourqueryname
qd.parameters!parmName = Me.someformfield
qd.parameters!parmName2 = Me.someotherfieldname

The query would be:
select ... from ... where somefield = [parmName] and someotherfield = [parmName2];

sparks80 · Apr 4, 2012

Thought I'd take one last crack at getting an SQL UPDATE query to work. I hadn't appreciated which way round you wanted the dates to go which wasn't helping. The upshot is that I think you need DMin rather than DMax, changing from >= to <= wouldn't help, although my previous posts were wrong - sorry.

PHartman's advice and VBA code is right, but I still think there is a clever way to retrieve the date you want by limiting the recordset using the UnpaidBalanceID, and using DMin to get the previous value.

In words this should change all null dates to "the minimum date that has an UnpaidBalanceID greater than or equal to the current record"

I tested this using the following for the new date:
NewPayDate: DMin("PayDate","tblTest","TransactionID=" & [TransactionID] & "AND UnpaidBalanceID>=" & [unpaidbalanceID])

And the resulting SQL:
UPDATE tblTest SET tblTest.PayDate = DMin("PayDate","tblTest","TransactionID=" & [TransactionID] & "AND UnpaidBalanceID>=" & [unpaidbalanceID])
WHERE (((tblTest.PayDate) Is Null));

The results I get match the ones you posted above, and the latest transaction remains null. Is this what you wanted for TransactionID 2758?

UnpaidBalanceID, NewPayDate
8562,
8561, 05/17/2011
8560, 05/17/2011
7759, 05/17/2011
7173, 05/17/2011
6806, 05/17/2011
6458, 05/17/2011
6005, 05/17/2011
5058, 05/17/2011
4691, 08/23/2010
4234, 08/23/2010
3593, 08/23/2010
3328, 05/14/2010
2671, 05/14/2010
1948, 03/12/2010
938, 01/27/2010
668, 11/17/2009
393, 11/17/2009
114, 11/17/2009

spenzer · Apr 6, 2012

@sparks80

Great work! Changing from DMax to DMin did worked beautifully. I've checked a couple of affected transactions and so far, so good. It is indeed updating all Blank PayDate/s in the same transaction with the previous payment dates made.

This is the now sql code for the working update query:

Code:

[B]UPDATE [/B]tblUnpaidBalances INNER JOIN tblTransactions ON tblUnpaidBalances.TransactionID = tblTransactions.TransactionID 
[B]SET [/B]tblUnpaidBalances.PayDate = DMin("paydate","tblUnpaidbalances","tblunpaidbalances.TransactionID = " & [tblUnpaidbalances].[TransactionID] & " AND UnpaidBalanceID >= " & [UnpaidBalanceID])
[B]WHERE [/B](((tblUnpaidBalances.PayDate) Is Null) AND ((tblTransactions.Closed)=0));

A BIG, WARM Thank you! for taking the time, effort and patience in helping me with my case mate.

spenzer · Apr 6, 2012

Howdy Pat!

I went through the tblUnpaidBalance table definition and there is no default PayDate value set whatsoever. In fact, it is not allowed have it automatically generated eversince.

However, I've checked the cmdReset button and i found this:

Code:

Private Sub cmdReset_Click()

Dim db As Database
Dim rs As DAO.Recordset
                
      Set db = CurrentDb
      Set rs = db.OpenRecordset _   
("SELECT * FROM tblUnpaidBalances WHERE TransactionID = " & Me.TransactionID AND Active = -1")
                
                Do Until rs.EOF
                    With rs
                    .Edit
                    !Debit = 0
                    !PayDate = Null
                    .Update
                    End With
			      rs.MoveNext
                Loop
                    rs.Close
                    db.Close
                    Set db = Nothing
                    Set rs = Nothing
                    
                   Me.Requery
				   
End Sub

Tell me, do setting PayDate into Null would probably exhibit that weird "12:00" from appearing?

Take note, that "12:00" only appears when i executed the code you have suggested. In normal circumstance, PayDate field is always empty when it is a new record or when it has been resetted. So I haven't really encountered that value until now.

spenzer · Apr 6, 2012

Pat Hartman said:
The code doesn't look like it is updating rows when it has no valid date so I think it is working. Check your table definition. Make sure you don't have a default defined for the date field. It looks like a 0 is being stored in the date field when you add a record.
PS:
I suggested a saved querydef for a reason. Using SQL strings embedded in code is the biggest cause of database bloat. When you create a querydef and save it, Access creates an execution plan for how to get the requested data and saves it. Then when you go to run the query, the plan has already been created so the query engine just uses it. However, with SQL strings in code, Access needs to build the execution plan each and every time you run the query. Calculating the plan takes a fair amount of workspace (and also some time) and that workspace cannot be recovered until the database is compacted. So for applications that rely heavily on this technique, you can literally watch them grow right in front of your eyes.

To pass a parameter into a query just give it a value.
set qd = db.querydefs!yourqueryname
qd.parameters!parmName = Me.someformfield
qd.parameters!parmName2 = Me.someotherfieldname

The query would be:
select ... from ... where somefield = [parmName] and someotherfield = [parmName2];

@Pat

With regards to your suggested saved querydef as oppose to executing sql on the fly, I think that is a terrific advice; I didn't really knew that in the sense that it is more efficient and that will help alleviate my database from bloating.

I promise to look into that because i wanna code better too and frankly, I've got a long way to go.

Meanwhile, I mainly needed the code for one time use only. Because i wanna cover that particular hole (that Empty PayDates on old records of all transactions). And i think that, without that weird "12:00", the code you've suggested earlier is already doing its job perfectly.

Is this doable in MS Access Query? (1 Viewer)

Registered User.

Banishment Pending

Registered User.

Physicist

Registered User.

Physicist

Registered User.

Physicist

Registered User.

Registered User.

Physicist

Super Moderator

Registered User.

Registered User.

Registered User.

Super Moderator

Physicist

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread