Hi,

I have the following 'make table' query:

SELECT [ADM/DIS DATA].AdmissionDate, [max fce].DischargeDatetime AS
MAX_FCE_DischargeDatetime, [max fce].Specialty AS MAX_FCE_Specialty,
[ADM/DIS DATA].EpisodeNumber, [max fce].Consultant AS MAX_FCE_Consultant
INTO [re-admissions april-aug 2002]
FROM [max fce] INNER JOIN [ADM/DIS DATA] ON [max
fce].InternalPatientNumber = [ADM/DIS DATA].InternalPatientNumber
WHERE ((([max fce].DischargeDatetime)>=#3/1/2002# And ([max
fce].DischargeDatetime)<#9/1/2002#) AND (([ADM/DIS
DATA].IpAdmDtimeInt)>="200204010000" And ([ADM/DIS
DATA].IpAdmDtimeInt)<"200209010000"));


Is there any way to perform the following calculation during the query:

 
  [max fce].DischargeDatetime - [ADM/DIS DATA].AdmissionDate    
(giving me a value of months)

to give me a field in the table named LOS storing the number of months

Hope this makes sense!!!!

Best Regards

Tim Maher
This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.

Yes,  the following should work:

SELECT [ADM/DIS DATA].AdmissionDate, [max fce].DischargeDatetime AS
MAX_FCE_DischargeDatetime, [max fce].Specialty AS MAX_FCE_Specialty,
[ADM/DIS DATA].EpisodeNumber, [max fce].Consultant AS 
MAX_FCE_Consultant;
DateDiff("m",   [max fce].DischargeDatetime, [ADM/DIS 
DATA].AdmissionDate )
AS LOS  ....

This needs to be done in Access since SQL Server doesn't like VB 
functions.

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Wednesday, September 25, 2002 10:10 AM
To: Access
Subject: [access] Query Calculations

Hi,

I have the following 'make table' query:

SELECT [ADM/DIS DATA].AdmissionDate, [max fce].DischargeDatetime AS
MAX_FCE_DischargeDatetime, [max fce].Specialty AS MAX_FCE_Specialty,
[ADM/DIS DATA].EpisodeNumber, [max fce].Consultant AS 
MAX_FCE_Consultant
INTO [re-admissions april-aug 2002]
FROM [max fce] INNER JOIN [ADM/DIS DATA] ON [max
fce].InternalPatientNumber =3D [ADM/DIS DATA].InternalPatientNumber
WHERE ((([max fce].DischargeDatetime)>=3D#3/1/2002# And ([max
fce].DischargeDatetime)<#9/1/2002#) AND (([ADM/DIS
DATA].IpAdmDtimeInt)>=3D"200204010000" And ([ADM/DIS
DATA].IpAdmDtimeInt)<"200209010000"));


Is there any way to perform the following calculation during the query:


  [max fce].DischargeDatetime - [ADM/DIS DATA].AdmissionDate  
(giving me a value of months)

to give me a field in the table named LOS storing the number of months

Hope this makes sense!!!!

Best Regards

Tim Maher
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed.  It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.