Query based on other queries problem

[Gridrunner]

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?

 

[John Spencer]

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]

Here is some more detail. Hope this helps.

First, I was using "First" and "Last" along with sorting the ID numbers in
ascending order, to ensure I had the correct record from each table. Now that
I look at it, using Min and Max would be more efficient since I wouldn't have
to sort.

Second, I am using three tables for the data:

Events
Customers
Orders

Events and Customers both have an OrderID field to link them to the specific
record in Orders that they belong to. To clarify, I have another table for
Clients. I receive my orders from the clients, and those orders contain
information on their customers that I need to do my job. What I am trying to
do in my summary is display only the name of the first Customer on the order,
and the order's most recent status.

I have tried something very similar to your proposed solution, and the query
came up as read-only. I have looked through all the information I can find on
the subject, and the only reason I can find is that I had a GROUP BY clause
inside the WHERE clause.

My most recent attempt was to do the following:
1. Create a query that retrieves the first Customer for each order, using
Min(CustomerID)
2. Create a separate query that uses the CustomerIDs retrieved in 1 to look
at the table again and retrieve the data I want for each result.
3. Create a query that retrieves the last Event for each order, using
Max(EventID)
4. Create a separate query that uses the EventIDs retrieved in 3 to get the
data I want.
5. Finally, create another query that combines the results of 2 and 4 above.

Okay, I know this same thing can be done using subqueries, and I did that.
But that still produced a read-only query. So I broke it up as explained to
avoid any Group By clause appearing in the SQL for the final table.
Apparently that didn't trick Access into letting me update the records.

The only solution I can think of at this point is to use VB to take the
results from 1 and 3 above and create the tables for 2 and 4 respectively. In
this way, 2 and 4 are completely disconnected from the Min() and Max()
functions. However, I am unsure whether I can achieve the desired result even
in this manner. Any ideas?

Thanks!