Update Query Where Join Results in Several Rows (1 Viewer)

shadow9449 · May 18, 2009

It's a common practice to use an update query to update a field in one table (let's call it Table1) based on joining a field in another table (let's call it table 2) and then retrieving a value from Table2 based on matching the keys and updating a row in Table1.

My question is: what if more than one row of Table2 meets the criteria of the join?

In other words, Table1 only has one row to update, but there are several possibilities in Table2 based on the join we created. How does Access decide which value to update the field on Table1 with?

In a Select query, what happens is the query will return all matching rows in Table2. However, an update query can only update the field once.

Thank you

SHADOW

Guus2005 · May 19, 2009

However, an update query can only update the field once.

is that so??
I believe that the value was changed as many times as there are records that match. But in the end the field contains only one value.
You can check this by sorting the linked table and see what the final value will be.

HTH

neileg · May 19, 2009

How do you know which value in Table2 is the right one?

Plus, if you can join the tables in a query, why do you need to duplicate the data in Table1?

shadow9449 · Jun 2, 2009

Guus2005 said:
I believe that the value was changed as many times as there are records that match. But in the end the field contains only one value.
You can check this by sorting the linked table and see what the final value will be.

HTH

Ah, this is what I suspected was happening.

If this is the case, how can you know what sequence it will perform the update so you can know what the final result would be?

SHADOW

shadow9449 · Jun 2, 2009

neileg said:
How do you know which value in Table2 is the right one?

Plus, if you can join the tables in a query, why do you need to duplicate the data in Table1?

Good questions.

Without getting into all the ugly detail, it's part of a very complex system I've created of having a database ported to a laptop, worked on offsite (in a location with no phone line let alone internet connection) while people are working in the local database and then the data that has been changed ported back into the local database and merged.

It gets really complicated because there are both master records and detail records (think of a client table and an invoice table and then an invoice detail table) which can be added or edited both by the remote user and local user. The way I handle this is that the remote system issues a temporary ID so it knows which record to link the invoices back to and then the temporary IDs are changed to the "real" id.

It works pretty well (as complex as it is) except I find that sometimes it will have trouble linking to the temporary ID as it sometimes confuses it with a record that was created locally with the same temporary ID.

Aren't you glad you asked?

SHADOW

Guus2005 · Jun 2, 2009

shadow9449 said:
...
It works pretty well (as complex as it is) except I find that sometimes it will have trouble linking to the temporary ID as it sometimes confuses it with a record that was created locally with the same temporary ID.
...

You could add a field called ChangeDate. To store the date and time a record was changed. You could sync the tables based on that field??

HTH

neileg · Jun 2, 2009

Why aren't you using replication? Access will do all this for you.

shadow9449 · Jun 2, 2009

Guus2005 said:
You could add a field called ChangeDate. To store the date and time a record was changed. You could sync the tables based on that field??

HTH

That's part of what I'm doing. That's how it knows what data to export. The problem is that the primary keys will conflict.

Let's say that the main database is up to client #100. When the database is copied to the laptop, the laptop user enters a client who will be numbered as 101. The local user will also enter a new client who gets numbered 101. So far, I don't care because the query that appends the laptop table to the local table doesn't append the primary key and the Autonumber will just assign 102 to the new client and everyone's happy.

Here's where it gets interesting: the offsite new client gets an invoice and has the transaction related to client 101. My challenge is to have that update itself when merged with client 102 rather than 101.

My solution is that there is a temporary client ID issued which is equivalent to the main ID. However, when the remote client gets merged back, she will retain a temporary ID of 101 and then the database can reassign the client number of her invoice based on a join of 101 and grab her "real" ID (i.e. 102) to update it.

The problem that prompted me to post my question is that there are actually TWO clients with temporary ID of 101 now, namely 101 and 102 (did I lose you yet?) Hence I was wondering how Access would know which of the two to grab.

Guus2005's answer is that it would do both sequentially. I.e. it would first assign 101 and then assign 102 as the query executes (if I am misinterpreting your answer, please correct me, Guus). This will actually result in exactly what I want to happen but it appears that it's not doing it reliably, hence my question as to what sequence it executes it.

My solution at this point is to have another field indicating whether the client originated from a remote computer or the local computer and another field in the invoice table indicating the same. That way a join based on the temporary ID will no longer be ambiguous.

SHADOW

shadow9449 · Jun 2, 2009

neileg said:
Why aren't you using replication? Access will do all this for you.

That thought has crossed my mind. To be honest, I've played with replication but I'm not sure if it's simple enough to allow users who are not computer-savvy to do themselves.

Maybe it's just me, I have a lot of trouble trusting Access to do many tasks if I don't have control over how it's doing them, especially something as complex as this. Sometimes using features that come with Access that are supposed to do one thing just don't perform as expected and you have to spend days and days looking over bug lists until you find the problem mentioned somewhere on the internet. I am more comfortable developing an algorithm that I understand and I can debug until it works.

SHADOW

dfenton · Jun 3, 2009

shadow9449 said:
TI've played with replication but I'm not sure if it's simple enough to allow users who are not computer-savvy to do themselves.

No, replication is *not* simple enough for many users to do it themselves. Many are able to do a direct synch via the Access UI if they know they have to open the back end. But it's also very simple to program a command button in the front end to do direct synch the back end:

Code:

Private Sub cmdSynchronize()
  Dim dbLocal As DAO.Database

  Set dbLocal = DBEngine.OpenDatabase("C:\Databases\MyLocalBackEnd.mdb"
  dbLocal.Synchronize. "\\Server\Databases\RemoteReplica.mdb"
  dbLocal.Close
  Set dbLocal = Nothing
End Sub

If you're not savvy enough to program a simple command button like that, then no, you oughtn't be using replication.

Update Query Where Join Results in Several Rows (1 Viewer)

shadow9449

Registered User.

Guus2005

AWF VIP

neileg

AWF VIP

shadow9449

Registered User.

shadow9449

Registered User.

Guus2005

AWF VIP

neileg

AWF VIP

shadow9449

Registered User.

shadow9449

Registered User.

dfenton

AWF VIP

Similar threads

Users who are viewing this thread