Help

[sgab]

Im am new to Access, and am learning myself and have built a DB that i am fairly happy with, but theres 1 thing i cant get it to do.

I want to build a quer. the data that goes in is via radio buttons, and text. you type in the persons name (a doctor) and then tick boxes to put data into 2 seperate fields, the first set of 4 tick boxes is to determin the appointment type, routine, planed, urgent, emergency. (these show up as nubers 1-4 in the field) the next set of tick boxes is to show the outcome for the patient, Alive or Dead. these tick boxes come up as numbers 1-2

what i need to be able to do is build a query so i can select a doctor ( i think i can make the query do this) but ineed it to be able to show me how many of that doctors patients died for each appointment type.

eg, i select Dr Brown

the database then shows me that Routine 4 died, planed, 2 died, urgent 7 died, emergency all died.

i could also do with getting a % for these.

Any ideas???

thanks

 

[PHV]

A starting point (SQL code):
SELECT doctor, [appointment type], Count(*) AS CountOfDied
FROM yourTable
WHERE outcome=2
GROUP BY doctor, [appointment type]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sgab]

I may sound daft, but do i write that all on 1 line.

Do I write the bits in Capitals?

I use access 97, and in the query page i have the folowing lines
Field
Table
Total
Sort
Show
Criteria
Or

what info do i put where?

 

[PHV]

Write the code in the SQL view pane

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sgab]

thanks, i put it word for word in SQL view, and got an error

This expression is typed incorectly or is to complex to be evaluated, for example a numeric expresion may contain to many complicated eliments, try simplifyning the expresion by assigning part of the expression to variables

that mean anything to you?

 

[sgab]

got that working now, thank you so much, now i need to get a % or the death rate for each procedure?

 

[PJ44]

Try this. I just got done calculating percentages on a report of mine.

=(DCount("[Procedure]","tblMyTable","[Survived]=1"))/(DCount("[Procedure]","tblMyTable","[ProcedureID]=243"))

Breakdown:
DCount - counts the number of records in a specific domain (ie table or query)
"[Procedure]" - Field you are counting (as long as there aren't any nulls, it really shouldn't matter which field you choose here)
"tblMyTable" - name of the domain (ie table or query) from which you are pulling data
"[Survived]=1" - this is essentially your SQL WHERE clause (for your example, I'm assuming a Survived field in your table where 0=no and 1=yes)

Then you divide that by:
DCount - counts the number of records in a specific domain (ie table or query)
"[Procedure]" - Field you are counting (as long as there aren't any nulls, it really shouldn't matter which field you choose here)
"tblMyTable" - name of the domain (ie table or query) from which you are pulling data
"[ProcedureID]=243" - I'm assuming each procedure has an ID, so you simply specify the procedure in question here. If not, you can do the same thing with the procedure name.

Obviuosly, you'll have to do some tweaking to match your situation, but I found DCount to be very useful for presenting totals and percentages on my reports.

Hope this helps!
PJ

 

[sgab]

i havent got a field with number of survived, the query just has the number of dead for each procedure, dont know how i would get number of survived in.

 

[PJ44]

do you have another field in the query that stores the number of procedures performed? if so, just divide number dead by # of procedures, then set the format to Percent.

PJ