Solved Grouped records are uneditable (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 16:54
Joined
Oct 14, 2019
Messages
427
I have contacts that are joined to sales. If there is more than one sale, then I get multiple records for each contact. I use a query that asks for the MIN(SalesID) but then because of this grouping the contacts are not editable. How can I fix this? Or what's a better way to ask my question to find the answer which surely is very common.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:54
Joined
Oct 29, 2018
Messages
21,358
There's a reason why a group by query is read only, so you have to be careful what you're editing when you get it working. To remove the read only part, try using DLookup(). But again, you may find out you're not editing the correct record when you get it done. Good luck!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:54
Joined
Feb 19, 2002
Messages
42,981
What is the point of having the Min(SalesID) in the record you want to update? If this is the RecordSource for a form, I would make an unbound control that uses the DLookup() if the user wants to see this ID for some reason that I can't fathom.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:54
Joined
Feb 28, 2001
Messages
27,001
To answer the main question with a "why" type of answer, ...

You cannot edit a query that contains any SQL aggregations of any kind (with the possible exceptions of Min, Max, First, and Last) because aggregates reference more than one record. Editing through a SELECT query in datasheet mode therefore represents multiple underlying records on a single record of the aggregate. Access doesn't know WHICH of the underlying records to update, so won't update any of them.

To make your query editable, you have to not involve aggregates.

EDIT: Though to be honest, I thought that MIN and MAX records could be edited if they were single-row returns, and I see that you were using MIN in your aggregate. Was there anything ELSE in the aggregate like a SUM or COUNT?

Second edit: Look into sub-queries where you do the SQL aggregate in the subquery and use that record to select the main record to be edited.
 
Last edited:

ClaraBarton

Registered User.
Local time
Today, 16:54
Joined
Oct 14, 2019
Messages
427
When my contact list goes to detail, it has a page showing sales for this contact. How else would I tie sales records to the contact? I need the sales ID in the contact list so I can sort it by customer with sales and without.
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:54
Joined
Jan 23, 2006
Messages
15,364
I think we need some more info about your application and database structure to offer more focused advice.
 

ClaraBarton

Registered User.
Local time
Today, 16:54
Joined
Oct 14, 2019
Messages
427
I have a contact table with ContactID. That's the main purpose. I have a sales table with a SalesID Primary key that has a foreign key of ContactID. A detail form shows all contact info with a tabbed page showing sales for the contact. The database purpose is for sales calls so most contacts do not have sales and the list view toggles to show All Contacts, Contacts with sales, Contacts without sales. When a contact has more than one sale (multiple SaleID's) then the list shows multiple records with the same ContactID. So I put a Totals line in the query for the list to grab only the MIN(SalesID). But then it's uneditable.
 

ClaraBarton

Registered User.
Local time
Today, 16:54
Joined
Oct 14, 2019
Messages
427
I tried making a sub query to pull the minimum salesID and the query is still uneditable.
 

ClaraBarton

Registered User.
Local time
Today, 16:54
Joined
Oct 14, 2019
Messages
427
Code:
SELECT Contacts.ContactsID,
    Contacts.Company,
    Contacts.LastName,
    Contacts.FirstName,
    Contacts.EMail,
    Contacts.Business,
    Contacts.Home,
    Contacts.Mobile,
    Contacts.Fax,
    Contacts.Address,
    Contacts.City,
    Contacts.State,
    Contacts.ZIP,
    Contacts.Country,
    Contacts.Notes,
    Contacts.Created,
    Contacts.Modified,
    Contacts.FollowUp,
    Min(tblSales.SalesID) AS MinOfSalesID
FROM Contacts
LEFT JOIN tblSales ON (Contacts.ContactsID = tblSales.ContactsID)
AND (Contacts.ContactsID = tblSales.ContactsID)
GROUP BY Contacts.ContactsID,
    Contacts.Company,
    Contacts.LastName,
    Contacts.FirstName,
    Contacts.EMail,
    Contacts.Business,
    Contacts.Home,
    Contacts.Mobile,
    Contacts.Fax,
    Contacts.Address,
    Contacts.City,
    Contacts.State,
    Contacts.ZIP,
    Contacts.Country,
    Contacts.Notes,
    Contacts.Created,
    Contacts.Modified;
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:54
Joined
May 21, 2018
Messages
8,463
Untested, but maybe something like

Code:
SELECT Contacts.ContactsID,
    Contacts.Company,
    Contacts.LastName,
    Contacts.FirstName,
    Contacts.EMail,
    Contacts.Business,
    Contacts.Home,
    Contacts.Mobile,
    Contacts.Fax,
    Contacts.Address,
    Contacts.City,
    Contacts.State,
    Contacts.ZIP,
    Contacts.Country,
    Contacts.Notes,
    Contacts.Created,
    Contacts.Modified,
    Contacts.FollowUp,
    iif(dcount("*","tblSales","contactID = " & [contacts].[contactiD]) > 0,"Has Sales","No Sales") as HasSales
FROM Contacts
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:54
Joined
Oct 29, 2018
Messages
21,358
I like what @MajP suggested, but here's what I was thinking, if you "really" want to see the minimum SalesID.
SQL:
SELECT Contacts.ContactsID,
    Contacts.Company,
    Contacts.LastName,
    Contacts.FirstName,
    Contacts.EMail,
    Contacts.Business,
    Contacts.Home,
    Contacts.Mobile,
    Contacts.Fax,
    Contacts.Address,
    Contacts.City,
    Contacts.State,
    Contacts.ZIP,
    Contacts.Country,
    Contacts.Notes,
    Contacts.Created,
    Contacts.Modified,
    Contacts.FollowUp,
    DMin("SalesID", "tblSales", "ContactsID=" & [ContactsID]) AS MinOfSalesID
FROM Contacts
Hope that helps...
 

ClaraBarton

Registered User.
Local time
Today, 16:54
Joined
Oct 14, 2019
Messages
427
OH GREAT! It Works... so far. THANK YOU
Code:
HasSales: IIf(DCount("*","tblSales","contactsID = " & [contacts].[contactsID])>0,"Yes","")
 

ClaraBarton

Registered User.
Local time
Today, 16:54
Joined
Oct 14, 2019
Messages
427
I don't care about the ID, actually. I just wanted to know who had sales. Thank you so much for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:54
Joined
Oct 29, 2018
Messages
21,358
I don't care about the ID, actually. I just wanted to know who had sales. Thank you so much for your help.
Glad to hear you got it sorted out. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:54
Joined
Feb 19, 2002
Messages
42,981
I would still use a domain function on the form rather than in the query.
 

Users who are viewing this thread

Top Bottom