Updating records from aggregated query

[Craig0201]

I have a query running to find the first blank record in a table (I know, never have blank records in a table but it wasn't me that programmed this thing I promise! I just get left the mess to clear up.) Once I have the minimum value, I have to update that record with some values off a form. Sounds simple to me and was until to found that you can't update values off aggregated queries!! Whats the way round this?

Thanks

Craig

 

[MichaelRed]

There is none. At least not directly.

You need to figure out why the blanks exist in the aggregate (e.g. trace the generation of the blank record back to the recordset which causes it to be generated)) and correct the source problem.

If the aggregate is built from several sources, this can become really difficult. If it is only a couple of sources, there it is generally fairly easy.

While I would agree that you shoulnd't have blank records in table type recordsets, queries from multiple sources which do not include All of hte fields from ALL of the record sources will often include blank 'records'. this makes a good starting point for tracing the blanke in your agggregate - if the source for the aggregate includes such arrangement(s). Make "parallel" queries which include ALL Fields from ALL sources. Check the records where the fields used in the aggregate are blank. With ALL the fields present it should be easier to see where the blanks in the aggregate are generated.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Craig0201]

Thanks Michael.

The blank records are actually deliberate because there is a finite set of records that can exist (Max 100000) due to a system limitation (only holds 5 digits fields). Getting rid of them isn't an option.......well, don't think it is anyway!

OK, what about this then......

Is there a way that I can create an updatable recordset from a query with minimums in it?

Craig

 

[MichaelRed]

Well, two answers:

You CAN suppress the Blank records in the aggregate, but you still need to find out where/how they get into the aggregate and then set conditions (WHERE clause(s)) to remove them.

Aggregate queries are NEVER updateable. The best you can do is use them to get the recordIDs for what you want to update and then do a straight select on those records.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Craig0201]

Eek!! Nasty solution in the end! Had to create a hidden table holding the line number of the first available record, use that as the basis of the update query and then run a make table query to find the next blank record. Not nice and def not elegant!

Craig

Thanks for all your help, Michael