Re: Query based on other queries problem

Some comments:

First and Last don't necessarily return the oldest or newest record added to the database or the earliest or latest value.

Not enough details to really propose a solution, but you might be able to use sub-queries in a where clause to solve your problem of the query not being updatable. You can use a group by query in the where clause and that will not affect the update capability of the query itself.


Assumptions:
EventClients table contains a list of clients for an event (0 to many)
Events table contains records with data on an event

The following query with a correlated subquery should be updatable (assuming that it is without the where clause) and should display one clientId for each event.

SELECT *
FROM Events Inner Join EventClients
On Events.EventID = EventClients.EventID
WHERE EventClients.ClientID =
(SELECT Max(ClientID) FROM EventClients as C
WHERE C.EventID = EventClients.EventID)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Gridrunner wrote:
I am trying to find a work-around for the read-only problem when a query has a Group By clause in it. I am working in Access 2007. Here is a summary of what I am doing:

I receive orders from my clients that contain personal data about people. Often a given order is for multiple people. I have a form that displays summary data for a specific order, and only lists the name of the first person on the order. I also have a table that keeps track of various "milestone" events for each order, such as when the order was received, when it was completed, etc. Each of these events sets the order status to a particular value, and my summary table displays only the current status. The query I use is based on a number of preliminary queries in order to assure that I only receive the data I want and not a line for each combination of name and event for an order. To achieve this, two of my precursor queries use Group By functions (First() for the name, and Last() for the event). I then use this information to pull together my sumary data. The query directly behind the form does not use any Group By functions itself, but this does not prevent the read-only problem.

I think I have a solution to this this would use VB. I am proficient in VB, so this would not present a problem. However, the solution seems very klunky and inefficient. Does anyone have a better solution?
.

Relevant Pages

  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)
  • RE: Xlocking with a select statement
    ... named query expression, order clause, update clause, lock option ... A result table or the underlying base tables are updateable if the query ... A lock can be requested for the ...
    (microsoft.public.sqlserver.programming)
  • Re: update query: still having problems
    ... "Michel Walsh" wrote: ... From the User Interface, in the toolbar, or the menu, when you edit a query, ... If this is what you want, fine, else, add a WHERE clause to limit ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)
  • Re: update query: still having problems
    ... "Michel Walsh" wrote: ... you should find a button that allows you to change the query "type". ... If this is what you want, fine, else, add a WHERE clause to ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)
  • Re: Query based on other queries problem
    ... I receive my orders from the clients, ... I have tried something very similar to your proposed solution, and the query ... inside the WHERE clause. ... Create a query that retrieves the first Customer for each order, ...
    (microsoft.public.access.queries)