Total query based on start year and end year

[diegomarino]

hi, i have the table you can see attached, it has this structure

ID	Nome	Cognome	Data_nascita	DENOMINAZIONE_SOGGETTO_ABILITATO	data_inizio	Data_fine	NoCambio	Data/ora creazione	Data/ora modifica	Comune
260195	name	surname	19/08/1972	BANCA WISE DIALOG BANK SPA-BANCA WIDIBA		20/06/2022	No	20/06/2022 18:23:15
260195	name	surname	19/08/1972	BANCA WISE DIALOG BANK SPA - BANCA WIDIBA	20/06/2022		No	20/06/2022 18:23:16
260201	name	surname	29/09/1974	FINECOBANK BANCA FINECO SPA		20/06/2022	No	20/06/2022 18:23:15
260201	name	surname	29/09/1974	FINECOBANK BANCA FINECO S.P.A.	20/06/2022		No	20/06/2022 18:23:16
260204	name	surname	10/01/1979	BANCA GENERALI SPA		24/09/2022	No	31/08/2022 20:20:33
260204	name	surname	10/01/1979	\N	31/08/2022	24/09/2022	No	31/08/2022 20:20:34
260204	name	surname	10/01/1979	BANCA NAZIONALE DEL LAVORO S.P.A.	24/09/2022		No	24/09/2022 19:36:56
260210	name	surname	15/02/1979	FIDEURAM-INTESA SANPAOLO PRIVATE BANKING SPA IN FORMA ABBREVIATA FIDEURAM SPA		20/06/2022	No	20/06/2022 18:23:15
260210	name	surname	15/02/1979	FIDEURAM - INTESA SANPAOLO PRIVATE BANKING SPA IN FORMA ABBREVIATA FIDEURAM SPA	20/06/2022		No	20/06/2022 18:23:16
260221	name	surname	28/04/1977	\N		18/07/2022	No	18/07/2022 18:40:04
260221	name	surname	28/04/1977	INTESA SANPAOLO SPA	18/07/2022		No	18/07/2022 18:40:04
260224	name	surname	14/06/1974	FIDEURAM-INTESA SANPAOLO PRIVATE BANKING SPA IN FORMA ABBREVIATA FIDEURAM SPA		20/06/2022	No	20/06/2022 18:23:15
260224	name	surname	14/06/1974	FIDEURAM - INTESA SANPAOLO PRIVATE BANKING SPA IN FORMA ABBREVIATA FIDEURAM SPA	20/06/2022		No	20/06/2022 18:23:16
260241	name	surname	27/06/1977	VALORI & FINANZA INVESTIMENTI SIM S.P.A.		20/06/2022	No	20/06/2022 18:23:15
260241	name	surname	27/06/1977	VALORI & FINANZA INVESTIMENTI SIM SPA	20/06/2022		No	20/06/2022 18:23:16
260242	name	surname	27/11/1960	BANCA WISE DIALOG BANK SPA-BANCA WIDIBA		20/06/2022	No	20/06/2022 18:23:15
260242	name	surname	27/11/1960	BANCA WISE DIALOG BANK SPA - BANCA WIDIBA	20/06/2022		No	20/06/2022 18:23:16
260243	name	surname	20/02/1950	FIDEURAM-INTESA SANPAOLO PRIVATE BANKING SPA IN FORMA ABBREVIATA FIDEURAM SPA		20/06/2022	No	20/06/2022 18:23:15
260243	name	surname	20/02/1950	FIDEURAM - INTESA SANPAOLO PRIVATE BANKING SPA IN FORMA ABBREVIATA FIDEURAM SPA	20/06/2022		No	20/06/2022 18:23:16
260244	name	surname	24/10/1970	BANCA NAZIONALE DEL LAVORO S.P.A.		24/09/2022	No	24/09/2022 19:36:56		PESCARA
260244	name	surname	24/10/1970	\N	24/09/2022		No	24/09/2022 19:36:56
260248	name	surname	22/03/1986	BANCA MEDIOLANUM SPA	04/11/2021		No	27/06/2022 16:30:42
260250	name	surname	12/04/1956	IW PRIVATE INVESTMENTS SIM SPA		20/06/2022	No	20/06/2022 18:23:15
260250	name	surname	12/04/1956	IW PRIVATE INVESTMENTS SIM S.P.A	20/06/2022		No	20/06/2022 18:23:16
260257	name	surname	07/06/1956	FINECOBANK BANCA FINECO SPA		20/06/2022	No	20/06/2022 18:23:15
260257	name	surname	07/06/1956	FINECOBANK BANCA FINECO S.P.A.	20/06/2022		No	20/06/2022 18:23:16
260263	name	surname	03/02/1960	FINECOBANK BANCA FINECO SPA		20/06/2022	No	20/06/2022 18:23:15

i'd like to know the trend of every bank year per year making a chart, so for example i'll count how many people started (data_inizio means "start date") working in "BANCA MEDIOLANUM SPA" in a year and then i subtract the number of people quit (data_fine means "end date") working with same bank in that year
then i'd ike to make a chart

[CJ_London]

use an aggregate query grouping on the bank name and counting the number of dates in each column.

then use a second query based on the first to subtract the count of leavers from the count of starters

[ArviLaanemets]

In my example, all people leaving in given year independent on year they started are counted!

[diegomarino]

thanks, maybe i didn't understand well, but i'd like to see changes yearly, in this way i just have the total number

[CJ_London]

sorry forgot about year - suggest try ArviLaanemets example

[diegomarino]

In my example, all people leaving in given year independent on year they started are counted!

