Statistik over en given tidsramme og anden parameter

For at afgrænse statistikken til en periode, må du jo nødvendigvis have et datofelt, der angiver datoen for en konsultation. Når du har det, kan du i forespørgslen angive et kriterie ved hjælp af det reserverede ord Between. I datofeltet i forespørgslens kriterielinie skriver du:

Between [Indtast første dato:] And [Indtast sidste dato:]

De 4-kantede paranteser angiver, at du promptes for en indtastning, valget af tekst indenfor paranteserne er fri.

M.h.t. din dropdown, kan du i forespørgslens felt vedr. institutioner, skrive et kriterie der henviser til formularen's combo:

=[Forms]![FORMULARNAVN]![COMBOENS NAVN]

Form with two fields for date interval added.

And query changed from

SELECT I.ID, I.Navn, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE (S.Institution = I.ID AND  ((S.Konsultationer)<=3) AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)=1)))) AS 3KonsEnKlient, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE (S.Institution = I.ID AND  ((S.Konsultationer)<=5) AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)=1)))) AS 5KonsEnKlient, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE (S.Institution = I.ID AND  S.ID In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)=1))) AS TotKonsEnKlient, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE (S.Institution = I.ID AND  ((S.Konsultationer)<=1) AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)>1)))) AS 1KonsFlereKlienter, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE (S.Institution = I.ID AND  ((S.Konsultationer)<=2) AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)>1)))) AS 2KonsFlereKlienter, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE (S.Institution = I.ID AND  S.ID In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)>1))) AS TotKonsFlereKlienter, CLng(nz((SELECT Sum(S.Konsultationer) AS KonsAntal
FROM Sager AS S
WHERE (S.Institution = I.ID  AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)=1)))),0)) AS KonsAntalEnKlient, CLng(nz((SELECT Sum(S.Konsultationer) AS KonsAntal
FROM Sager AS S
WHERE (S.Institution = I.ID  AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)>1)))),0)) AS KonsAntalFlereKlienter
FROM Institutioner AS I;

TO

SELECT I.ID, I.Navn, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND  S.Institution = I.ID AND  ((S.Konsultationer)<=3) AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)=1)))) AS 3KonsEnKlient, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND S.Institution = I.ID AND  ((S.Konsultationer)<=5) AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)=1)))) AS 5KonsEnKlient, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND S.Institution = I.ID AND  S.ID In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)=1))) AS TotKonsEnKlient, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND S.Institution = I.ID AND  ((S.Konsultationer)<=1) AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)>1)))) AS 1KonsFlereKlienter, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND S.Institution = I.ID AND  ((S.Konsultationer)<=2) AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)>1)))) AS 2KonsFlereKlienter, (SELECT Count(S.ID) AS CountOfID
FROM Sager AS S
WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND S.Institution = I.ID AND  S.ID In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)>1))) AS TotKonsFlereKlienter, CLng(nz((SELECT Sum(S.Konsultationer) AS KonsAntal
FROM Sager AS S
WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND S.Institution = I.ID  AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)=1)))),0)) AS KonsAntalEnKlient, CLng(nz((SELECT Sum(S.Konsultationer) AS KonsAntal
FROM Sager AS S
WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND S.Institution = I.ID  AND ((S.ID) In (SELECT K.Sag AS ID
FROM Klienter AS K
GROUP BY K.Sag
HAVING Count(K.ID)>1)))),0)) AS KonsAntalFlereKlienter
FROM Institutioner AS I;

Oh yes, I also added a combo to the form

Mugs, tak for forsøget men terry gav den noget mere :)

Terry, you're the man!

I have a few questions I hope you can help with! :)

(1) Ok, the queries seems to work just fine, however the qryTerry1 queries must only number "sager" where afsluttet <>

null. I guess I can just add ' AND S.Afsluttet<>""' to the WHERE clause for each column in the query?

(2) I've modified your form to contain the data from the query, to be displayed to the end user. I've added to test

combos and a submit button which should assign the combos values from your query. when i click the button however,

after filling in institution along with date interval i get a run-time error 424 object required.

My onClick function:

Dim ThreeKI, FiveKI As String

ThreeKI = Format(KonsultationerPrInstitution![3KonsIndividuel])
FiveKI = Format(KonsultationerPrInstitution![3KonsIndividuel])

