Non-Updatable Query Issue

[rich.barry]

A 2002 post from Pat says this:

Queries are NEVER updatable if:
1. they contain aggregate functions such as Sum()
2. they are union queries
3. they contain a cross join
4. they are a cross tab
5. they contain a group by or distinct clause.

The reason that those query types are never updatable is because there is no way to identify an individual record. Ie, Jet can't take a row from the resulting recordset and identify its specific source row in a table because each row in the recordset is an amalgm of multipls source rows.

I have a problem with a non-updatable query, which the root cause appears to be item 1, but the way I have set it up, there (as far as I can see) is no barrier to it being an updatable query.

What I have is a parent schedule table with a key of RunID.
A child table of this is my inventory table, each pack produced belonging to a specific RunID and having a production date.

When modifying the schedule, the query needs to show RunID's which have the most recent pack produced up to X days ago.

First Query is an aggregate query which groups by RunID and has Max in the production date field, plus a Where column to get those in the correct date range. Result is a list containing all the RunID's I'm interested in.

Second query is a join between the first query and the schedule table and gives the schedule information where the RunID in the 2 tables are equal.
This should be updatable, as nowhere is it ambiguous as to what RunID I want to update.

Unfortunately it is not updateable. Does anyone know why?

Thanks for the help

Richard

 

[FoFa]

Even if the queries are based on another query that contains an agregate, Access considers them nonupdateable.
I am not sure about Access, but other DB enginies typically combine all the queries together then run them as one big query (that is the best way to think about it). I don't know if access does this (it does not appear to do so) but it sure doesn't like agregates anywhere in the mix when it comes to updates. Sometimes you can get around it by using it as a subquery in the criteria, some times not. I am not a fan of this either specially when the agregate query is just used for selection, not update. But Access can not seem to tell the differance. You just have to come up with alternate ways to do it has been my experience.

 

[Pat Hartman]

I'm not sure if Jet combines all the queries either. Separating the queries does have some impact on how Jet evaluates joins though and you can improve performance by forcing Jet to think along your lines when joining to foreign tables. I also think that Jet should be smart enough to let you update an unambiguous recordset but it doesn't. Maybe if Microsoft hadn't stopped development of it we might see that feature in a future version. But alas, it is not to be.

The usual solution is turning your aggregate query into a make table query. Then the update query can join to the table. If this is a process that you need to automate, you'll need to delete the temp table before you run the make table if it exists. You could do it afterwards but there is always the chance that for whatever reason, it does not get deleted. Then the next time the make table query runs, it will fail.

 

[rich.barry]

Thanks FoFa & Pat

The make table query is the way I had made it work, it was just annoying that I had to do a work around.
Just a quick question on the best location for the temporary table in a FE/BE database. I have currently put it on the FE, as everyone has their own personal copy, so they won't be trying to delete and remake the table while someone else is querying it.
Is the locking smart enough that I could put it on the back end? If 2 users simultaneously try to delete and remake the table, what is likely to happen?

Regards

Richard

 

[Pat Hartman]

Leave the table in the front end. It is quite possible that if it is in the back end some one could delete a table that someone else just made before they even get a chance to use it.

 

[FoFa]

I would go with Pat on this one. How ever I typically make this a work table (just my name), and use a delete/append query instead of MakeTable (it's a control thing, I like to know what access is doing, not guess or let access just handle it). We have put the table on the BE, but you need a unique key to distinguish each users data from the other. I like the FE assigment better as it turns out to be less "dirty" in the long run.