Updatable?

[Perfac]

There is a query q04InvPurchaseSub03. On the image 001 it can be seen it is updatable. This query is a subquery to query q02InvPurchases that stores the subform for Purchase invoices. Since I assume all TOTAL QUERIES are not updatable, q04InvPurchaseSub03 is not a TOTAL QUERY but still returns only the latest record against every Inventory Item or any other item bought that is not Inventory. This demo we are working on, the policy is for a retail business to have a mark up on latest purchase price to get to the sales price.
I have a table t01CombindedEntity where all 10 entities(i.e. Banks, Employees, Suppliers, Customers, Inventory, Goods and services etc.) come together. We created a subquery q04CombinedEntity09 which lists all 277 records in t01CombinedEntity. This 277 include 29 Inventory Items and 73 Goods and Services Items. It all works perfect to join q04CombinedEntity09 and q04InvPurchaseSub03. The 29 +73 items gives 102 items that should have a latest purchase price and they lie in separate tables but come together in t01CombinedEntity. It can be seen in Image 002 that q04CombinedEntity09 is also updatable.
I need to join q04CombinedEntity09 with q02InvSalesSub to get the sales Price on the sales invoice. Joining these queries in q02InvSalesSub makes q02InvSalesSub not updatable. I don’t see right now why if the two queries are seperately updateable turn to not updatable when joined.
To return the salesprice by expression makes the system hang.

[June7]

Why does it matter if they are not updatable?

Certainly two updatable queries used in another query might then not be updatable. What is the SQL statement?

"I need to join q04CombinedEntity09 with q02InvSalesSub" seems to conflict with "Joining these queries in q02InvSalesSub"

What is the salesprice expression?

[Perfac]

[QUOTE=June7;398318]Why does it matter if they are not updatable?

Hello June7. I hope to learn something here. When I do the join,q02InvSalesSub is not updatable and I cannot add SALES INVOICES. You said Certainly two updatable queries used in another query might then not be updatable. Can you tell me why?

When I enter a PURCHASE INVOICE the unitprice field is open and I enter it. This demo of ours have a field then that do a salesmarkup lets say 30% which brings me to SalesPrice01, then there is an open field for management adjustment, the adj may be negative as well. It brings us to SalesPrice02. We need to connect this Salesprice02 in the Invoice table against the Item code. Expressions doesn't work, we have to get it done through joining.

SQL for q04CombinedEntity09
SELECT t01CombinedEntity.CmbEntID, IIf([UnitPrice05]>0,[UnitPrice05],0) AS LtstPchPrc01, t01CombinedEntity.EntityName01 AS InventoryName01, t01CombinedEntity.BusPol_ID06, DLookUp("Percentage02","q04BusinessPolicy02","BusP olItem01=" & """SalesMarkUpOnCost""") AS SalesMarkUpOnCost01, Format([SalesMarkUpOnCost01],"0.0%") AS SalesMarkUpOnCost02, IIf([LtstPchPrc01]=0,0,Format([UnitPrice05]*(1+[SalesMarkupOnCost01]),"Currency")) AS SalesPrice01, t01CombinedEntity.MnmAdjSlsPrc01, Format([SalesPrice01]*(1+[MnmAdjSlsPrc01]),"Currency") AS SalesPrice02, t01CombinedEntity.Logo01, t01CombinedEntity.Photos01, t01CombinedEntity.Documents01, t01CombinedEntity.MinQtyLvl01, t01CombinedEntity.Unt_ID02, t01CombinedEntity.EntTpe_ID01, t01CombinedEntity.LdgAcc_ID01, t01CombinedEntity.Check016, t01CombinedEntity.VatClassification01, t01CombinedEntity.Check010, t01CombinedEntity.Check011, IIf([VatClassification01]=1,"PS",IIf([VatClassification01]=2,"PC",IIf([VatClassification01]=3,"PZ",IIf([VatClassification01]=4,"PE","NV")))) AS VatCodePurchases01, IIf([VatClassification01]=1,"SS",IIf([VatClassification01]=2,"SC",IIf([VatClassification01]=3,"SZ",IIf([VatClassification01]=4,"SE","NV")))) AS VatCodeSales01
FROM q04InvPurchaseSub03 RIGHT JOIN t01CombinedEntity ON q04InvPurchaseSub03.CmbEnt_ID04 = t01CombinedEntity.CmbEntID;

[isladogs]

You said Certainly two updatable queries used in another query might then not be updatable. Can you tell me why?

See http://allenbrowne.com/ser-61.html

[Perfac]

Thank you for the reference. I counted 14 lines of advice, and marked them one by one whether the point is not applicable to my queries. So far the last line mentions "The fields that the query outputs are Calculated fields" seems to be the only point I can give attention to. I plan to learn today whether MAKETABLE can solve my challenge. I know how to use APPEND QUERY to create a table with no calculated fields, but I don't like all the messages my users must answer on Append queries.

On opening form f02InvSales with Subform f02InvSalesSub I need to find the VBA to MAKETABLE of q04CombinedEntity.

[isladogs]

Make table queries are generally best avoided as these cause database bloat

I haven't studied this thread in detail but I don't understand this line

I don't like all the messages my users must answer on Append queries.

What questions? There is no reason why any answers to messages should be necessary if correctly designed

If you mean action query confirmation messages like "Do you want to append 35 rows?" then use

strSQL = ....
CurrentDB.Execute strSQL , dbFailOnError

instead of

strSQL = ....
DoCmd.RunSQL strSQL

