1

I'm trying to manage our inventory on access but I'm very new to it. My raw data lists out items, quantity requested, and the crews that requested it. I want to see the sum of the quantity requested for an item and the associated crews but I want all the crews to be in one cell. I've tried searching up the question and people suggest using the concatenate function but I don't know how to change it for my situation as I want the totals as well (and I don't know anything about SQL or VBA). Any help would be appreciated! This is how my data looks 

||ID||  ||ITEM||    ||Qty requested||   ||Crew||
||1||   ||bolt||    ||2||               ||1234||
||1||   ||bolt||    ||30||              ||4567||
||2||   ||saw||     ||4||               ||7890||
||3||   ||screw||   ||10||              ||1234||
||4||   ||glove||   ||1||               ||1111||
||4||   ||glove||   ||5||               ||222||

but I want it to look like this

||ID||  ||ITEM||    ||Qty requested||   ||Crew||
||1||   ||bolt||    ||32||              ||1234,4567||
||2||   ||saw||     ||4||               ||7890||
||3||   ||screw||   ||10||              ||1234||
||4||   ||glove||   ||6||               ||1111,222||

I've tried running a query but the closest I can get is a table with the items and sum of the quantities but not the crews. Thank you!

Name23
  • 11
  • 1
  • 1
    look here https://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value – BlooB Oct 12 '17 at 13:11
  • I tried doing that but what I have sums up the all of the quantities and lists all the Crews, I have this SELECT Sum(Qty_Requested) AS Total_Qty_Requested , ConcatRelated("Crew", "Table") AS Crew FROM Table t; – Name23 Oct 12 '17 at 14:17
  • Did you download the function and add it? – BlooB Oct 12 '17 at 14:18
  • Yeah I did, I think I didn't specify in my query that it should repeat based on Item name (like recursion?) but I don't know how to do that – Name23 Oct 12 '17 at 14:25
  • Should edit your question to show the attempted query SQL. – June7 Oct 12 '17 at 19:08

1 Answers1

0

Your table is named Table?

Consider:

SELECT ID, Item, Sum(Qty_Requested) AS Total_Qty_Requested, ConcatRelated("Crew", "Table", "ID=" & [ID]) AS Crews FROM Table GROUP BY ID, Item;

June7
  • 19,874
  • 8
  • 24
  • 34