Append from grouped query only appends first record in each group

[DougieJ]

I have a query which groups records based on one field. When run as a selection query, it gives the expected output. However when I convert it to an append query, only the first record of each group is appended to the table. What causes this?

[JoeM]

Can you post the SQL code of your query?

One thing to check is for is key field violations, i.e. if some field must be unique and you are trying to add records that violate that condition, or if some field is required and you are not populating a value.

[DougieJ]

Thanks - silly me. Yes, it was an index I thought I had deleted.

[June7]

Why are you appending aggregate data? Saving calculated data, especially aggregate data, is usually a bad design.

[DougieJ]

Why are you appending aggregate data? Saving calculated data, especially aggregate data, is usually a bad design.

Because Access does not have stored procedures. The query interrogates several tables in order to fill certain fields in a report so that the user doesn't have to do it (and get it wrong). Women and fools criticise work they do not understand.

[JoeM]

Women and fools criticise work they do not understand.

I hope that wasn't a shot at June! She knows more about Access than you or I could ever hope to!

I think what she was referring to is that Reports can be based off of queries just as easily as they can tables, so it may not be necessary to save any data that can easily be calculated at run-time.

The general rule of thumb is that you usually do not need to store any data that can be calculated. Doing so may undermine the dynamic nature and integrity of your database (not to mention violate some rules of normalization). However, there are exceptions to the rule (like if you are trying to store historical or "point-in-time" data).