[Perfac]

Our Salary and wages Process works as follow.
a. A query q03SalarySummary filter and set up all employees that should get salaries.
b. This query has many calculations i.e. PAYE(Tax), Pension fund, Medical aid, UIF, Other deductions and Workmens Compensation(Even though the employer pays that).
c. Lets say we work with a sizable business with many employees. We don't want all the names to show in the ledger. Just a summarized Salary Journal. That Journal with us is made up by 4 append queries, which we activate clicking one command button.
d. So yes the "query Information messages" demand 12 clicks of the mouse. That will surely annoy my accounting customers, so we don't allow those messages. Thank you for the advice.

Staying with this thread. I don't understand yet why I use the same query q04CombinedEntity09 joining another query q02Inventory and it works, but it is not working joining q02InvSalesSub? I posted threads some time ago and do a lot of effort to solve issues with expressions or joining tables. We get more experienced, and believe Access is great and there are solutions. If expression and joining is not the way, then what is? I feel strongly the Append way will work and the MAKETABLE way a little better. Right now I am searching for tutorials that will teach me the command to MAKETABLE on opening the invoice form. I also wondered if an Update query is the way to go.

Since I am short of experience, Can you tell me. Once a MAKETABLE query is created, does it keep the new table updated or is my assumption correct thinking that the VBA on a form needs to activate the MAKETABLE query each time I post a transaction?

[Perfac]

I opened a new Access database and copied only the needed objects. I tried to attach it here but failed. I load it to DRIVE. The link is https://drive.google.com/open?id=1OmmmhyYO9ojnf2-hnmlJ2I470pm-Ho42. The only file there.

I just learned that "Calculated columns are not allowed in Select Into statements". MAKETABLE is not my solution. Append queries or update therefore should be.

[isladogs]

I hope you don't mind me saying so but your posts tend to cram in lots of ideas into a small space & it can be difficult to identify the main issue you are asking about.

For example:

I feel strongly the Append way will work and the MAKETABLE way a little better. Right now I am searching for tutorials that will teach me the command to MAKETABLE on opening the invoice form. I also wondered if an Update query is the way to go.

I just learned that "Calculated columns are not allowed in Select Into statements". MAKETABLE is not my solution. Append queries or update therefore should be.

You have fitted 3 types of action query into each of these quotes. They aren't interchangeable as they do different things:
a) UPDATE queries are used to update the values of 1 or more fields in EXISTING records
b) APPEND queries are used to add 1 or more newl records to a table
c) MAKE TABLE queries are used to a create new table .... or overwrite an existing table replacing all records
These should be avoided as far as possible as they cause databases to increase in size and doing so repeatedly will eventually lead to instability & crashes
I certainly would NOT use a make table query every time you open a form for those reasons.

So whatever you are trying to do (remember I haven't studied the whole thread), APPEND and/or UPDATE queries are better - possibly with DELETE queries as well if needed to delete unwanted records

If you problem is that you can't do something because a query is NOT updateable, then identify the reason(s) & modify the query.
For example if its not updateable due to calculated values, you might be able to use a SELECT query based on the results of that query instead.
The SELECT query may well be editable

Just so you know:

I load it to DRIVE. The link is https://drive.google.com/open?id=1OmmmhyYO9ojnf2-hnmlJ2I470pm-Ho42. The only file there.

1. In common with many forum users, I don't open files on external sites
2. I won't have time to look at it today anyway even if uploaded here

[Gicu]

Perfac,

You can make your subform and query updatable by changing the recordsettype to dynaset(inconsistent updates) in both the query and the subform. Please review very closely your data if you decide to use this approach as sometimes can cause data inconsistency problems.

I recommend you review some of the links below if you decide to use it:

https://www.pcreview.co.uk/threads/w...-mean.2133608/
https://access-programmers.co.uk/for...d.php?t=188286
https://www.reddit.com/r/MSAccess/co...stent_updates/

Cheers,
Vlad

[Perfac]

In the months since starting to learn MS Access I realized quickly that it is very normal to make my best decision for that day, not to mean it is not best Access can do. I try my best to be clear and to the point whenever placing a thread. I'll try once more in one short sentence.

It is not a hardware issue and there are very little information in my database, but some expressions make the system hang. My salary process have many expressions in the query and some are quite complicated, but those expressions and the salary query works well. I don't know why. In the query about this thread there are not more than two expressions but it hangs. As I tried to tell. The same query q04CombinedEntity09 is joined with query q01Inventory and it works perfect and immediate (With the calculated fields). Joining q04CombinedEntity09 with q02InvSalesSub makes it hang.

I have studied your website and hoped that some expert can help me. This joins must be very common. I did the verification on the reference you gave me. I assume that there are options which I miss, and today I will test the append query way. I try this exact process the first time, therefore I am learning. I wonder if the final best thing will be if the append query append to a table the info from q04CombinedEntity09. I assume now that it will work well. It does seem to me that whenever my Sales Invoice form closes it will have to first delete all records in this new table, and then append all the data from q04CombinedEntity09 there again.

I am passionately hoping and trying to learn the best way. I have little doubt that a new table with no calculated fields will give the successful result. I wish not to go that way, but how then? Bloating is one reason. My inventory query and another query works well to get the latest purchase price and sales price from the same query. I dont find the reason yet why the SalesInvoice have a problem. I will study Select query and watch a tutorial or two, for right now I lack experience to know how.
Thank you.

[Gicu]

You do that in the properties box of the query (and subform) in design view: