Update existing tables with new data base on newest date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to write a query that will look through the master database table
and a file import table, compare duplicate account numbers, and only update
the master with new information based on the last payment date on said
account number. However, the date the original data was entered into the
master file has to remain the same. Can this be done with a query or do I
have to write a function to do this? Any help is greatly appreciated, tryin'
to keep my job here... lol.
 
On Mon, 16 May 2005 12:48:20 -0700, Nick Scarpinato <Nick
I'm trying to write a query that will look through the master database table
and a file import table, compare duplicate account numbers, and only update
the master with new information based on the last payment date on said
account number. However, the date the original data was entered into the
master file has to remain the same. Can this be done with a query or do I
have to write a function to do this? Any help is greatly appreciated, tryin'
to keep my job here... lol.
Click to expand...

An Update query should do this. Create a Query joining the master
table to the import table (by the account number). Put a criterion on
the import table transaction date like

=DMax("[PaymentDate]", "[ImportTable]", "[AccountNumber] = " &
[AccountNumber])

to select just the most recent transaction for each account number.

Make it an Update query and update whatever Master fields you want
updated to

=[ImportTable].[Fieldname]

using the appropriate table and field names.

John W. Vinson[MVP]
 
I have the query created but I'm not understanding where the dmax function
needs to be. Is that something that runs outside the query, or in the critera
field of the query? I tried putting it in the criteria field but I keep
getting compile errors.

John Vinson said:
On Mon, 16 May 2005 12:48:20 -0700, Nick Scarpinato <Nick
I'm trying to write a query that will look through the master database table
and a file import table, compare duplicate account numbers, and only update
the master with new information based on the last payment date on said
account number. However, the date the original data was entered into the
master file has to remain the same. Can this be done with a query or do I
have to write a function to do this? Any help is greatly appreciated, tryin'
to keep my job here... lol.
Click to expand...

An Update query should do this. Create a Query joining the master
table to the import table (by the account number). Put a criterion on
the import table transaction date like

=DMax("[PaymentDate]", "[ImportTable]", "[AccountNumber] = " &
[AccountNumber])

to select just the most recent transaction for each account number.

Make it an Update query and update whatever Master fields you want
updated to

=[ImportTable].[Fieldname]

using the appropriate table and field names.

John W. Vinson[MVP]
Click to expand...
 
I have the query created but I'm not understanding where the dmax function
needs to be. Is that something that runs outside the query, or in the critera
field of the query? I tried putting it in the criteria field but I keep
getting compile errors.
Click to expand...

It should be in the criteria field.

Please open your query in SQL view and copy and paste the SQL to a
message here. Also indicate the datatype (text, number - Long Integer,
....) of the fields that you're using in the DMax() function call.

John W. Vinson[MVP]
 
Back
Top