Combine rows with like data

[halnurse]

I have a query that shows when material is on back order. The purpose of this query is to show how much material still needs to be received. Right now if part of the total amount of material needed is received the query shows the name of the material, total amount ordered, quantity received, and how much still needs to be received. For example, say that I ordered 10 fixture A's and 3 are received Wednesday. The query will show that 7 are on back order. If I then receive 4 on Friday, the query should show one line item with a total of 7 received and 3 on back order. However currently the query would show two separate line items, one with 3 received and 7 on back order and then a second line with 4 received and 6 on back order. Is there a way to combine these two lines? I already have "Group By" on the item description field. When I make the received quantity field "Sum" on the totals line an error occurs that says "You tried to execute a query that does not include the specified expression '[qryMaterial_Ordering].[tblMaterial_Tracking].[Quantity_Ordered]-[qryMaterial_Ordering].[Received]' as part of an aggregate function."



Thanks for you time in advance

[isladogs]

I think your problem is the expression:

[qryMaterial_Ordering].[tblMaterial_Tracking].[Quantity_Ordered]-[qryMaterial_Ordering].[Received]

I believe the part in RED or that in BLUE should be deleted depending whether the field is in the table or query

[Micron]

You'd have an easier time with calculated form controls, methinks. One textbox for Required, one for DSum of all received (both can come from a query), one for Required - DSum of received.

[halnurse]

@Micron So have two columns for the quantity ordered field?

[Micron]

As many as you need. I forgot to mention that this might not be a suitable approach for your situation, but you didn't reveal much about what's behind the effort. If the number of groups will vary, then I don't see that working very well. You only stated there were 3 time slots. Make sure you address the suggestion in post 2 in case it is the solution. I'm afraid I didn't try hard to understand it as the language makes it a bit difficult to follow.