Query Read Only (1 Viewer)

[Oreynolds]

Hi,

I have a fairly basic query based on a single main table 'Quotations'. The query has a join to a subquery which contains 2 further subqueries which are 'Totals' queries which enable the sum cost of all products in one and labour hours in another which are then combined to give the 'Quote Total Value' for each individual quote record.

I need to display a list of all the quotes in a datasheet form which I have done and all is fine. However I want to be able to edit one of the fields ([Quotations].[CopyQuote]) that exists in the main 'Quotations' table but it won't let me. I understand that you cannot edit a 'totals' query but I am not trying to edit a field from the subquery so assumed that I would be able to do so?

As soon as I remove the subquery that contains the 'totals' sub-queries it allows editing. Is there any way round this as it wouldn't be possible to create the Quote total value without using a totals query? SQL below:

SELECT Quotations.QuoteNumber, Quotations.QuoteDate, Quotations.EmployeeID, Quotations.CustomerID, Quotations.SiteID, Quotations.QuoteName, IIf([QuoteType]=1,"Job New",IIf([QuoteType]=2,"Maintenance",IIf([QuoteType]=6,"Maintenance Renewal",IIf([QuoteType]=4,"Job Variation",IIf([QuoteType]=5,"Remedials"))))) AS QuoteTypeName, IIf([Discipline]=1,"FIRE",IIf([Discipline]=2,"EVC",IIf([Discipline]=3,"SUPPRESSION",IIf([Discipline]=4,"EXTING",IIf([Discipline]=5,"ELIGHTS",IIf([Discipline]=6,"N/CALL",IIf([Discipline]=7,"FRA",IIf([Discipline]=8,"INTRUDER",IIf([Discipline]=9,"ACCESS",IIf([Discipline]=10,"CCTV",IIf([Discipline]=11,"MONITORING",IIf([Discipline]=12,"REMEDIALS","UNKNOWN")))))))))))) AS DisciplineName, CCur(Nz([QuoteTotal],0)) AS QuoteTotalValue, Quotations.CopyQuote, Quotations.QuoteID
FROM Quotations LEFT JOIN qselQuoteTotal ON Quotations.QuoteNumber = qselQuoteTotal.QuoteNumber
WHERE (((Quotations.QuoteType) Is Not Null) AND ((Quotations.Discipline) Is Not Null));

 

[silentwolf]

Hi,

Have you got all the ID 's in the query?
In order to be able to update fields you need to make sure that there are all ID's available in the query.

HTH

 

[June7]

Use DSum() domain aggregate function instead of totals query. Don't do the totals calc in query, do it in textbox on form. Unfortunately, domain aggregate functions can slow performance of query and form.

Why not use form/subform arrangement?

 

[CJ_London]

doubt the OP has since he has a totals query. You could try not using a totals query and use a dsum function instead

your nested iif is a bit of a handful - really you should have these values in a separate table

alternatively use the choose function

choose([QuoteType],"Job New","Maintenance","","Job Variation","Remedials","Maintenance Renewal")

double double quote because you don't have a value for quotetype=3

 

[Oreynolds]

Hi,

Have you got all the ID 's in the query?
In order to be able to update fields you need to make sure that there are all ID's available in the query.

HTH

Thanks for this - Yes they are all in but still not updateable

 

[Oreynolds]

doubt the OP has since he has a totals query. You could try not using a totals query and use a dsum function instead

your nested iif is a bit of a handful - really you should have these values in a separate table

alternatively use the choose function

choose([QuoteType],"Job New","Maintenance","","Job Variation","Remedials","Maintenance Renewal")

double double quote because you don't have a value for quotetype=3

OK thanks, I'll try the DSUM and am sure it will work. I was trying to avoid this as I imagine it will run significantly slower as there are a few thousand quotes, all with multiple product sub records and multiple labour sub records to sum.......

 

[Pat Hartman]

Queries that contain aggregation or join to queries that aggregate data are NOT updateable. The only way to get a total and still set the query be updated is to use a domain function. However, domain functions run separate queries and so are very inefficient in queries with a large number of rows.

If you are binding the query to a form, use the domain function as the ControlSource of a control. That runs only for the visible record so doesn't slow things down.

 

[plog]

