Welcome.
My first post = first problem with Access. I use MS Access for a short time. I am familiar with MS SQL, but Access disappoints me. It is difficult for me to write commands, because many options does not work.
My current problem. I want to aggregate and add column with aggregated statistics to table in a one query. OVER PARTITION does not work, so I need to write 2 queries(1 - group by SEX INDEX LEVEL 2 - group by SEX INDEX). I am sure that more sophisticed solution can do this in one query, but i need help.
data for example
my input
ID SEX YEAR INDEX LEVEL
1 1 MALE 2019 WEALTH low
2 2 MALE 2018 STRENGHT low
3 3 MALE 2019 WEALTH low
4 4 MALE 2019 WEALTH average
5 5 MALE 2018 STRENGHT high
6 6 MALE 2018 WEALTH average
7 7 MALE 2019 STRENGHT high
8 8 MALE 2018 STRENGHT low
9 9 MALE 2019 STRENGHT low
10 10 MALE 2018 WEALTH low
11 11 MALE 2019 STRENGHT high
12 12 MALE 2018 STRENGHT low
13 13 MALE 2019 STRENGHT low
14 14 MALE 2019 STRENGHT low
15 15 MALE 2019 STRENGHT high
16 16 FEMALE 2018 WEALTH low
17 17 FEMALE 2018 STRENGHT average
18 18 FEMALE 2019 WEALTH high
19 19 FEMALE 2018 WEALTH average
20 20 FEMALE 2018 STRENGHT low
21 21 FEMALE 2019 WEALTH high
22 22 FEMALE 2018 WEALTH average
23 23 FEMALE 2018 WEALTH average
24 24 FEMALE 2018 STRENGHT low
25 25 FEMALE 2018 STRENGHT low
26 26 FEMALE 2018 WEALTH low
27 27 FEMALE 2019 STRENGHT high
28 28 FEMALE 2019 STRENGHT low
29 29 FEMALE 2018 WEALTH high
30 30 FEMALE 2019 STRENGHT high
my output should look like this
SEX INDEX LEVEL x.x x.y
1 FEMALE STRENGHT low 4 7
2 FEMALE STRENGHT high 2 7
3 FEMALE STRENGHT average 1 7
4 FEMALE WEALTH low 2 8
5 FEMALE WEALTH high 3 8
6 FEMALE WEALTH average 3 8
7 MALE STRENGHT low 6 10
8 MALE STRENGHT high 4 10
9 MALE WEALTH low 3 5
10 MALE WEALTH average 2 5
Thanks in advance.
My first post = first problem with Access. I use MS Access for a short time. I am familiar with MS SQL, but Access disappoints me. It is difficult for me to write commands, because many options does not work.
My current problem. I want to aggregate and add column with aggregated statistics to table in a one query. OVER PARTITION does not work, so I need to write 2 queries(1 - group by SEX INDEX LEVEL 2 - group by SEX INDEX). I am sure that more sophisticed solution can do this in one query, but i need help.
data for example
my input
ID SEX YEAR INDEX LEVEL
1 1 MALE 2019 WEALTH low
2 2 MALE 2018 STRENGHT low
3 3 MALE 2019 WEALTH low
4 4 MALE 2019 WEALTH average
5 5 MALE 2018 STRENGHT high
6 6 MALE 2018 WEALTH average
7 7 MALE 2019 STRENGHT high
8 8 MALE 2018 STRENGHT low
9 9 MALE 2019 STRENGHT low
10 10 MALE 2018 WEALTH low
11 11 MALE 2019 STRENGHT high
12 12 MALE 2018 STRENGHT low
13 13 MALE 2019 STRENGHT low
14 14 MALE 2019 STRENGHT low
15 15 MALE 2019 STRENGHT high
16 16 FEMALE 2018 WEALTH low
17 17 FEMALE 2018 STRENGHT average
18 18 FEMALE 2019 WEALTH high
19 19 FEMALE 2018 WEALTH average
20 20 FEMALE 2018 STRENGHT low
21 21 FEMALE 2019 WEALTH high
22 22 FEMALE 2018 WEALTH average
23 23 FEMALE 2018 WEALTH average
24 24 FEMALE 2018 STRENGHT low
25 25 FEMALE 2018 STRENGHT low
26 26 FEMALE 2018 WEALTH low
27 27 FEMALE 2019 STRENGHT high
28 28 FEMALE 2019 STRENGHT low
29 29 FEMALE 2018 WEALTH high
30 30 FEMALE 2019 STRENGHT high
my output should look like this
SEX INDEX LEVEL x.x x.y
1 FEMALE STRENGHT low 4 7
2 FEMALE STRENGHT high 2 7
3 FEMALE STRENGHT average 1 7
4 FEMALE WEALTH low 2 8
5 FEMALE WEALTH high 3 8
6 FEMALE WEALTH average 3 8
7 MALE STRENGHT low 6 10
8 MALE STRENGHT high 4 10
9 MALE WEALTH low 3 5
10 MALE WEALTH average 2 5
Thanks in advance.