Text16 = ThreeKI
Text18 = FiveKI

Should be pretty straightforward?!

(3) On the form I've added a subform which should display the results of the qryTerry2. But after i click my "generate

-statistics-button", the data isn't updated. Requery doesn't seem to work either, but if i manually right click the

subform and select "Remove Filter/Sort" the data shows. How do i this from the function of the button?

(4) Forgot one damn thing again regarding the query! :) - But perhabs you just need to clear me out on the syntax... How do one add, multiply and divide the values returned from your query in another column? E.g. "3KonsIndividuel/TotKonsEnKlient*100" would give the percentage of Sager closed within 3 konsultations.

Hope you can help me on this last bit!

BR
Rasmus

I'll take a look and get back as soon as possible.

In answer to EXTRA Q1:

Q1:

In the query (qryTerry1) Change the criteria for each calculated field
from this

WHERE S.Institution = I.ID AND

to this

WHERE ((Not S.afsluttet is Null) and (S.Institution = I.ID)


Place the cursor in the field and press Shift + F2 so that you ZOOM into the SQL for the field, its easier to change that way.

Or in the query (qryTerry1DateInterval)

Change the criteria for each calculated field
from this

WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND  S.Institution = I.ID AND

to this

WHERE ((S.Modtaget BETWEEN [FORMS]!frmDateInterval.txtFra AND [FORMS]!frmDateInterval.txtTil) AND  (Not S.afsluttet is Null) and (S.Institution = I.ID)


Q2 and Q3 I think you will need to send the dB so that I can see exactly what it is you are doing.

I'm busted for today, but I'll have a closer look at your proposal thursday evening/night :)

Hope we arent going to take as long in closing this Q as we did in the previous :o)

Yak yak yak :) - I'm looking at it right now :P

Great T! - Just sent you the DB :)

I'll take a look ASAP

Question 1:
If the “sager” isn’t afsluttet then the date is NULL which isn’t the same as “” (empty) > it doesn’t exist!

So you have to test for NOT NULL. (WHERE (NOT S.afsluttet Is Null)  AND …..) I have included an example for the first field (qryTerryAfsluttetSagerDEMO), I’m sure you can see how it works!

Question 2:
Well its hard to give an answer here as you don’t say what you are trying to do! I can only take a wild guess :o)

REPLACE THIS>
--------------------------

Private Sub Command20_Click()

Dim ThreeKI, FiveKI As String

ThreeKI = Format(KonsultationerPrInstitution![3KonsIndividuel])
FiveKI = Format(KonsultationerPrInstitution![3KonsIndividuel])

Text16 = ThreeKI
Text18 = FiveKI

End Sub

WITH THIS>

Private Sub Command20_Click()

Text16 = DLookup("[3KonsIndividuel]", "KonsultationerPrInstitution")
Text18 = DLookup("[5KonsIndividuel]", "KonsultationerPrInstitution")

End Sub

Question 3:
I don’t see a button named “generate-statistics-button"! and if I right click on the sub form to remove the filter is STILL don’t get any records! How can this be?

Well try opening the query directly, but still entering the date interval and institution. You still get no records, correct? Well if I understand your question correctly (Q3) then it is because the query only shows records where “sager” is afsluttet, and because NONE are afsluttet you will see no records :o)

Question 4:
Take a look at the last field in the DEMO query “qryTerryAfsluttetSagerDEMO”

looks as though we are back into the same circle here :o)

No, don't worry Terry! However I was delayed in testing this, as my client in the last minute while finishing the statistics thing, changed their minds in a way that ruins the foundation of my question 2 U!! :( :(

So the past days I've been busy changing what they want, and am about to modify your solutions to fit the new structure.

Thanks for your patience! :)

OK :o)

Og godt Nytår :o)

come on woolbox, lets get this question closed once and for all!

Helllloooooo......

Sorry Terry, but I do have other things than this f*cking project to do - and like I said the changed their freakin' procedures. However, I edited to fit the new way so thanks for your help and happy new year to you to! :)

Hi Terry,

Since you've already helped alot, you have a huge advantage over other on this one: http://www.eksperten.dk/spm/489393.

That's an easy 200 points if you show me how to extend the regnings-query!

Happy (late) easter.

just got back from England, I'll take a look as soon as I'm up and running again, thats if you dont have an answer by then :o)