To bperry ( here is some more info)

[Dado111]

Hi bperry,
Sorry for all this hassel, well yes i am working on Ms Access, and yet (cast) is not working and the message that the program is returning is as follows:
Syntax error ( missing operator) in queri expression
"CAST (
(Select count(Ward_code) FROM Admission
WHERE admission.Ward_code = ward.Ward_code
AND admission.Discharge_date IS NULL
) AS numeric(3,2)) / bed_count *100 ".
So what do u thing is the problem




1st thread
Hi,

How can i produce a report that gives for each ward its name, code, and the percentage occupied in the ward of a hospital.
Knowing that we have the table of "Ward" which hold a wardCode, wardName, BedCount.
And another table for Admission which has AdmissionID, patient_Id, AdmissionDate, Ward code Discharge_Date

Thanks for all your help
Dado


2nd thread
Hi,
Thanks for ur solutions i was trying to work on this one below but it is still not working. And to tell u the truth i haven't heard of the "CAST" before maybe it is replacment for some other word so if it is tell me what is it please check the code and tell me what u think
Thanks allot
Dado
N.B: if u need i can send u the Data base

SELECT ward.Ward_code,ward.Ward_name,
CAST (
(Select count(Ward_code) FROM Admission
WHERE admission.Ward_code = ward.Ward_code
AND admission.Discharge_date IS NULL
) AS numeric(3,2)) / bed_count *100 AS PerOcc

FROM Ward
INNER JOIN Admission
ON ward.Ward_code = admission.Ward_code
Group By ward.Ward_code, ward.ward_name, ward.Bed_count;

 

[tlbroadbent]

Cast is not valid in MS Access. You can't use it. That is why the error occurs. Try this.

SELECT w.Ward_code,w.Ward_name,
(100. * q.RecCnt / w.bed_count) AS PerOcc
FROM Ward
INNER JOIN
(Select WardCode, Count(*) As RecCnt
FROM Admission a
WHERE a.Discharge_Date IS NULL
GROUP BY a.WardCode) As q
ON w.Ward_code = q.Ward_code
Group By w.Ward_code, w.ward_name;

In the future, I recommend posting Access Questions in an Access forum. Read faq181-1655, "Which Access forum should I choose for my question? Why?" to find out the purpose and focus of each forum.

Again, post follow-on questions in the same thread rather than starting a new thread. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Dado111]

It is still not working I am getting a message saying that
" you tried to excute a query that doesnot include the specified expression "100 * q.RecCnt / ward.bed_count" as an aggregate function"

what is ment by q.RecCnt??
thanks for your help

 

[tlbroadbent]

Remove the Group By clause at the end.

SELECT w.Ward_code,w.Ward_name,
(100. * q.RecCnt / w.bed_count) AS PerOcc
FROM Ward
INNER JOIN
(Select WardCode, Count(*) As RecCnt
FROM Admission a
WHERE a.Discharge_Date IS NULL
GROUP BY a.WardCode) As q
ON w.Ward_code = q.Ward_code;
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Dado111]

Still not working
it is giving the same " you tried to excute a query that doesnot include the specified expression "ward_code" as an aggregate
If u are online and not busy are we able to chat on msn if u have it so we can sort it out, i fell that there is somthing simple wrong there but we are missing it
so my msn & e-mail is imadelghali@hotmail.com

thanks again

 

[tlbroadbent]

Group By clause should read...

GROUP BY a.Ward_Code Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ldandy]

In Access you cannot use an explicit subquery in the FROM clause, so you cannot say FROM (SELECT ... ). You have to provide a table name or a query name (view) in the FROM clause. You can transform Terry's query in the following way: make the SELECT from the FROM clause a new query and use it in the INNER JOIN. It should work.

 

[tlbroadbent]

ldandy,

You can Select from a subquery in Access. Which version are you using? The query I provided previously works. I chatted with Dado111 Saturday night. With the underscore correction, it worked for him. The following also works.
[tt]
Select col1, col4, col5
From (Select * From MyTable
Where col2='abc') As q[/tt]

This is really quite a powerful concept that I use regularly and frequently in Access and SQL Server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ldandy]

Terry,

I'm using Access '97. Probably this is the reason those queries don't for work in my Access. However in SQL Server I'm also using them.

Thanks for your answer.

Danny.