SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 IIF Statment in Access
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Briceston
Yak Posting Veteran

54 Posts

Posted - 08/16/2013 :  16:09:40  Show Profile  Reply with Quote
How would I write this If statement in Access?.
I have fields with Patient ID and Code.

I want to write if patient ID is the same then Code "PatientNew" else code = "PatientDischarge". Code "PatientNew" takes precedence if the the Patient ID is the same.

Patient ID Code
123456 PatientNew
123456 PatinetOld
789767 PatientDischarge

Edited by - Briceston on 08/16/2013 16:18:12

James K
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 08/16/2013 :  17:04:41  Show Profile  Reply with Quote
quote:
Originally posted by Briceston

How would I write this If statement in Access?.
I have fields with Patient ID and Code.

I want to write if patient ID is the same then Code "PatientNew" else code = "PatientDischarge". Code "PatientNew" takes precedence if the the Patient ID is the same.

Patient ID Code
123456 PatientNew
123456 PatinetOld
789767 PatientDischarge

IIF is usually used to compare data in two columns (or two scalar variables). If I am interpreting your explanation correctly, then your data is in multiple rows. I might be misinterpreting it, but when you say "if the patientID is the same", which patient id are you looking at and what are you comparing it against?
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 08/16/2013 :  20:10:43  Show Profile  Reply with Quote
Ok, so in my data set, I have multiple records with the same patient ID. They differ by code "patientnew", "patientold", and "patientDischarge". I want patientnew to take precedence over patientold. it would go like this: IIF the patientid field has the same record twice, then patient new.


quote:
Originally posted by James K

quote:
Originally posted by Briceston

How would I write this If statement in Access?.
I have fields with Patient ID and Code.

I want to write if patient ID is the same then Code "PatientNew" else code = "PatientDischarge". Code "PatientNew" takes precedence if the the Patient ID is the same.

Patient ID Code
123456 PatientNew
123456 PatinetOld
789767 PatientDischarge

IIF is usually used to compare data in two columns (or two scalar variables). If I am interpreting your explanation correctly, then your data is in multiple rows. I might be misinterpreting it, but when you say "if the patientID is the same", which patient id are you looking at and what are you comparing it against?

Go to Top of Page

jethrow
Starting Member

USA
37 Posts

Posted - 08/17/2013 :  05:09:51  Show Profile  Reply with Quote
Well, based on the given data & requirements, you may be able to just do this:
SELECT		[Patient ID], MIN([Table].Code) AS Code
FROM		[Table]
GROUP BY	[Patient ID]


However, if there are other codes, this might be the route to go:
SELECT		[Patient ID], IIF(MAX(n)=1,"PatientNew",MAX(s.Code)) AS Code
FROM (		SELECT	[Patient ID], Code, IIF(Code="PatientNew",1,0) AS n
		FROM	[Table]) AS s
GROUP BY	[Patient ID]


Also, you are in the SQL Server section of the forum. I'm not a veteran here, but I'd assume Access questions should go in the Other Forums::MS Access forum.

Microsoft SQL Server Noobie
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 08/17/2013 :  10:26:28  Show Profile  Reply with Quote

Thanks, this was helpful.


quote:
Originally posted by jethrow

Well, based on the given data & requirements, you may be able to just do this:
SELECT		[Patient ID], MIN([Table].Code) AS Code
FROM		[Table]
GROUP BY	[Patient ID]


However, if there are other codes, this might be the route to go:
SELECT		[Patient ID], IIF(MAX(n)=1,"PatientNew",MAX(s.Code)) AS Code
FROM (		SELECT	[Patient ID], Code, IIF(Code="PatientNew",1,0) AS n
		FROM	[Table]) AS s
GROUP BY	[Patient ID]


Also, you are in the SQL Server section of the forum. I'm not a veteran here, but I'd assume Access questions should go in the Other Forums::MS Access forum.

Microsoft SQL Server Noobie

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000