Solved Grouping within a group or grouping DLIST, is it possible?

[dxfoxd]

I have a table with 300k records containing the following data (sample)

Id    p_year    p_month    code
59901    2024    02    820996
59902    2024    01    5721012
59903    2024    02    826193
59904    2024    01    820139
59905    2024    02    821012
59906    2024    01    821032
59907    2024    01    821173
59908    2024    02    5741237
59909    2024    01    821065
59910    2024    01    826193
59911    2024    02    820708
59912    2024    02    826217
59913    2024    01    821086

The first 3 characters of the code field are all records belonging to a group, for example 572 or 589 or 802.

I want to create a query that summarizes if records exists for each group in a specific year and month

Result should be

Group Year JAN FEB MAR etc
082   2023  X   X   X
572   2024  X   X

alternative I tried to get a result with the months in a string variable using DLIST, but that function does not have a grouping option. I already tried some other queries, but every time it is not quite what I had in mind.

For example

SELECT Left(Format([code],"0000000"),3) AS code, a.p_year AS [year], a.p_month AS JAN, a.p_month AS FEB
FROM be_performance AS a
GROUP BY Left(Format([code],"0000000"),3), a.p_year, a.p_month, a.p_month
HAVING (((a.p_month)=1)) OR (((a.p_month)=2));

But this displays a line for each month. It should be something with grouping in a group, but I’m stuck. Now I'm wondering if this is possible within a query. Does anyone has an idea?

Art.

 

[theDBguy]

Looks like a crosstab query might work. Can you post a sample db?

 

[Ranman256]

make a query based on your table that makes the groups say: qsGrpData
select *, Left([code],3) as Group, Format([mo] & "/1/2000","mmm") as MonthName from table

now you can produce crosstabs or other groups  using qsGrpData.

 

[plog]

First and most important idea--fix your tables. Each discrete piece of data needs to be stored in its own field. You are storing 2 pieces of data in your code field--that is incorrect. If part of the code identifies a group, then that part needs to be in its own field. Your code field should be broken into two fields at the table level.

Second, you will need 2 queries to produce the results you want. You are on the right track with your first one. You need to first prepare the data vertically (with each month on its own row), then you can PIVOT the data to the final output you want.

Revise your query to this:

SELECT Left(Format([code],"0000000"),3) AS group, p_year, p_month
FROM be_performance
GROUP BY Left(Format([code],"0000000"),3), p_year, p_month

Then use the below link to make another query using the above query as its source. The second query will be a cross-tab 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

Further nitpicks--no need for an alias (renaming your FROM table to a) when you only have 1 table in your query, don't use the name of an existing field as the name of a calculated field in your query (I changed it from code to group in the above). And of course---fix your tables as I described in the first paragraph.

 

[arnelgp]

see also Query1:

 

[ebs17]

TRANSFORM
   COUNT(a.Id) AS X
SELECT
   Left(Format(a.code, "0000000"), 3) AS code,
   a.p_year AS [year]
FROM
   be_performance AS a
GROUP BY
   Left(Format(a.code, "0000000"), 3),
   a.p_year
PIVOT
   MonthName(a.p_month, True) IN("Jan", "Feb", "Mar", …)

table with 300k records

It would improve performance if something like Left(Format(a.code, "0000000"), 3) didn't have to be calculated first, but rather such atomic information was available for GROUP as an indexed table field. @plog had already pointed out the missing normalization step.

The same would apply if instead of, for example, "Feb" a 2 would be sufficient as a column heading.

 

[dxfoxd]

Thx all! I wil bookmark this page in case the performance drops and I have to normalise the table. But momentarily the query works within 2 seconds.

The solution of Eberhard worked, with all in the same query. However, and that is also anoying in my other VBA issues, that the locale setting of Accesss still expects the locale (Dutch) month names in the query and not the English versions. So:

PIVOT MonthName(a.perf_m, TRUE) IN("JAN", "FEB", "MRT", "APR", "MEI", "JUN", "JUL", "AUG", "SEP", "OKT", "NOV", "DEC")

works, but

PIVOT MonthName(a.perf_m, TRUE) IN("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")

leaves the columns MAR, MAY and OCT empty.

I had the same issue in Excel VBA and I had to use a worksheet function TEXT because "FORMAT" combined with "[$-409]mmm" didn't work. Or is this possible in an Access query?

Art

 

[ebs17]

The easiest way is to use the numbers from the table field.

PIVOT a.perf_m

Numbers are international.

Do you have a Dutch regional setting but want output in English?
Functions such as MonthName or Format always deliver according to the regional settings.

To get to your conclusion, I can think of two options.
1) You create an additional mapping table for the 12 months, where the English expression is compared to the Dutch expression. This table would then have to be included in the query.
2) You can convert the content of one regional setting into the content of another regional setting using a function using the Win API. This function can be built into the query.

 

[dxfoxd]

Thx. Too much trouble. I will stick with the Dutch months (close to English anyway)