a little challenge

[Oregoncoder]

The attached are two columns of data from a larger table. The two columns are "Yearjoined" and "inactiveyear". I am trying to create a summary that show the following: by year, how many have joined, how many went inactive and then how many were actually active. I have been scratching my head on this a while and can't quite figure out how to count the active members by year.

[madpiet]

"No match at all" (sorry, too much Skyrim).

The trick is to create a table with all the years from the minimum to the maximum (for join and leave). (Just a record for each year from whatever your start years was to your final year. Then you don't join the two tables, but do this:

SELECT tblyearlystatus.PersonID, tblyearlystatus.YearJoined, tblyearlystatus.Inactiveyear, YearsList.Yar
FROM tblyearlystatus, YearsList
WHERE (((YearsList.Yar)>=[YearJoined] And (YearsList.Yar)<[InactiveYear]));


You may need to play with the >= and < to get exactly what you want. Depends if you include the Inactive Year or not.

[mike60smart]

Hi
I would suggest that your table structures are not quite correct.

Can you show us a screenshot of your relationship window showing the Parent table and the corresponding table that these fields are from?

[madpiet]

Mike, The relationship window is there for you to examine. Super easy if you actually open the file. That's how I answered the question. I wrote a query against the data he provided. Maybe you should try it sometime. If you actually knew what you were talking about, I might take you seriously, but I honestly don't think you do. But feel free to prove me wrong.

With this structure:

CREATE TABLE Members ( MemberID, JoinYear, LeaveYear)

It's a snap... if you're not missing table I already described.
All you are missing is an auxiliary table of Years. (CREATE TABLE Years Yr INT PRIMARY KEY). After that, this is super easy, as I outlined above. If you have "off by one" errors (one too many years or one too few), adjust the inequality operators (maybe < instead of <=, for example).

PM Mike if you need further help. suuuuuuuure. Try that.

[madpiet]

have joined: COUNT(*) GROUP BY YearJoined
left: same, but GROUP BY YearLeft
went inactive... that's basically when their end Year or whatever was.
actually active.... that's the trickiest one - much easier with a "helper" table of years as I outlined in my first response. because "active" means "joined on or before [Year], and left on or after [Year]".

The maybe unclear thing I'm doing in my code is not joining the two tables on a specific column. Instead, since I don't join them, it creates a cross join (all possible combinations of the contents of table A and table B). And then I filter that down to just the years meeting my criteria. The Years "helper" table just makes all the records show up (and makes the query much easier to write).

[mike60smart]

Well I must say I have never seen a table based on just two fields with no PK.

All i was trying to ascertain was what the actual table relationships are. Is this the wrong way of doing things?

[ArviLaanemets]

What about cases, where someone joins in some year, and goes inactive in same year?

[June7]

OP stated these fields are only 2 from a larger table. In this case, knowing other fields and table relationships aren't really necessary to formulate a solution - concept employed will be the same.

[madpiet]

Well I must say I have never seen a table based on just two fields with no PK.

All i was trying to ascertain was what the actual table relationships are. Is this the wrong way of doing things?

Okay, Professor. It’s a mad small table in the example so I took liberties. I was just working on the query to answer his question. Yeah I know about indexing… it was just that he had like 10 records in his sample database. So sure index primary and foreign keys. YAWN

[Oregoncoder]

Didn't mean to start an argument. Thanks for the help. What I was attempting to achieve was more like: It would be a count of each for the given year.

[June7]

Either do the aggregation first then JOIN to ListYears or aggregate after JOIN so that all years will show even if no data.

[Oregoncoder]

Either do the aggregation first then JOIN to ListYears or aggregate after JOIN so that all years will show even if no data.

Would all of this be simpler to do in VBA?

[June7]

The JOINED and INACTIVE aggregation by year is fairly simple.

Calculating total members each year is complicated. This has to take into account the joins and quittings for all years up to the year of calculation. That most likely will involve a correlated subquery or Domain Aggregate function.

Query1: CountInactive
SELECT tblyearlystatus.Inactiveyear, Count(tblyearlystatus.PersonID) AS Inactive
FROM tblyearlystatus
GROUP BY tblyearlystatus.Inactiveyear;

Query2: CountJoined
SELECT tblyearlystatus.YearJoined, Count(tblyearlystatus.PersonID) AS Joined
FROM tblyearlystatus
GROUP BY tblyearlystatus.YearJoined;

Query3:
SELECT YearsList.Yar, CountInactive.Inactive, CountJoined.Joined, DCount("*","tblyearlystatus","YearJoined<=" & [Yar])-DCount("*","tblyearlystatus","InactiveYear<=" & [Yar]) AS Active
FROM CountJoined RIGHT JOIN (CountInactive RIGHT JOIN YearsList ON CountInactive.Inactiveyear = YearsList.Yar) ON CountJoined.YearJoined = YearsList.Yar;

or

SELECT YearsList.Yar, CountInactive.Inactive, CountJoined.Joined, (SELECT Count(*) AS Joined FROM tblyearlystatus WHERE YearJoined<=[Yar])-(SELECT Count(*) AS Inactive FROM tblyearlystatus WHERE InactiveYear<=[Yar]) AS Active
FROM CountJoined RIGHT JOIN (CountInactive RIGHT JOIN YearsList ON CountInactive.Inactiveyear = YearsList.Yar) ON CountJoined.YearJoined = YearsList.Yar;

Yar	Inactive	Joined	Active
1997		9	9
1998		1	10
1999		4	14
2000		1	15
2001			15
2002		3	18
2003		4	22
2004		8	30
2005		7	37
2006		8	45
2007		9	54
2008		33	87
2009		21	108
2010	46	21	83
2011	4	20	99
2012	15	22	106
2013	2	17	121
2014	32	24	113
2015	22	12	103
2016	11	20	112
2017	16	22	118
2018	22	16	112
2019	17	22	117
2020	23	12	106
2021	18	15	103
2022	10	31	124
2023	12	37	149
2024	49	15	115

[Oregoncoder]

-------------

[Oregoncoder]

That worked out just right. Greatly appreciate your help.