Update query: calculated field to other field with SharePoint Lists (1 Viewer)

[1var]

Good morning,

I am a beginner in Access and I've built a database for the indexation of commercial prices. In this database I use an update query in which a calculated field ‘calc’ is updated to another field ‘tarief’. This calculated field ‘calc’ is a multiplication of the field ‘tarief’ with another field. The query below works perfectly when I use a local table. However, when I use a linked table in SharePoint, the update query does not always work and the ‘tarief’ remains the same.

strSql_Update = "UPDATE [TB_Tarieven] INNER JOIN QR_Tarieven_Indexatie ON ([TB_Tarieven].OmschrijvingID = QR_Tarieven_Indexatie.OmschrijvingID)" AND ([TB_Tarieven].AccountID = QR_Tarieven_Indexatie.AccountID) SET [TB_Tarieven].[Tarief] = [QR_Tarieven_Indexatie].Calc WHERE ((([TB_Tarieven].AccountID)=" & Me.Klant_Combo & ") And (([TB_Tarieven].PeriodeId)=" & Me.From_Periode_Combo & "));"

I've already unchecked the 'Caching Web Service and SharePoint tables' box, but this didn't work.

Could someone please help me?

Thank you!
Ivar

 

[namliam]

We do not store calculated values in tables, calculated values are calculated at run time in a query.

This is were your solution is, DONT

Groeten uit Amsterdam

 

[1var]

Thanks.

The commercial prices need to be mutiplied with a certain percentage every year. I use an append query to create new records for the new year, where all the product information is duplicated and the price 'tarief' is multiplied with a certain percentage. This works perfectly.

I want the user to be able to correct the price 'tarief' with a certain percentage, if he or she made a mistake. This is the idea of the update query in my first post.

How would you design something like this?

Groeten uit Groningen!

 

[namliam]

It works or you, if its perfect or not is another question....

Simplest solution is to make a query which does the tarief * percent calculation for you.

I fear a design issue though, I am smelling a product that has different prices depending on year.
If so, you dont really want a "product" record per year / price , instead you want one product and a related price table containing the prices per year or per period - i.e. prices could change on a week basis depending on resource cost, or seasonal influences-.

 

[1var]

Haha yes. The append query works for me. I am not sure if I understand you correctly.

I think this is a price table. In this table you select a product 'omschrijving', the customer and the 'period' from other tables. So you only have a new record for an unique combination of these three fields including a different price. The prices are only indexed by a percentage every X period. There are no other factors influencing the price. The period is more like a category. For instance, Customer A has a period of 3 months in which the prices are fixed and Customer B has a period of 12 months.

I do not think I am storing a calculated value in a table. I am only storing the result of the calculation in the table. The 'QR_Tarieven_Indexatie].Calc' is a query that does the calculation for me. The update query in my first posts only copies the outcome of this calculated field to another field.

 

[namliam]

Hmz, I see.

What are your primary keys? For update statements to work you need to have a proper PK and use it in the query for it to work.
Do you get an error? You seem to be expecting certain records to change but they dont?

I dont know Sharepoint and its interactivity enough to really help here, but start at the starting point... if you run a select statement are your records selected?

 

[1var]

If I open the Frontend, the first time I run the query it seems to work. However, if I want to run the query again the records do not change. Closing and re-opening the Frontend sometimes work. I also get a lock violation error. But this only seems to be the record that is currently selected by me, or the record that another user is working on.

If I download the table from SharePoint and use it locally, the query works as expected.

How could I run a select statement and check if the records are selected?

Edit: Every record in the 'price list' has an unique PK. The period, account and 'omschrijving' tables also have unique PK's.

 

[namliam]

make a query like your update without the update:
Select * from [TB_Tarieven] INNER JOIN QR_Tarieven_Indexatie ON ([TB_Tarieven].OmschrijvingID = QR_Tarieven_Indexatie.OmschrijvingID)" AND ([TB_Tarieven].AccountID = QR_Tarieven_Indexatie.AccountID)
WHERE ((([TB_Tarieven].AccountID)=" & Me.Klant_Combo & ") And (([TB_Tarieven].PeriodeId)=" & Me.From_Periode_Combo & "));"

Which you can run manually from the DB while your form is open, just replace Me. by forms("Formname").