Malfunctioning aggregate queries

[topher23]

I was working in the development copy of one of my databases, and as I was saving it, Access encountered one of it's much-beloved errors. No choice - I restarted and "recovered" the database. Everything looked fine, so I finished the work I was doing and pushed the update out to the server. I guess that was a mistake.

Suddenly, all of my aggregate SQL functions are acting screwy. Putting criteria on a HAVING statement (by putting the criteria directly on a GROUP BY expression in query builder) isn't working, so I had to take all of my criteria and put them into their own WHERE statement. That took a lot of time, but wasn't a big deal. Now, unfortunately, there's another issue. I have several aggregate queries with Expressions in them, properly designated as such in query builder, and these queries are now erroring out with "doesn't include the specified expression '[insert expression here]' as part of an aggregate function."

As a further bit of wierdness, the queries still malfunction if I export them to another database, but if I re-create them in a 'clean' database they work fine.

Anyone have any insight on this issue? Am I just S.O.L. and need to start over with an older revision?

[TheSmileyCoder]

I had a similar experience once, after a total network breakdown (Which corrupted 30% of the front-ends.....)

Some of the distributed front-ends could suddenly not link properly to their designated table. If you tried to open the linked table from the frontend it looked like garbage. Removing teh link, and then adding it back worked. In the end I just "faked" a new update, so all the client frontends downloaded a new frontend (which was basicly just the same non-corrupted frontend).

[topher23]

I don't rightly see how that applies in this case. Although I do have to admit that I have had the exact problem as in your anecdote and used the same solution, in this case all of the client front-ends are now the corrupted version. As I inferred, I do keep backups of all of the revisions as a hedge against catastrophic screwups - I'm just hoping this one isn't catastrophic enough that I have to go back and revise the older backup.

[NeoPa]

I've seen situations where Access forgets that a set of SQL is no longer aggregated.

I generally take the SQL out and put it in a text editor to fix. Pasting that into a new QueryDef generally resets anything Access has fooked up for you.

I hope that's of some help.