Group duplicate text fields as one?

[jset818]

I want to group those duplicate Project ID so it would only list as 1 Project ID for NOV06864, NOV07794, NOV07908, etc... I also want any Negotiated Cost and Quantity with the duplicated Project ID to add up as sum so I can still divide them. The last column is just a division formula (Round([Negotiated Cost]/[Quantity],2).

[JoeM]

Use an Aggregate Query where you Group By ProjectID, and Sum your Negotiated Cost and Quantity Fields. You can then divide those totals to get your calculated field (might need to do a second query based on your Aggregate Query to do that if it won't do it all in one step).

For information on Aggregate (Totals) Queries, see: https://support.office.com/en-us/art...7-e1024ccfcca8

[jset818]

Use an Aggregate Query where you Group By ProjectID, and Sum your Negotiated Cost and Quantity Fields. You can then divide those totals to get your calculated field (might need to do a second query based on your Aggregate Query to do that if it won't do it all in one step).

For information on Aggregate (Totals) Queries, see: https://support.office.com/en-us/art...7-e1024ccfcca8

Which Aggregate Query function do you recommend I use? All the ones I see are for number data type.

[JoeM]

Make sure you are looking at the "Totals Query" section and not the "Totals Rows" section.

To get you started, do the following:
1. Create a new query based on your table
2. Add the ProjectID, Negotiated Cost, and Quantity fields (and nothing else!)
3. Click on the Totals button (looks like a Sigma). This will add a Totals Row under your query fields.
4. Leave the "ProjectID" Totals row setting to "Group By"
5. Change the Totals row setting to "Sum" under the other two fields.
6. View your results

From there, you can add your calculated division field.

[jset818]

I was able to achieve the result I wanted by Grouping by Sum for Negotiated Cost and Quantity like you said. However, when I added the [Negotiated Cost/Quantity] field, it would give me the message Overflow. Setting it as Sum(Round(NegotiatedCost/Quantity)) gives me the same error too. So, now all I really need to do is divide the SumNegotiatedCost by SumQuantity. I don't know what's causing it to overflow, the value is so small.

[JoeM]

However, when I added the [Negotiated Cost/Quantity] field, it would give me the message Overflow.

Try leaving the query "as-is".
Then, try creating a new query based on the first query, and try doing your division there.
Does that work?

Sometimes things can get a little messy if you try to do too much in a single query.

[jset818]

Try leaving the query "as-is".
Then, try creating a new query based on the first query, and try doing your division there.
Does that work?

Sometimes things can get a little messy if you try to do too much in a single query.

I created a new query with these fields. If I run this, it'll give me the Overflow error. If I remove the division field, it'll run just fine. I don't understand what's actually overflowing. If you look at the screenshot from my previous post, you can see dividing SumNegotiatedCost and SumQuantity should result in numbers not too big or small.

[JoeM]

I think you are misunderstanding me. I am saying to create two queries.

Query 1: The Aggregate (Totals) Query with no division calculation to combine your like records

Query 2: Use Query 1 as the data source for this query. This should NOT be an Aggregate (Totals) Query, so it should NOT have a Total row. Do your division in this one.

Now, your original attempt may work if your change the "Total" row value for your Expression (division calculation). You should NOT use "Group By" under the "Expr1" field. Change that value to "Expression".

[jset818]

I think you are misunderstanding me. I am saying to create two queries.

Query 1: The Aggregate (Totals) Query with no division calculation to combine your like records

Query 2: Use Query 1 as the data source for this query. This should NOT be an Aggregate (Totals) Query, so it should NOT have a Total row. Do your division in this one.

Now, your original attempt may work if your change the "Total" row value for your Expression (division calculation). You should NOT use "Group By" under the "Expr1" field. Change that value to "Expression".

Ahh okay, I get what you mean. So I created another query like you said and used the first query with the Aggregate Query and no division as source data. Interesting enough, the sums do add up but the duplicate Project IDs are back. This is the second query with no Aggregate Query.

[JoeM]

Can you post the SQL code of each query here (just change your query to SQL view and copy and paste the code here)?

[jset818]

Can you post the SQL code of each query here (just change your query to SQL view and copy and paste the code here)?

This is query 1.
SELECT [Budget Item].[Program ID] AS [Project ID], [Budget Item].[Budget Item], Sum([Budget Item].[Negotiated Cost]) AS [SumOfNegotiated Cost], Sum([Budget Item].Quantity) AS SumOfQuantityFROM [Budget Item]
GROUP BY "Meals – AM+PM Breaks PP exceeded Guideline", [Budget Item].[Program ID], [Budget Item].[Budget Item]
HAVING ((([Budget Item].[Budget Item]) Like "AM/PM Breaks*") AND ((Sum([Budget Item].[Negotiated Cost]))>0) AND ((Sum([Budget Item].Quantity))>0));


Query 2

SELECT [Budget Item].[Program ID] AS [Project ID], [Budget Item].[Budget Item], [QC Internal Planning Meals - AM+PM Breaks].[SumOfNegotiated Cost], [QC Internal Planning Meals - AM+PM Breaks].SumOfQuantity, [SumOfNegotiated Cost]/[SumOfQuantity] AS Expr1
FROM [Budget Item] INNER JOIN [QC Internal Planning Meals - AM+PM Breaks] ON [Budget Item].[Program ID] = [QC Internal Planning Meals - AM+PM Breaks].[Project ID]
WHERE ((([Budget Item].[Budget Item]) Like "AM/PM Breaks*" And ([Budget Item].[Budget Item]) Is Not Null));

[JoeM]

What did you name the first query (Query1)?

[jset818]

What did you name the first query (Query1)?

It's called QC Internal Planning Meals - AM+PM Breaks

[JoeM]

OK, what happens if you just try this:

SELECT 
    [Project ID], 
    [Budget Item], 
    [SumOfNegotiated Cost], 
    SumOfQuantity,
    [SumOfNegotiated Cost]/[SumOfQuantity] AS Expr1
FROM 
    [QC Internal Planning Meals - AM+PM Breaks];