Update Query

[nick243]

Hi,

I have been trying to get this update query to work. I am looking for any ideas? This query updates my History pending table with recent shipped history. There is data in this shipped history by store item table copy, but every time I run the query it updates 0 rows in History Pending. Here is my SQL if that helps?

UPDATE [DM-History-Pending] INNER JOIN [DM-ShipHistoryByStoreItem] ON ([DM-History-Pending].Store = [DM-ShipHistoryByStoreItem].Store) AND ([DM-History-Pending].Items = [DM-ShipHistoryByStoreItem].Item)

SET [DM-History-Pending].[30-Shipped] = IIf([DM-ShipHistoryByStoreItem]![30]>0,[DM-ShipHistoryByStoreItem]![30],Null), [DM-History-Pending].[60-Shipped] = IIf([DM-ShipHistoryByStoreItem]![60]>0,[DM-ShipHistoryByStoreItem]![60],Null), [DM-History-Pending].[90-Shipped] = IIf([DM-ShipHistoryByStoreItem]![90]>0,[DM-ShipHistoryByStoreItem]![90],Null), [DM-History-Pending].[120-Shipped] = IIf([DM-ShipHistoryByStoreItem]![120]>0,[DM-ShipHistoryByStoreItem]![120],Null), [DM-History-Pending].[150-Shipped] = IIf([DM-ShipHistoryByStoreItem]![150]>0,[DM-ShipHistoryByStoreItem]![150],Null), [DM-History-Pending].[180-Shipped] = IIf([DM-ShipHistoryByStoreItem]![180]>0,[DM-ShipHistoryByStoreItem]![180],Null);

This code used to work but I may have modified it to where it doesn't now. Thank you for any help.

[nick243]

I am also dealing with a module that runs a combination of queries to get this history pending. I am now getting the Update Query to at least say its going to update rows but when it goes to update there is a failure when it runs and does not update any row. Just wondering what would normally cause this?

Thanks,
Nick

[RuralGuy]

Perhaps your answer is in this link: http://allenbrowne.com/ser-61.html

[John_G]

Your Update statement is updating a multi-table query; if this query is not updatable, the SQL update won't work. You might be able to make it updatable by putting indexes on [DM-ShipHistoryByStoreItem].Store and [DM-ShipHistoryByStoreItem].Item.

To check if the query is updatable, put this into query design view, and see if you can make changes when you run it:

SELECT [DM-History-Pending] INNER JOIN [DM-ShipHistoryByStoreItem] ON ([DM-History-Pending].Store = [DM-ShipHistoryByStoreItem].Store) AND ([DM-History-Pending].Items = [DM-ShipHistoryByStoreItem].Item)

[nick243]

Thanks, I am trying to fully understand the word index. I will try to make the change. I have a stand alone table also that works but I just can't get those numbers to update to the main table.

[nick243]

It looks like both store and item have Primary Keys. My process works by making a singular table and syncing it to Excel. I also changed that update query to a select query and it brought up a table with no values but had all the columns named and I could add info manually.

[nick243]

Your Update statement is updating a multi-table query; if this query is not updatable, the SQL update won't work. You might be able to make it updatable by putting indexes on [DM-ShipHistoryByStoreItem].Store and [DM-ShipHistoryByStoreItem].Item.

To check if the query is updatable, put this into query design view, and see if you can make changes when you run it:

SELECT [DM-History-Pending] INNER JOIN [DM-ShipHistoryByStoreItem] ON ([DM-History-Pending].Store = [DM-ShipHistoryByStoreItem].Store) AND ([DM-History-Pending].Items = [DM-ShipHistoryByStoreItem].Item)

I was able to put indexes on both [DM-ShipHistoryByStoreItem].Store and [DM-ShipHistoryByStoreItem].Item. I set it to allow duplicates. Is this what you mean by indexing? It did not work for me so far.

[nick243]

I have also noticed that the table DM-History-Pending that I am trying to update is blank in the records that I am trying to update. The default value is zero but when information is queried and appended to the DM-History-Pending the Columns I want updated in the next update query become blank. Is this a problem?

[John_G]

ShipHistoryByStoreItem].Item)I was able to put indexes on both [DM-ShipHistoryByStoreItem].Store and [DM-ShipHistoryByStoreItem].Item. I set it to allow duplicates. Is this what you mean by indexing?

Yes, that is what I meant. Since that didn't work, it means there is something else causing the problem - see below.

...the Columns I want updated in the next update query become blank. Is this a problem?

No, not necessarily. But it does suggest that the update query is putting Null values where it shouldn't.

Try this -
For the columns in DM-History-Pending where you have set the default to zero, set Required = Yes and Allow Zero Length = No.

You will have to use an update query to change any Nulls to 0 first (Numeric fields cannot contain zero-length strings anyway) , otherwise Access won't let you make those changes.


If you are running the update query in VBA, add the Option dbFailOnError to the statement that executes them:
currentDb.Execute "Update.....", dbFailOnError

This will generate an error message if the query fails. If you are running the query from the Query Design screen, the changes I suggested above should cause an err message if it tries to update those fields to Null.

[nick243]

Thank You for replying. I will give it a shot.

[nick243]

OK, I was just looking and it seems to me that I have a number field instead of a text field could this be the issue? I did not see the allow zero length so I messed around with the data type and changed it to text? I changed the required to yes and the data violates the new rule.

[nick243]

I have also tried to append the zeros to the columns before I ran the update and it would still not update and the columns I put zeros in went blank.

[John_G]

I have a number field instead of a text field could this be the issue?

No, not directly. Leave it it as a number field if that is the type of data it contains. You won't see an Allow Zero Length property for numeric fields because it doesn't apply.

I changed the required to yes and the data violates the new rule.

Yes, it probably does. You have to replace the Nulls with zero's before you change the Required property to Yes.

I have also tried to append the zeros to the columns before I ran the update and it would still not update

Sounds like you might be trying to do that in the query. Instead, do it directly in the table so that you don't have to worry about problems related to linked tables.

PS - do you have any relationships defined in the database (Database Tools - Relationships)?

[nick243]

Yes, I do have relationships set up between DM-ShipHistoryByStoreItem and DM-History-Pending the "Store" field and the "Items" fields are related.

[nick243]

Sorry about the picture kind of hard to tell my relations ships. I can try to crop it out.