Prevent Group By eliminating duplicates

[bruegel]

I have a very simple query which takes the item name from one table and the amount received from another. Tables linked by Item Name. (Latter table imported hence item name field). I want to calculate the total received. As soon as click view totals the Group By eliminates duplicates. I wish to prevent this. If I select Output all fields then get error "cannot group on fields selected with *". I'm not using any wild cards to filter.



Queries are used for more than one purpose so would prefer to limit number littering the db. Is there another way of preventing Group By eliminating duplicates please?

Many thanks

Pieter

[JoeM]

Queries are used for more than one purpose so would prefer to limit number littering the db. Is there another way of preventing Group By eliminating duplicates please?

Why exactly are you clicking this if you do not want to combine like entries to get a total?
Are you trying to maintain each individual record, while showing a grand total for each item?

If so, do this in a series of two queries.
First, create your aggregate query where you only have Item Name and Amount, and get your total of Amount (by using Group By to get rid of the duplicates).
Then, link this query back to your original table, and you can "tack" on the Totals field to the end of each record, returning whatever fields you want.

If you really want to do this in a single query, you can just nest the queries together. Just switch your first query to SQL View, and Copy and Paste the code out to somewhere (i.e. NotePad). Then open your second query, and in SQL View, replace the reference to your first Query in the join with the SQL code you Cut and Pasted out.

[bruegel]

Thanks for coming back so quickly. For the sake of completeness for other viewers I'll explain what happened.

The answer to your first question is that the query was nested in other queries and I didn't realise that dups were eliminated when the sum function wasn't used until today.

A second query contained two Expressions and I assumed that I needed to select Expression in the Totals field in order for it to work. I declicked Totals and found the query still did its job and that showing Expression in the Total field wasn't needed. When I ran the report based on this query with Totals clicked it returned 343 lines because of dups removal. When I declicked Totals I had the full 12903 rows.

Thanks for the help. I've noted the other solutions and the second one will come in useful. It's a trick that hasn't found its way into the manuals so far as I'm aware.

Pieter

[JoeM]

Thanks for the help. I've noted the other solutions and the second one will come in useful. It's a trick that hasn't found its way into the manuals so far as I'm aware.

Most Access manuals I have seen only show you how to use the Query Builder when creating queries. Most of them don't get into writing/editing the SQL Code directly. That is very powerful, as there are many things you cannot really do with the Query Builder, but you can do by writing the SQL code directly (like nesting queries and creating Union Queries).

If you really want to become proficient at that, the best way is to learn to write SQL code directly.