How to update date field in table using another table.

[hex6007]

Please help me on this,

I have 2 Tables A and B. I want to update the transaction date field in Table A where doesnt match the LAST transaction date field in Table B.

thanks in advance

 

[fneily]

Why do you have two transaction date fields in two separate tables? Are you tables normalized? For clarity, you may want to post your table structures, eg.
tblTransaction
TransID
TransDate
Quantity
etc.

 

[dhookom]

The "LAST" transaction date is a bit confusing since "LAST" is often mis-used in queries. I believe you really want the most recent of "MAX" transaction date.

As fneily was getting at, this value is generally calculated on the fly and not stored. If you have a strong need to store the max transaction date, you can use an update query with DMax(). If you can't figure this out, come back with your table and significant field names.

Duane
Hook'D on Access
MS Access MVP

 

[hex6007]

tblTransaction
AccountID
TranID
Amount
DateTransactn
CustomerID
ProductID
RunningBal

tblAccounts
AccountID
ProductID
Status
ActualBal
DateClosed

My problem is to update the DateClosed field from tblAccounts that doesnt match the DateTransctn field in tblTransaction, where there are 2 or more transaction of the same date per AccountID. so i want to update the DateClosed(tblAccounts) of the last DateTransactn(tblTransaction)if there is more than 1 transaction of the same date.

 

[hex6007]

i made a query for the tblTransaction:

SELECT Transactn.AccountID, Last(Transactn.DateTransactn) AS LastOfDateTransactn, Last(Transactn.Amount) AS LastOfAmount, Transactn.CustomerID, Last(Transactn.TranID) AS LastOfTranID
FROM Transactn
GROUP BY Transactn.AccountID, Transactn.CustomerID
HAVING (((Last(Transactn.TranID))="007"));

how do i update the tblAccounts, DateClosed field to match the DateTransactn of the query i made from tblTransaction.

 

[dhookom]

Did you understand what I stated regarding "Last"? You can't update any query that includes a GROUP BY. That's why I suggested you consider using DMax().

UPDATE tblAccounts
SET DateClosed = DMax("DateTransactn","Transactn","AccountID=" & [AccountID])

This assumes AccountID is numeric.

Duane
Hook'D on Access
MS Access MVP

 

[hex6007]

To dhookom thanks a lot.
But another thing, how if i add conditions where Status='C',CustomerID='1' and ProductID='LD'?

 

[dhookom]

Where do you want the conditions? Do you want these in the DMax() function? If so, try:

UPDATE tblAccounts
SET DateClosed = DMax("DateTransactn","Transactn","AccountID=" & [AccountID] & " AND Status='C' AND CustomerID='1' and ProductID='LD'")

Duane
Hook'D on Access
MS Access MVP

 

[hex6007]

on the accounts table pls

 

[hex6007]

To dhookom:
It cant update the records in the update query-an error occur.

 

[dhookom]

hex6007,
If you want help, you need to learn how to reply in threads. First, you should provide your SQL statement that can't update. Second, you should provide the data types of significant fields. Third, always provide the error message and/or your results.

Without this basic information, we can only guess at issues.

Duane
Hook'D on Access
MS Access MVP

 

[hex6007]

Sorry for that. I successfully run the query that you gave then and it prompts the number of rows to be updated when i click the YES button, an error "Microsoft Access can't update all the records in the update query". Beneath, message follows, "Microsoft Access didnt update 1182 field(s) due to type conversion failure, 0 record(s) due to key violations,0 record(s) due to lock violations, and 0 reocrd(s) due to valid rule violations".

 

[dhookom]

Second, you should provide the data types of significant fields.

"type conversion failure"

Duane
Hook'D on Access
MS Access MVP

 

[hex6007]

tblAccounts
Data type:
AccountID = Text
DateClosed = DAte/Time


tblTransaction
Data Type:
AccountID = Text
DateTransactn = Date/Time

 

[dhookom]

Since AccountID is text, you must wrap it in delimiters. Try:

UPDATE tblAccounts
SET DateClosed = DMax("DateTransactn","Transactn","AccountID='" & [AccountID] & "' AND Status='C' AND CustomerID='1' and ProductID='LD'")

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I should have also confirmed the data type of CustomerID since your [red]CustomerID='1'[/red] might be wrong.

Duane
Hook'D on Access
MS Access MVP

 

[hex6007]

WOW works perfect..Thank you very much... Btw, with regards to the error that occured, i checked the accounts table i found out that there are no records on the DateClosed column.Sorry for that... Thanks a lot Dhookom...