How to make complex queries editable?

[Thomasso]

Hello,



I have created queries that are based on for example 6 tables (Companies, Orders, Products, OrderDetails, etc...). I have found out that I cannot edit the records in this query which is a big problem for me, since I am creating forms based on this query.

I am also using TOTALS (GROUP BY etc.).

How do I make this kind of query editable? Sorry for foreign language, I tried to translate the core so you can understand the query.



It is a query called: Supplier list (Orders and OrderDetails are there because I am counting turn over for each supplier).

Thank you
Tomas

[pbaldy]

You can't; the totals query alone will make it read only, even on one table. Other thoughts and reasons:

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

[Thomasso]

Ah, ok, thanks for the link. What would you recommend to do when I need to input new orders through a form?

I have these tables: Customers, Orders, OrderDetails, Products.

I need to select a customer from the Customers table, add a new record into Orders table, then insert units to OrderDetails table and choose product types. How do I make this form work?

I need totals functionality for the total price etc. What do you recommend about this? A separate query?

Thank you,
Tomas

[pbaldy]

For customers and such, I'd use a combo box that got its selections from the customer table, but was bound to the customer field in the orders table. Normally orders and order details would be displayed with a form/subform, so the main form would only be bound to the orders table, the subform to the details table.

[Thomasso]

Ok, I followed your advice and I think it is gonna work.

I have created many queries and I am starting to get lost in them. In your personal experience, when is it better to create a query and when is it better to stick to tables?

All data inputs should be connected to tables and read only data to queries? How do I choose?

Thank you,
Tomas

[pbaldy]

There are those that would advocate always using a query behind a form. I don't see a problem binding a form directly to the table...but...I always either open the form with a wherecondition or in data entry mode. As a rule you don't want to open a form bound to a table/query that includes all records, or you'll have performance problems when that table gets larger.

[Homegrownandy]

Just as a note. you can edit the SQL for the forms even if its not linked to a query. Basically you do make a query for that form but its not displayed in the left bar with the other query's.

If that makes sense. I have a bit of OCD keeping my everything to a minimum. haha

[Thomasso]

Thanks for the tip. However, I decided to do the following:

I will build queries for all the read-only forms, like overviews, navigation screens, etc. And all the data entry forms (add new customer, edit order etc.) will be connected to tables. That is a fine approach, isn't it?

Cheers,
Tomas

[Homegrownandy]

yea that wont be an issue unless you want to add data to more than one table.... but even then you just click on form in design view and edit "record source" in the data properties tab. Then its the same as any query.

[Micron]

I would not be above temporarily storing Totals query values in a temp table so to allow other updates through a query based on multiple. Note, I'm not saying I would then store those calculations anywhere as part of the query update. That being the case, why are aggregate functions (totals or otherwise) part of a query that is being used to update if not to store those values? Most of us here do not and would not store calculations 99.98% of the time.

Looks to me that you are using lookup fields in your tables too (but I might just be looking at the results of a defined relationship when I see the minus signs). I wonder if the totals query was removed would the update fail anyway? Have to say I don't know because lookup fields in a table are another questionable practice that I'd try to stay away from.