Line Chart - Crosstab query

[diegomarino]

Hi,
i'd like to have a line chart based on those data (except the first column "totvar"


I'm not an expert of charts, but it is achievable? or should i use a more basic query?


thx

[CJ_London]

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

[Bulzie]

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.

[diegomarino]

this is the aggregate query i think i have to use

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"))

so basically there are three columns:
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

[CJ_London]

Just using you first bank - what do you expect to see? something like

1
1
1
5

[diegomarino]

this is what i expect to see (i hope you like arts) don't mind data, just look at the visual

[CJ_London]

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?

[diegomarino]

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?

[CJ_London]

the first 2 just share almost the same name....

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 like

     /
    /
\__/

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 have

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

[diegomarino]

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.