Solved Grouped records are uneditable

[ClaraBarton]

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]

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]

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]

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.

 

[ClaraBarton]

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]

I think we need some more info about your application and database structure to offer more focused advice.

 

[ClaraBarton]

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]

I tried making a sub query to pull the minimum salesID and the query is still uneditable.

 

[theDBguy]

I tried making a sub query to pull the minimum salesID and the query is still uneditable.

Did you try using DLookup(), as I suggested earlier?

 

[ClaraBarton]

Where or how would I use it, please?

 

[theDBguy]

Where or how would I use it, please?

Can you post the SQL statement for your query?

 

[ClaraBarton]

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]

Untested, but maybe something like

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 like what @MajP suggested, but here's what I was thinking, if you "really" want to see the minimum SalesID.

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]

OH GREAT! It Works... so far. THANK YOU

HasSales: IIf(DCount("*","tblSales","contactsID = " & [contacts].[contactsID])>0,"Yes","")

 

[ClaraBarton]

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 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]

I would still use a domain function on the form rather than in the query.