Simple summed values for item type (1 Viewer)

[ARC20]

I'm thinking this should be simple, but I am very much a novice and could use some guidance.

I have data that is coming in the form of something like this which I have entered as a table:

Date	Type	Quantity
1/14/22	A	4
1/18/22	B	2
1/24/22	A	5
2/3/22	C	4
2/5/22	B	8

I am trying to create a query to have a row for each month that will sum the quantities for each type:

	A	B	C
2022-01	9	2	0
2022-02	0	8	4
2022-03

I've been able to group the dates by formatting the date as yyyy-mm and sum the values for one of the Types, but can't figure out how to have separate columns for each Type with summed values. Any suggestions?

 

[CJ_London]

Investigate using a crosstab query

Row heading - you year month calc
Column heading - type
Value - sum quantity

 

[plog]

Actually, I would call the skills needed for this advanced. Here's the steps and some links:

1. Create an aggregate query

Sum data by using a query - Microsoft Support

Learn how to use an aggregate function in Access to sum the data in a query result set.

support.microsoft.com

2. In that query create a calculated field that converts your date values to the date of the first day of the month they occur.

MS Access: DateValue Function

This MSAccess tutorial explains how to use the Access DateValue function with syntax and examples. The Microsoft Access DateValue function converts a string to a date.

www.techonthenet.com

MS Access: Month Function

This MSAccess tutorial explains how to use the Access Month function with syntax and examples. The Microsoft Access Month function returns the month (a number from 1 to 12) given a date value.

www.techonthenet.com

MS Access: Year Function

This MSAccess tutorial explains how to use the Access Year function with syntax and examples. The Microsoft Access Year function returns a four-digit year (a number from 1900 to 9999) given a date value.

www.techonthenet.com

3. In that query you will also need the Type and Quantity fields. See the first link on what aggregate designations you need to assign each.

4. Once the above query generates the correct data you save it. It won't have each Type as a field, but you should be able to verify the data is correct.

5. You build another query using the first query you made and PIVOT it so that each Type becomes a field in the query.

Make summary data easier to read by using a crosstab query - Microsoft Support

Crosstab queries are useful for summarizing data, calculating statistics and then grouping the results.

support.microsoft.com

 

[ARC20]

Thank you, I got it working using the crosstab query CJ suggested.

To Plog: what is the advantage of converting the dates to the first day of the month over just changing the format.
I just used an expression Format([Pickup Date],"yyyy-mm") and it appears to group okay on the surface.

As a follow up question, I also want to create a report with a bar chart for quantities in each month over the last twelve months. Do I need to filter the data in a query to limit the range to twelve months first or can I have a chart filter to the last twelve months on its own without the need for an additional query?

 

[plog]

With an actual date (e.g. first date of the month) you can do date related things--you can sort by Date, you can filter by date, you can use built -n Date functions of Access. When you convert it to your format its a string (e.g. '2022-01') you can't do those things as easily.