sorry, for some reason i must missed this. i see a lot of tables wih names like "od", what's that?

FROM [Copia di OcfDettaglio] odf)

this is an alias?

then, i'd like to get the new employee and quitted employee grouped per bank too, not only per year

[ArviLaanemets]

sorry, for some reason i must missed this. i see a lot of tables wih names like "od", what's that? ... this is an alias?

Yes. Aliases allow to refer to different instances of same table in same query, and generally they make queries more readable too - especially when you use such long table names and have spaces there :-)

... then, i'd like to get the new employee and quitted employee grouped per bank too, not only per year

This will be entirely other query - based on list of employees instead of list of years (Assumed banks are from column

DENOMINAZIONE_SOGGETTO_ABILITATO)

Btw., is the column ID in your table an ID for employee, or can an employee have entries with different ID there - from this depends, must the list of employees contain either ID, forename, last name and bank, or forename, last name and bank only. And also, can an Employee have a single bank only, or more - like get employed when client of one bank, and being a client of another one when getting unemployed?

I can't help with your query on weekend, as I don't have Access installed in my home computer. So either you have to wait until Monday, or someone other here will step in.

[diegomarino]

ok thanks i'll try to figure out.
ID is an id for employees, so one employee-one id, and am employee can have just one bankat time.
i'll post the solution (if i find it) or you'll post the solution

[xps35]

You want to report the trend per bank. So is BANCA WISE DIALOG BANK SPA-BANCA WIDIBA the same as BANCA WISE DIALOG BANK SPA - BANCA WIDIBA? And is FINECOBANK BANCA FINECO SPA the same as FINECOBANK BANCA FINECO S.P.A.? Maybe for you, not for Access.

I don't understand the content of your table. For example:

Is this about one and the same person? Where is he leaving on 19-10-2022.

We need (much) more information (our you need better data).

[diegomarino]

1)BANCA WISE DIALOG BANK SPA-BANCA WIDIBA is the same as BANCA WISE DIALOG BANK SPA - BANCA WIDIBA, but i can't do nothing, it is a report of banca d'italia pretty sh..y, I'm normalizing datas, but until then i'll use a listbox so i can choose every values i want, in this case both; i made a table listing every bank name and i'm giving a foreign key pointing to one entity (the real bank) to all different names of the same bank
2) this ID 312249 is the same person: again another problem of banca d'italia that makes a lot of mistakes, do not consider that, i made alot of segnalations

morever there are some other issue:

this table is made by calculation on the list of Professional Register of the financial advisors;this table(you don't have that) is a list of unique records (financial advisors of course), periodically i download it and, since many people changes bank, i insert old banks nd new banks in the table you have (copia di ocfdettaglio). sometimes i look in the web register and i copy the whole work experience of a guy, but mostly is automatic;the problem is, obviously, that the first work experience i have of a person has a first record thatr is the old bank with end date and another record with the new bank and start date, so the first experience has not start date (i'm thinking how to solve that, maybe i just use in a query as start date the end date-4 years)
btw i need this query, then i'll solve those problems

[xps35]

It is not that difficult in itself to calculate the number of people who have started per year per bank. You can do that with a query like the one below.

SELECT DENOMINAZIONE_SOGGETTO_ABILITATO, Year(data_inizio) AS Anno, Count(ID) AS NumeroDiPartenti
FROM [Copia di OcfDettaglio]
GROUP BY DENOMINAZIONE_SOGGETTO_ABILITATO, Year(data_inizio)
HAVING Year(data_inizio) Is Not Null;

In a similar way you can tell people who have left. The point remains that such queries only return reliable data if the base data is reliable. Garbage in, garbage out!

[diegomarino]

thanks, unfortunely this garbage forces me to make extra work.

however, the point is to have just one query in which i can see the number of who left the bank and the number of who joined the bank , like this (don't mind data, it'excel)

DENOMINAZIONE_SOGGETTO_ABILITATO	Anno inizio	N° start	N° ended
ACOMEA SOCIETA' DI GESTIONE DEL RISPARMIO SPA	2017	1	4
ACTIVTRADES EUROPE S.A.	2022	1	6
ACTIVTRADES EUROPE S.A. MILAN BRANCH	2022	1	2
AGORA INVESTMENTS - SOCIETA' DI GESTIONE DEL RISPARMIO SPA	2022	5	1
AGORA INVESTMENTS-SOCIETA' DI GESTIONE DEL RISPARMIO SPA	2022	5	7
AGOS SIM SPA	1993	1	1
AGOS SIM SPA	1994	2	1
AGOS SIM SPA	1996	1	0

[xps35]

I've made some more queries that would work if the basics were OK (see attachment).
QryStart gives all (697) combinations of bank/year plus the number of starters.
QryEnded gives the number of leavers (588) per bank/year.
QryMatches shows that only 244 cases could be linked (bank and year equal in QryStart and QryEnded). This should be the query you are looking for. The fact that so little data can be linked is of course due to the different spellings of the bank names. For the sake of completeness, QryNoEnd and QryNoStart show which combinations of bank and year could not be linked.
In addition, of course, my selection of data in post #9 shows that the data is highly unreliable in other areas as well.
I stand by my opinion that this action only makes sense if you first get the data in order.

[diegomarino]

thanks very much.
you're right, but i'm fixing all those data.

i looked your query "QryMatches" that should give the result i want; unfotunely i've just tried this query, but some data is missing: e.g "AGOS SIM SPA" is not present, even if has people employed and people that quit

[xps35]

You are right. I matched on bank + year, which is not right. The starters are from 1993, 1994 and 1996. The quitters from 1995 and 1999. So no matches. I will try again.