Order for an Update query (1 Viewer)

[mafhobb]

I use the following code to update data in a table (Address) based on another table (Customer):

    'Look at the table "Customers", select all "UPDATE" and edit the table.
        CurrentDb.Execute "UPDATE Customer INNER JOIN Address ON (Customer.addressType = Address.addressType) AND (Customer.customerId = Address.customerId) " & _
        "SET Address.addressNr = [Customer].[addressNr], Address.firstname = [Customer].[firstname], Address.lastname = [Customer].[lastname], Address.companyName = [Customer].[companyName], Address.postalcode = [Customer].[postalcode], Address.country = [Customer].[country], Address.workphone = [Customer].[workphone], Address.email = [Customer].[email], Address.notes = [Customer].[notes], Address.streetaddress = [Customer].[streetaddress], Address.city = [Customer].[city], Address.contactTypeId = [Customer].[contactTypeId]" & _
        "WHERE (((Customer.function)='UPDATE'));"

The update process works except that the order that it does is not what I expected when more than one UPDATE is needed for one record.

For example:

Let's say that the Address table looks like this:

CustomerID AddressType PostalCode
001 Customer 10000

and I update it with the following Customer table (three updates for the same record on the table):

CustomerID AddressType PostalCode
001 Customer 20000
001 Customer 30000
001 Customer 40000

I end up with and address table looking like this:

CustomerID AddressType PostalCode
001 Customer 20000

When I need to end up with this:
CustomerID AddressType PostalCode
001 Customer 40000

I guess I expected the code to go through the tables from top to bottom, but that does not seem to be the case. How can I tell it to do so?

Thanks

mafhobb

 

[Minty]

You can't guarantee the order , how would the database "know" that 4000 was your expected result? You would have to tell it by using a entry date or something similar.

 

[mafhobb]

So if on my customer table I have an incremental number like this:

Number CustomerID AddressType PostalCode
1 001 Customer 20000
2 001 Customer 30000
3 001 Customer 40000

I could get it to do Number 3 last?

 

[Minty]

Yes if the incremental number is definitely in the order you want it, simply use a Max() of that number

 

[mafhobb]

I can't find the syntax for an update query such as the one above with an ORDER BY or MAX() filter. Can it even be done?

 

[Minty]

You'll need to include a SQL query to get you the desired max record for each customer, then only update that record.

The easiest route would be to construct a select query in the design window returning just the last record for each customer and then change it to a Update query. That will give you the desired SQL statement.

 

[mafhobb]

uhmm. can't do that. The minute that the select query is turned into an Update query the totals row dissapears

 

[Minty]

Are you going to run this from code or dynamically from a form ?

If you save the first query and then make a second update query based on the first one it should work.

 

[mafhobb]

I was planning to do this with code just as you see in the query in the first post. However, I find it useful to work it out in design view first to try to understand how it works.

In this case, I created the first query in design view and it works:

SELECT Max(Customer.Number) AS MaxOfNumber, Customer.customerId, Customer.addressType, Customer.function
FROM Customer
GROUP BY Customer.customerId, Customer.addressType, Customer.function
HAVING (((Customer.function)="Update"));

However when I try to create the second query to use this first query, it tells me that I am referencing the same table with two queries and it won't let me go on. What am I missing?

 

[mafhobb]

It seems like the hard work was already done with the first query (first post)...all I needed was to get it done in order, and it is turning out to be much more complex than I thought!

 

[Minty]

Can you show me the second query you are trying to run ? You only need to join the MaxOfNumber to the original table to do the update.

 

[mafhobb]

Here is the database. It is meant to be used for importing xml files in sequential order into the table "customers" (called temporary xml import table in the main form)

You do not have the xml files but I have already imported them into the Customer table.

The code to then merge the data with the address table can be found in the on click envent of cmdMergeData. You can see what the final table looks like by clicking on cmdViewAddressTable.

The code works, but as I said, the UPDATE part of it does not work in the order seen in the customer table so the update process while it affects the correct records, it does not show the latest update . I am trying to create queries in the design wizard to take care of that (see qryMaxAddressUpdate (1,2,3)) but I keep getting errors.

What am I doing wrong?

If i looked at this reight, at the end of the process, I should have the records with "Update 13, Update 19 and Update 20" listed in the address table.

Thanks for looking into this

mafhobb

 

[Minty]

OKay - you are trying to do an update when you should be doing a INSERT.

You can't update records that don't exist. At the moment you are trying to UPDATE the empty Address table.

The process you have will require you to do two stages.

Import (INSERT) any new Customer ID's first.
Then amend(UPDATE) any existing records in the Address table using pretty much what you have.

 

[mafhobb]

Hi Minty,

If you look at the code in cmdMergeData, which is what almost works, you can see that the code breaks up the process in three steps:
1) it will first insert all records with the "function" field listed as "INSERT".
2) it will then go through the UPDATE part also based on the "function" field.
3) finally it will get the Delete part (which is actually an update + Delete queries)

The issue comes during step 2, which is not updating in the order listed in the temporary table (customer). At this point all the inserts are done and all I need is for the update query to update the records with the field "Function" with a value of "Update" in the same list as the customers table. Basically I need the first update to be the first in the table as you go down and the last update to the last in the table.

That's the only part that is just not getting done the way I need. The queries on the navigation bar are just tests that I was running trying to do what you suggested, using the "max" function.

 

[Minty]

Hi Mafhobb,

Sorry I was concentrating on the queries you had written, not the code...

On a quick look - and I'll come back to it later once I have had more time to look your Import should only import unique records, then you won't need to delete the duplicates. So a similar max of the ID of the account number.

I've become busier with real life work - let me have a look either later tonight or in the morning.

 

[mafhobb]

Thank you so much. One thing...right now this is all done on the same database (two tables storing prety much the same data...not good...), but once it all works, the Address table will be on a different database and this database will simply be on a timer to import the xml files and then save them on the Address table on the other db.

mafhobb

 

[mafhobb]

Does anyone have a suggestion to solve this complicated issue?

mafhobb

 

[Minty]

Hi Maf, sorry I have had some major issues at work, so no time to progress this - maybe later this week.

 

[mafhobb]

Hi Minty, Thanks for trying!