Presume you want a line for each bank> Suspect you don't need the crosstab, just an aggregate query.
see this link.
https://support.microsoft.com/en-us/...4-ff2312d3f127
You are using the right items (Crosstab, chart), just play with it, best way to learn. Use the wizard, add a chart, play with the query/data, etc. see what it displays.
this is the aggregate query i think i have to use
so basically there are three columns:Code:SELECT Banca,quarter,ingressis-uscites as bilancioFROM ( SELECT banca, Format(data_inizio, "yyyy" & " - " & "q") AS Quarter, Count(data_inizio) AS IngressiS, 0 AS UsciteS FROM (SELECT Iif(azienda IS NOT NULL, azienda, ocfdettaglio.denominazione_soggetto_abilitato) AS Banca, data_inizio FROM aziende RIGHT JOIN (ocfdettaglio LEFT JOIN ocfbanche ON ocfdettaglio.denominazione_soggetto_abilitato = ocfbanche.denominazione_soggetto_abilitato) ON aziende.idazienda = ocfbanche.aziendaid WHERE ( ocfdettaglio.denominazione_soggetto_abilitato <> "\n" ) AND ( noinizio = false ) AND ( data_inizio IS NOT NULL ) AND (Data_inizio BETWEEN #1-1-23# AND #12-31-23#) ORDER BY data_inizio) AS [%$##@_Alias] GROUP BY banca, Format(data_inizio, "yyyy" & " - " & "q") UNION ALL SELECT banca, Format(data_fine, "yyyy" & " - " & "q") AS Quarter, 0 AS IngressiS, Count(data_fine) AS UsciteS FROM (SELECT Iif(azienda IS NOT NULL, azienda, ocfdettaglio.denominazione_soggetto_abilitato) AS Banca, data_fine FROM aziende RIGHT JOIN (ocfdettaglio LEFT JOIN ocfbanche ON ocfdettaglio.denominazione_soggetto_abilitato = ocfbanche.denominazione_soggetto_abilitato) ON aziende.idazienda = ocfbanche.aziendaid WHERE ( ocfdettaglio.denominazione_soggetto_abilitato <> "\n" ) AND ( nocambio = false ) AND ( data_fine IS NOT NULL ) AND (Data_FINE BETWEEN #1-1-23# AND #12-31-23#) ORDER BY data_fine) AS [%$##@_Alias] GROUP BY banca, Format(data_fine, "yyyy" & " - " & "q"))
1 - Bank, 2 - quarter, 3 - Balance (that is a positive or negative number representing the balance of people that joined and left that bank)
this is the output
i can't figure out ho to show the balance through quarters for each bank
Just using you first bank - what do you expect to see? something like
1
1
1
5
Doesn’t really help, we know what a line chart looks like and no idea if your lines relate to the data or not. So I’ll asked the question a different way.
for one specific bank - the top one (I assume the first two rows are the same bank) in 2023 you have 4 quarters but only 2 values. So the line will join points 1 and 4, but how? 1-1-1-4 or 1-0-0-4 or 1-1-1-5? Or something else?
well, banks are unique, cause i have to aggregate data, the first 2 just share almost the same name....
for the data, i'd like the 1-0-0-4, but consider that expecially for old dates i have no data at all.
can a line just disappear when data are not available?
you are being very unclear about the data you have and the way you want to display it. Have you read the links provided? If so you would know that that you would get a line something likethe first 2 just share almost the same name....
If you are display 4 quarters per year, you need to provide the data for each quarter, you can't miss one or more out - so you either provide a 0 (for a line like above) or you use some sort of calculation to 'fill in the blanks'. Something like if you haveCode:/ / \__/
Q1 1
Q2 0
Q3 0
Q4 4
you would need a calculation to change the zeros to a value which puts them 'on the line' between Q1 and Q4
Q1 1
Q2 2
Q3 3
Q4 4
But I'm just guessing because your data is not making sense to me. It appears a bar chart may be a more appropriate based on what you have said, but that is just a guess. Only way a line chart makes sense to me is if you used cumulative values. i.e. from the example above you would have
Q1 1
Q2 1
Q3 1
Q4 5
You might want to consider doing this in excel (as a crosstab) and use the sparkline feature (Access does not have a sparkline feature) to see what it might look like
yes thanks, maybe the problem is that data are really a mess and i'm working to give it a shape, cause the source is not good.