03. april 2003 - 11:32Der er
15 kommentarer og 1 løsning
En SELECT
Jeg har en tabel, der ser således ud:
OpInitialer - Text Patientnr - Text Operationsdato - Text Year - Number Ugevagtnr - Number Ugedag - Text Stuenr - Number Operationsnr - Number OpTypegruppenr - Number Akutoversigtnr - Number
Ud fra den tabel er jeg kun interesseret i antal af de forskellige Operationsnr - som der er 4 forskellige af (1, 2, 3, 4). Det har jeg prøvet af gøre med følgende SQL:
SELECT Operationsnr, Patientnr FROM Operationsstuer WHERE Operationsdato>='0401' AND Operationsdato<='0401' AND (((Operationsstuer.Patientnr)Is Not Null));
Men Access giver hele tiden et forkert svar...og desuden kun 1 svar - altså ikke alle de records, som det var meningen, at den skulle komme med. Hvad gøre jeg forkert, og hvordan skal jeg så gøre?
I dette særtema ser vi på, hvordan cloud og AI bliver fundamentet for virksomhedernes digitale forretning, og hvordan de nye muligheder for automatisering og forretningsværdi kan udnyttes uden at miste overblik, sikkerhed og menneskelig kontrol.
Operationsdato>='0401' AND Operationsdato<='0401' er meningsløs. Hvor spørg om den samme betingelse to gange. Hvis daoen skulle have været forskellig, skal du rette And til Or.
Operationsdato>='0401' AND Operationsdato<='0401' er det samme som at skrive Operationsdato='0401' hvad er det helt precist du vil have ud af den query ?
Operationsdato>='0401' AND Operationsdato<='0401' skrev jeg for at debugge SQL'en. Men den fungerede stadigvæk ikke.
Jeg ønsker at få antal udførsler af operationstyper ud vha. sql'en. De forskellige operationstyper er der så 4 af. Så jeg vil gerne have det bestemte antal af hver operationstype indenfor et bestemt tidsrum (derfor startdato & slutdato).?
well, I want to search the table for the number of different Operationsnr in a certain period of time (startdato - slutdato). The numbers in the table can be from 1 to 4. But this is not what I want from my search. I want the number of times the number "1" is figuring in the tabel in that period of time. ANd number of times the number "2" is there....and so on?
You need to make an aggregate(not sure thats spelt correctly) querty. When the query is in design view press the little button on the menu taht looks like a Z.
There is a row "total" here you will see "Group by". You want to group by Operationsnr but you also want to count how amny there are. So you can select the field again but now in the "total" row choose "Count" from the list. Now all you need to think about is your date inteval. Enter SOMETHING like this for the date field criteria Between #27/03/2003# And #28/03/2003# an dthen in the total field choose "Expression"
This query will give you the number of each Operationsnr within a given period. You must NOT select Patientnr and other information as this has NOTHING to do with what you want and will result in the wrong information
well...if there's no Patientnr attached to the record then the operation isn't made. And therefore it shouldn't be counted in the query. So I have to make some kind of check whether patientnr is set or not.
hmm...it doesn't work. When I execute this query: SELECT Count(Operationsstuer.Operationsnr) AS CountOfOperationsnr FROM Operationsstuer HAVING (((Operationsstuer.Operationsdato)>='0301' AND (Operationsstuer.Year)=2003) And ((Operationsstuer.Operationsdato)<='0401' AND (Operationsstuer.Year)=2003));
it results in "0". But the correct answer is 1 of "1" and 3 of "4". ??
WHY dont you have one field containing the date and not a text field for the month+day and a number field for the year?
First try getting the query to work GROUPING on Operationsnr using an aggregate query as I mentioned. Then when you see that works we can look att the date fields etc.
You can also send the db to eksperten@anthell.dk and I will take a look later
SELECT Count([Operationsstuer].[Operationsnr]) AS CountOfOperationsnr FROM Operationsstuer WHERE (((Operationsstuer.Operationsdato)>='0103' And (Operationsstuer.Operationsdato)<='0104') AND ((Operationsstuer.Operationsnr)=1) AND ((Operationsstuer.Patientnr) Is Not Null));
It was the "count"-thingy that did the job...mostly. So that dude gets the points!
thanks :o) I can also see that you are now selecting a specific Operationsnr. Well if you use the GROUP BY then you will get the count for each Operationsnr in one go :o)
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
