Graphic, members at a certain year

[Rainer2000]

It sound so simple:
I would like to have a graphical view how many members were/are in my sports club during the last years.

I can make a graph how many have joined on a certain year:
TRANSFORM Count(*) AS [Anzahl] SELECT (Year([Datejoined])) FROM [Myquery]GROUP BY (Year([Datejoined])) PIVOT [AnzahlvonNachname];

but that is not what I want!

Rainer

 

[dhookom]

I can't see anything wrong with the query from here. Can you provide some information that might help us provide some suggestions? We don't have a clue regarding your structure or data or what you expect to see.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rainer2000]

Hallo Duane,
thanks for your reply!
What I want: on the x-axis: The year, e.g 2003; 2004; 2005..
on the Y -axis total number of members of the club (e.g.summary count of second names). The code I listed always gives me the graphical view of members who have joined at a certain year, no matter what I try.
The data base has the names of the members & the year of their entry.

Rainer

 

[dhookom]

Do you have anything that stores the year or date of termination?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rainer2000]

Yes I got that too

Rainer

 

[dhookom]

Hint: when you are asked if you have a field, you should consider replying with the field name and possibly the data type.

Assuming you have a table "Rainer2000" with fields ID (pk), DateJoined, DateLeft, and AnzahlvonNachname...

First create a record set of all years:

=== qselRainerYears ====
SELECT Year([DateJoined]) AS Yrs
FROM Rainer2000
GROUP BY Year([DateJoined]);

Then create a crosstab with SQL of:

TRANSFORM Count(Rainer2000.ID) AS CountOfID
SELECT qselRainerYears.Yrs
FROM Rainer2000, qselRainerYears
WHERE (((qselRainerYears.Yrs) Between Year([DateJoined]) And Year(Nz([DateLeft],Date()))))
GROUP BY qselRainerYears.Yrs
PIVOT Rainer2000.AnzahlvonNachname;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rainer2000]

Hallo Duane,
I got part 1 working: //Name qselRainerYears
SELECT Year([Beitrittsdatum]) AS Yrs // Date Joined
FROM Adressen_TVKall // MyTable with all members
GROUP BY Year([Beitrittsdatum]); // Date Joined
This gives me a list of years-nothing else.

Part 2 is more complex:
I got something working which gives something weird:

TRANSFORM Count(Adressen_TvKall.ID) AS CountOfID
SELECT qselRainerYears.Yrs
FROM Adressen_TvKall, qselRainerYears
WHERE (((qselRainerYears.Yrs) Between Year([Adressen_TVKall].[Beitrittsdatum]) And Year(Nz([Adressen_TvKall].[Ausgetreten-Datum],Date()))))
GROUP BY qselRainerYears.Yrs
PIVOT Adressen_TvKall.Nachname; //MyTable.[Lastname]

I cannot understand what the line "pivot...." does.

Rainer

 

[dhookom]

Your original posting had the PIVOT so I thought you needed it in your query. Without knowing anything about your database, I assumed you knew why you were using it ;-)

Perhaps you need to start from the beginning. I appreciate your // Date Joined remarks which help me understand your data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]