IIf([QuoteType]=1,"Job New",IIf([QuoteType]=2,"Maintenance",IIf([QuoteType]=6,"Maintenance Renewal",IIf([QuoteType]=4,"Job Variation",IIf([QuoteType]=5,"Remedials"))))) AS QuoteTypeName

Instead of a huge IIf statement you need either a QuoteType table to convert those numbers to text or you just need to store the text in the QuoteType field. Same goes for Discipline.

 

[Oreynolds]

Queries that contain aggregation or join to queries that aggregate data are NOT updateable. The only way to get a total and still set the query be updated is to use a domain function. However, domain functions run separate queries and so are very inefficient in queries with a large number of rows.

If you are binding the query to a form, use the domain function as the ControlSource of a control. That runs only for the visible record so doesn't slow things down.

OK thanks. That's a real shame as its very limiting. As you can see from the screenshot example below I need to present a meaningful list of quotes to the users so they can quickly and easily see all the quotes on the system, filter as necessary and then tick the check boxes on the end of all the quotes they wish to make duplicates for. The trouble is that its essential they see the quote totals as that really helps them knowing which quotes they want to select.

I tried using a DLOOKUP pointing back to the Totals query which worked but was painfully slow! Are we essentially saying that its effectively impossible to achieve this in access then?

 

[Oreynolds]

Instead of a huge IIf statement you need either a QuoteType table to convert those numbers to text or you just need to store the text in the QuoteType field. Same goes for Discipline.

Yer, I am just messing with the data at the mo but will store in tables once I've got it all sorted, thanks

 

[plog]

That doesn't really require an updateable data source. The copy field doesn't need to reside in a table. Make it an unbound control, then when they have selected all the records to copy have them click a button. That button then runs VBA that loops through every record on your form, seeing which have the copy box checked and copying them appropriately by using the corresponding ID field.

 

[CJ_London]

dlookup on a query will be slower than dsum on a table

there are a few thousand quotes, all with multiple product sub records and multiple labour sub records to sum.......

I need to present a meaningful list of quotes to the users so they can quickly and easily see all the quotes on the system, filter as necessary and then tick the check boxes on the end of all the quotes they wish to make duplicates for.

In my experience, presenting the user with a list of several thousand quotes for them to subsequently filter is the slowest way of doing things. That is an excel way of thinking.

Enable them to choose some parameters before seeing a list. Your screenshot appears to provide a filter on dates, perhaps include others for discipline/salesman/customer/quote type. BUT instead of applying a filter, use it as criteria

Also since the only update you appear to need is a tickbox, there are other ways of users making a selection - this is one

multi select form

Responding to a thread where I suggest using a multiselect listbox, I thought it would be useful to upload this simple example where you can turn a form into a multi select form. It only does a 'simple' version but with more work could probably also be extended to mimic the extended form. The...

www.access-programmers.co.uk

 

[Pat Hartman]

That's a real shame as its very limiting.

Think about it. When you aggregate data, you lose its unique ID. Without a unique ID, there is no way to update any specific record.

 

[CJ_London]

Make it an unbound control, then when they have selected all the records to copy have them click a button.

don't think that will work - if it is unbound and then clicked, all records will be selected (or deselected)

 

[Galaxiom]

As CJLondon says there is no point presenting thousands of records to the user. Start with this refinement first.

The most effective way to add a select checkbox to a form's records is by using an ADODB disconnected recordset. Join your data query to a table with a single boolean field and one record using a Cartesian join. The single field will be joined to every record.

The most important property is the CursorLocation which must be adUseClient and the LockType as adLockBatchOptimistic.

Set the form's Recordset Property to the ADODB recordset. Bind the form's Checkbox ControlSource to the boolean field. Clearing the recordset's Connection property will make the form's recordset updateable with each record being able to accept an independent value for the checkbox.

Processing the data requires a loop through the recordset updating or inserting another table on the basis of the Boolean field.

If you want to give this a go then read up about disconnected ADODB recordsets and post back.

 

[CJ_London]

Don't disagree with using a disconnected recordset so you can have a checkbox instead of highlighting selected records. The only thing to be aware of is if the user still needs to be able to filter and sort the more limited dataset, the standard form shortcut menu functionality won't work with ADO so you will either need to write your own shortcut menu functionality or have additional filter/sort fields in the header. Sounds complicated, but it isn't really